i would like to query a date which only showing work week and year... i try in FORMAT([MYDATE], 'WW YYYY', 1, 2)... it work but when goes to 31/12/2006 it showing "01 2006"... but suppose it should show "01 2007" how can i overcome this?

Recommended Answers

All 4 Replies

i would like to query a date which only showing work week and year... i try in FORMAT([MYDATE], 'WW YYYY', 1, 2)... it work but when goes to 31/12/2006 it showing "01 2006"... but suppose it should show "01 2007" how can i overcome this?

I am not clear what you are trying to do, can you eplain a bit more please.

Also 31/12/2006 should be 52 2006 surely ?

Denis

I am not clear what you are trying to do, can you eplain a bit more please.

Also 31/12/2006 should be 52 2006 surely ?

Denis

i'm looking query for work week and year in one column... so when comes to 31/12/2006 it should be return ww01 year 2007 instead of ww01 year 2006.

Where Is The Formatting Taking Place - SQL ?

FORMAT Is A (Among Other Things) A VB/VBA Command - NOT SQL

Regardless Of This, 31st Dec Is the 1st (Or 2nd Day In A Leap Year) Day In The 53rd Week. 52 * 7 = 364

You Say You Want The Working Week. What Exactly Is This ? What Day Does Your Week Start.

Also If 30th Dec 2006 = 52 2006 Week 1 Day 1 = 1st Jan 2006
However You Say You Want 31st Dec 2006 = 01 2007 - This Means 1st Jan Is Week 1 Day 2

This Is Not Consistant

Try Using The Days In The Year - WW = ((Days DIV 7) MOD 52) + 1

This is how your code should read:

FORMAT$([MyDate], "ww", "yyyy", 1,2)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.