SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 date to yyyyww
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nord
Posting Yak Master

122 Posts

Posted - 09/17/2013 :  14:24:00  Show Profile  Reply with Quote
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

Sweden
30117 Posts

Posted - 09/17/2013 :  14:41:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

122 Posts

Posted - 09/17/2013 :  15:17:28  Show Profile  Reply with Quote
2012-02-05 is week 05
Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 09/17/2013 :  15:31:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30117 Posts

Posted - 09/17/2013 :  15:32:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 09/17/2013 :  17:22:30  Show Profile  Reply with Quote
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

547 Posts

Posted - 09/17/2013 :  17:57:25  Show Profile  Reply with Quote
Something like this:


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');

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000