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.
| 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.000I 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 |
 |
|
|
nord
Posting Yak Master
126 Posts |
Posted - 2013-09-17 : 15:17:28
|
| 2012-02-05 is week 05Thanks |
|
|
|
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 |
|
|
|
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 |
|
|
|
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. |
|
|
|
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] |
|
|
|
|
|
|
|
|
date to yyyyww