Post reply

Changing a datetime value

  • January 3, 2007 at 12:11 pm

    #115638

    Hi All:

    I need to change a datetime value from "2007-01-03 09:10:35.000"

    to "2007-01-02 10:00:00.000"

    Thanks for your help on this

    William

  • January 3, 2007 at 12:37 pm

    #681024

    That trick works for any part of the date (ms, seconds, minutes, hours, days...)

     

    SELECT DATEADD(hh, DATEDIFF(hh, 0, '2007-01-03 09:00:00.000') + 1, 0)

    UNION ALL

    SELECT DATEADD(hh, DATEDIFF(hh, 0, '2007-01-03 09:10:35.000') + 1, 0)

    UNION ALL

    SELECT DATEADD(hh, DATEDIFF(hh, 0, '2007-01-03 09:30:35.000') + 1, 0)

    UNION ALL

    SELECT DATEADD(hh, DATEDIFF(hh, 0, '2007-01-03 09:59:59.997') + 1, 0)

     

  • January 3, 2007 at 12:46 pm

    #681029

    So to use this would the complete statement look like:

    update table where record_id = 'abc'

    set event_date = SELECT DATEADD(hh, DATEDIFF(hh, 0, '2007-01-03 09:59:59.997') + 1, 0)

    Thanks

    William

  • January 3, 2007 at 1:40 pm

    #681045

    Do you really need to change the base table data?

     

    You can simply select the data and present it in a different way.

     

    If you were to choose to update the data :

    UPDATE dbo.YourTableName

    SET Event_date = DATEADD(hh, DATEDIFF(hh, 0, event_date) + 1, 0)

    where record_id = 'abc'

  • January 3, 2007 at 1:50 pm

    #681048

    Worked like a champ!!!

    Thank you so much

    William

  • January 4, 2007 at 11:14 am

    #681276

    Do you also need to set the date back 1 day or is that a typo?

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • January 4, 2007 at 11:18 am

    #681280

    Hi Robert:

    I actually had to set the time back one hour

    William

Viewing 7 posts - 1 through 6 (of 6 total)

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