date problem

  • 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

  • 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

  • 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/

  • 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:

  • 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

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

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