Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 date to yyyyww

Author  Topic 

nord
Posting Yak Master

126 Posts

Posted - 2013-09-17 : 14:24:00
Hi,
I have table with column WeekEndDate format:2012-02-26 00:00:00.000
I would like convert this format to YYYYWW,how I can do it?
Thanks a lot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-17 : 14:41:14
We can try, if you first explain what a week is to you.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

nord
Posting Yak Master

126 Posts

Posted - 2013-09-17 : 15:17:28
2012-02-05 is week 05
Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-17 : 15:31:44
And that is week 5 because..?
According to ISO week numbering? Because 5th of February is in the 5th weeks because Week #1 starts always with January 1st?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-17 : 15:32:24
And you week starts with a sunday and ends with a saturday?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-17 : 17:22:30
Take a look at this page http://msdn.microsoft.com/en-us/library/ms174420.aspx in particular, the sections on "week and weekday datepart Arguments" and "ISO_WEEK datepart". You will see why the answering the "WW" part in your "YYYYWW" is not possible without knowing your definition of a week.
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-09-17 : 17:57:25
Something like this:
[CODE]

DECLARE @date DATETIME = '2012-02-05';
SELECT FORMAT(DATEPART(YEAR, @date), '0000') + FORMAT(DATEPART(WEEK, @date)-1, '00');

SELECT FORMAT(DATEPART(YEAR, @date), '0000') + FORMAT(DATEPART(ISO_WEEK, @date), '00');

[/CODE]
Go to Top of Page
   

- Advertisement -