DatePart Question 1

[jon92]

Hi

I am using the following to produce a Week Number Column in a select query:

Week Number: DatePart("ww",[MyDate],2)

the above returns 05/04/04 as week 15
(date field is formatted to dd/mm/yy)

Is it possible to add to the code
to return (Mon) 05/04/04 as week 1 thru to (Mon) 28/03/05 as week 52

Thanks

 

[r937]

what you want to do is shift the number that Datepart returns

Week Number: (DatePart("ww",[MyDate],2) + 38 ) Mod 52

okay, so in the first week of april, the Datepart function gives week 15

so add 38 to that, getting 53, then use the Mod function on it, with 52 as the modulus, and we get a 1 remainder

when Datepart returns 14 for the last week in March in 2005, 14 + 38 = 52





rudy

http://r937.com/

SQL Consulting

 

[jon92]

Thanks rudy

I have tried your suggestion
but the last week in march (wk beginning 28/03/05)
returns 0

Is this because the month changes to April on the Friday ?

Jon

 

[r937]

oh, okay

well, just play with it until you get it

Week Number:
iif(DatePart("ww",[MyDate],2)=14, 52,
(DatePart("ww",[MyDate],2) + 39 ) Mod 52 )


rudy

http://r937.com/

SQL Consulting

 

[jon92]

Rudy

I changed +39 to +38

And that gives me the required results

Thanks very much for your help

Jon