DatePart(ww, DateTime) Function

[Keyth]

Hi Guys n Gals

When I query the week number of a datetime column it is returning 3 for WC11/01/2010 whereas on my paper calendar on the wall it is week 2. I understand why the db is returning week 3 but is there an option somewhere I can add/change to count week numbers starting wc04-01-2010 so WC11/01/2010 becomes week 2?

Thanks,

Keyth

 

[drlex]

Sounds like you want the ISO week. If you have SQL Server 2008, that supports 'isowk' as an argument for DATEPART.

Have a look at these two internet links:

http://www.rmjcs.com/SQLServer/TSQLFunctions/ISOWeekNumber/tabid/207/Default.aspx

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/iso-week-in-sql-server

soi là, soi carré

 

[Keyth]

Thanks for your reply, will your suggestions work with SQL Server 2005?
thanks

 

[drlex]

If you read the second link, pay particular attention to the sentence "In SQL Server 2000/2005 you could use the user defined function ISOweek which was in the SQL Server books on line" and the part that follows.

If you hadn't got that far, the first link includes the text "This function is suitable for SQL Server 2005 and 2008 as well as SQL Server 2000 (just remove the RETURNS NULL ON NULL INPUT function option)."

soi là, soi carré

 

[Keyth]

Thanks drlex, no I hadnt even looked at the pages using the links, I will check though now you mention that thank you.

 

[drlex]

And thank you for your refreshing honesty. As the terminator progresses around the globe, expect a post or two on this topic from those in the Americas in a few hours.
Happy Monday!

soi là, soi carré