I have a table with 3 columns
YearNo, WeekNo, DayNo

Is there any syntax I can use in a Query to return the date
(yyyy-mm-dd) from these values?

--
Rob

On 21 Nov 2011 in comp.databases.mysql, R C Nesbit wrote:

STR_TO_DATE() should do it, given an appropriate format string.

http://dev.mysql.com/doc/refman/5.1/...on_str-to-date

--
Joe Makowiec
http://makowiec.org/
Email: http://makowiec.org/contact/?Joe
Usenet Improvement Project: http://twovoyagers.com/improve-usenet.org/

On 11/21/2011 8:57 AM, Joe Makowiec wrote:
Hmmm, I was thinking of a combination using date_add(), but this is a
much better solution.

However, I would also recommend the op change his table to use a single
DATE type column. It's quite easy to extract the year, week and day of
week from this value. And it ensures the date will always be valid.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jerry Stuckle spoke:
This is what I actually ended up doing in a script.

Not so easy, as the table is quite large, need to be accessed very rapidly to update a
screen matrix (works schedule) of 7 Weekday rows by n columns, all indexed to the display by
wk(screen) & day number (row) & vehicle (column)

I hate dealing with dates & times in code - why can't they decimalise it?

It would make life *much* simpler!

I dread to think what code would be like if we were still using £/-p

--
Rob

On 11/21/2011 2:44 PM, R C Nesbit wrote:
How big is big?

Have you actually tried it? My experience has been that the date
functions are quite fast. And if you're just looking for a single week,
you could use the week start and end date in your WHERE clause to limit
the initial selection. With an index on the date column, the results
should be quite fast.

MySQL makes date types easy to work with. gb

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Beware that MySQL supports 8 different types of week numbers (see
manual for the week() function), and that you need to use the year
that matches the correct type of week numbering. For example, the
year associated with 2011-01-01 (which is a Saturday) may be 2010,
not 2011, depending on which week-numbering scheme you are using.
(There are also some week-numbering systems in use which MySQL does
not support, such as weeks beginning on Saturday).

You probably have a lot of existing data, so whatever definition
of week numbers you're using, you probably want to stick with.

You can do this conversion with str_to_date(), using %x, %X, or %Y
(and hopefully *not* %y) for the year, %U, %u, %V, or %v for the
week number, and %w for the day of the week. You need to use the
correct conversions for the year and week number based on which set
of week numbers you are using.

Jerry Stuckle spoke:
Not huge - at present around 50k records.
But the screen is set to refresh every 60 seconds for people with read-only
privilege since several people with write access can be updating it at
once.
The screen is a matrix of 7 x nn and each cell can have x records in it.
so it works fine with querying on year & week, and the dayno (row) &
anotherID (column) gives me the coordinates to fill the matrix.

Now there is a requirement to give a date value for any given record. My
own fault for not foreseeing this, so I've added a date column as well and
written a script to convert the Y-W-D fields into a julian date.

Gordon Burditt spoke:
Yes I know - tiz a right pain!

Correct - oddities of week 0 and week 53 are handled in code as
exceptions. Thankfully the user doesn't do much work between Dec24 -
Jan2.

--
Rob