Post reply

Update field in dimDate table

  • March 8, 2013 at 1:47 pm

    #275091

    Hi

    I have a dimDate table that I need to update a field

    DateFull = '1900-01-01 00:00:00.000'

    = '1900-01-02 00:00:00.000'

    etc.....

    I need to change to = '1900-01-02 23:59:59.000'

    Thanks

    Joe

  • March 8, 2013 at 3:11 pm

    #1595136

    So you're just looking to update each date to the end of the day?

    Will this help you get started?

    declare @datefield datetime

    set @datefield = GETDATE()

    select @datefield originalDate, dateadd(ms,-3,cast((dateadd(d,1,cast(@datefield as DATE)) )as datetime)) updatedDate

    Original Date:2013-03-08 15:10:04.677Updated Date:2013-03-08 23:59:59.997

  • March 8, 2013 at 3:15 pm

    #1595139

    Erin Ramsay (3/8/2013)

    So you're just looking to update each date to the end of the day?

    Will this help you get started?

    declare @datefield datetime

    set @datefield = GETDATE()

    select @datefield originalDate, dateadd(ms,-3,cast((dateadd(d,1,cast(@datefield as DATE)) )as datetime)) updatedDate

    Original Date:2013-03-08 15:10:04.677Updated Date:2013-03-08 23:59:59.997

    No need to do all the casting.

    select @datefield originalDate, dateadd(ms,-3, dateadd(d,1, @datefield)) updatedDate

    Once I removed all the extra casts this look remarkably like the version I was about to post.

    dateadd(millisecond, -1, dateadd(day, 1, DateFull))

    I like to use the whole part name because I can't every seem to remember many of them. 🙂

    _______________________________________________________________

    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/

  • March 8, 2013 at 3:21 pm

    #1595145

    Sean Lange (3/8/2013)

    Erin Ramsay (3/8/2013)

    So you're just looking to update each date to the end of the day?

    Will this help you get started?

    declare @datefield datetime

    set @datefield = GETDATE()

    select @datefield originalDate, dateadd(ms,-3,cast((dateadd(d,1,cast(@datefield as DATE)) )as datetime)) updatedDate

    Original Date:2013-03-08 15:10:04.677Updated Date:2013-03-08 23:59:59.997

    No need to do all the casting.

    select @datefield originalDate, dateadd(ms,-3, dateadd(d,1, @datefield)) updatedDate

    Once I removed all the extra casts this look remarkably like the version I was about to post.

    dateadd(millisecond, -1, dateadd(day, 1, DateFull))

    I like to use the whole part name because I can't every seem to remember many of them. 🙂

    Look carefully with eye at the results of the following:

    declare @datefield datetime = '1900-01-01 00:00:00.000';

    select @datefield originalDate, dateadd(ms,-3, dateadd(d,1, @datefield)) updatedDate, dateadd(millisecond, -1, dateadd(day, 1, @datefield))

  • March 8, 2013 at 3:21 pm

    #1595146

    Thanks, Sean.

    I did all the casting to account for a time like 2013-03-08 15:18:50.807 and you add a day you get 2013-03-09 15:18:50.807.

    If you take way 1-3 milliseconds from that you're not going to get the results he's looking for. I just wanted to make sure that if he gave me a date for any time during that day it would increment properly to the end of that day.

    Erin

  • March 8, 2013 at 3:23 pm

    #1595148

    That's why I used -3 milliseconds, Lynn. 🙂

  • March 8, 2013 at 3:26 pm

    #1595150

    Hi

    I just did

    SET datefull = dateadd(ms,-3,cast((dateadd(d,1,cast(datefull as DATE)) )as datetime))

    worked great

    Thanks for all the help

    Now I just have to read up on what it all does 🙂

  • March 8, 2013 at 3:29 pm

    #1595151

    Erin Ramsay (3/8/2013)

    That's why I used -3 milliseconds, Lynn. 🙂

    Was actually talking to Sean and his code subtracting 1 millisecond.

  • March 8, 2013 at 3:33 pm

    #1595152

    Lynn Pettis (3/8/2013)

    Erin Ramsay (3/8/2013)

    That's why I used -3 milliseconds, Lynn. 🙂

    Was actually talking to Sean and his code subtracting 1 millisecond.

    /facepalm

    I think you both saw the error of my ways before I even posted them. Not sure if I fat fingered that or had a momentary lapse in brain activity. This is certainly something I am aware of.

    Thanks to both of you for correcting me!!!

    _______________________________________________________________

    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/

Viewing 9 posts - 1 through 8 (of 8 total)

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