General discussion

Locked

Date conversion problem

By Ian K ·
From within Access 97 (ideally in a query) I would like to convert a date in the format of year, week number and day number back into calendar date format.

e.g. Today is week 23, year 2001 day 4 which is 7/06/2001 (7th June 2001).

If it can't be done in a query a VBA solution would be fine.

Thanks,

Ian

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Date conversion problem

by mark.thomson In reply to Date conversion problem

Here is a sample query that does the conversion

SELECT calendartest.Dayno, calendartest.weekno, calendartest.year, [dayno]-1+(([weekno]-1)*7)+DateValue("01/01/" & [year]) AS fulldate
FROM calendartest;

This is just off the top of my head, there may be a more elegant solution

Collapse -

Date conversion problem

by Ian K In reply to Date conversion problem

Thanks for this Mark. Unfortunately I can't get exact results with the above. However I've learnt something new from you.

Cheers,

Ian

Collapse -

Date conversion problem

by Ian K In reply to Date conversion problem

Point value changed by question poster.

Collapse -

Date conversion problem

by rfoster In reply to Date conversion problem

DateAdd may also be appropriate. For example:

CalendarDate = DateAdd("w", DayNumber, DateAdd("ww", WeekNumber, DateSerial(2001,1,1)))

(Note, this is from memory and may be inaccurate, also I rarely deal with dates in the form you mention - test well!)

Regards,
Richard

Collapse -

Date conversion problem

by Ian K In reply to Date conversion problem

Hi Richard,

Thanks for the help. Unfortunately this doesn't give exact result. I developed your answer into:

DateAdd("w",[DayNum]-1,DateAdd("ww",[Week No]-1,DateAdd("yyyy",[Year Number]-1996,"1-Jan-96"))

My dates don't got back past 96 hence me subrtracting and adding from 1-1-96. However It doesn't give the same answer as me looking up in my diary.

e.g. day 7, week 25, year 1998 should return 28-6-98, however the above returns 24-6-98. Close but not exact !!

Collapse -

Date conversion problem

by mark.thomson In reply to Date conversion problem

It looks like Day 1 week 1 for any year is the first monday in January ( which happens to be Jan 01 for 2001) thus your solution has to take account of which day of the week O1 jan [year number] falls on

so try SELECT calendartest.Dayno, calendartest.weekno, calendartest.year, [dayno]-1+(([weekno]-1)*7)+DateValue("01/01/" & [year]) + weekday(DateValue("01/01/" & [year])) - 2 AS fulldate
FROM calendartest;

(The -2 is to take account of the fact that the weekday function returns 2 for monday)

Collapse -

Date conversion problem

by Ian K In reply to Date conversion problem

This still isn't exact enough, I'm afraid. Thanks for your effort though.

Collapse -

Date conversion problem

by Ian K In reply to Date conversion problem

This question was closed by the author

Back to Web Development Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums