date problem

Question

date problem

jamesnorton

SSC Journeyman

Points: 84
More actions
March 4, 2013 at 9:45 am

#271093

Hi not sure if this is the correct forum but I need some help with dates in SQL.
I have been given a database that has the dates stored in 3 different parts (week of year, year and day number) and I need to change it back to a proper date
for example if today is Monday 04/03/2013 then the data will be stored as week no = 10 , year = 2013 , daynumber = 2
but I need to change it back to Monday 04/03/2013
please help
thanks
James

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

jamesnorton (3/4/2013)
Hi not sure if this is the correct forum but I need some help with dates in SQL.
I have been given a database that has the dates stored in 3 different parts (week of year, year and day number) and I need to change it back to a proper date
for example if today is Monday 04/03/2013 then the data will be stored as week no = 10 , year = 2013 , daynumber = 2
but I need to change it back to Monday 04/03/2013
please help
thanks
James

Hi James

Can you identify which algorithm has been used to set the week number? IIRC there are several, and the majority are in BOL.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

This should work based on your description.

declare @WeekOfYear int = datepart(week, getdate()) --10

declare @Year int = datepart(year, getdate()) --2013

declare @DayNumber int = datepart(weekday, getdate()) --2

declare @BaseDate datetime = cast(@Year as char(4)) + '/1/1'

select dateadd(week, @WeekOfYear - 1, @BaseDate), dateadd(day, -1 * (@DayNumber - 1), dateadd(week, @WeekOfYear - 1, @BaseDate))

The challenge you are facing is an example of why you should ALWAYS store datetime information in a datetime column. This of course is not always available to change but this type of thing is the cause of a lot of frustration.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jamesnorton SSC Journeyman Points: 84 More actions · Answer 3

Many thanks for your help it has saved a few days of work 😀 . I hate working with dates like this . now I have to split the week so it starts on a wed :ermm:

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

You might actually benefit from setting up a calendar table with a Column indicating the Start of the week, and once set up it should also enable you to quickly look up the correct date for and Day, Week, Year combination.

_________________________________________________________________________
SSC Guide to Posting and Best Practices