Post reply

Update the Time of Day, but not the Date

  • January 7, 2014 at 9:02 am

    #288095

    Hello Everyone

    I hope that all is well.

    I am working with some dates that are in the datetime data format. What I would like to do is set the current time to something else, without changing the date.

    I have these dates:

    DECLARE @TheOriginalDates TABLE

    (

    CurrentDate datetime

    , DateWithNewTime datetime NULL

    )

    INSERT INTO @TheOriginalDates

    SELECT '2013-01-09 11:32:00.000', NULL UNION ALL

    SELECT '2013-06-14 15:20:00.000', NULL UNION ALL

    SELECT '2013-06-14 15:20:00.000', NULL UNION ALL

    SELECT '2013-02-14 20:22:00.000', NULL UNION ALL

    SELECT '2013-05-05 23:11:00.000', NULL UNION ALL

    SELECT '2013-01-09 11:32:00.000', NULL UNION ALL

    SELECT '2013-01-09 11:29:00.000', NULL UNION ALL

    SELECT '2013-06-14 15:20:00.000', NULL UNION ALL

    SELECT '2013-08-15 15:22:00.000', NULL UNION ALL

    SELECT '2013-01-09 11:29:00.000', NULL UNION ALL

    SELECT '2013-01-09 11:35:00.000', NULL UNION ALL

    SELECT '2013-06-14 15:20:00.000', NULL UNION ALL

    SELECT '2013-06-14 15:08:00.000', NULL UNION ALL

    SELECT '2013-06-14 15:49:00.000', NULL UNION ALL

    SELECT '2013-01-09 11:34:00.000', NULL

    -------------------------------------------------

    SELECT * FROM @TheOriginalDates

    -------------------------------------------------

    DECLARE @TheOriginalDatesWithNewTime TABLE

    (

    CurrentDate datetime

    , DateWithNewTime datetime NULL

    )

    INSERT INTO @TheOriginalDatesWithNewTime

    SELECT '2013-01-09 11:32:00.000', '2013-01-09 23:59:59' UNION ALL

    SELECT '2013-06-14 15:20:00.000', '2013-06-14 23:59:59' UNION ALL

    SELECT '2013-06-14 15:20:00.000', '2013-06-14 23:59:59' UNION ALL

    SELECT '2013-02-14 20:22:00.000', '2013-02-14 23:59:59' UNION ALL

    SELECT '2013-05-05 23:11:00.000', '2013-05-05 23:59:59' UNION ALL

    SELECT '2013-01-09 11:32:00.000', '2013-01-09 23:59:59' UNION ALL

    SELECT '2013-01-09 11:29:00.000', '2013-01-09 23:59:59' UNION ALL

    SELECT '2013-06-14 15:20:00.000', '2013-06-14 23:59:59' UNION ALL

    SELECT '2013-08-15 15:22:00.000', '2013-08-15 23:59:59' UNION ALL

    SELECT '2013-01-09 11:29:00.000', '2013-01-09 23:59:59' UNION ALL

    SELECT '2013-01-09 11:35:00.000', '2013-01-09 23:59:59' UNION ALL

    SELECT '2013-06-14 15:20:00.000', '2013-06-14 23:59:59' UNION ALL

    SELECT '2013-06-14 15:08:00.000', '2013-06-14 23:59:59' UNION ALL

    SELECT '2013-06-14 15:49:00.000', '2013-06-14 23:59:59' UNION ALL

    SELECT '2013-01-09 11:34:00.000', '2013-01-09 23:59:59'

    SELECT * FROM @TheOriginalDatesWithNewTime

    I am trying to set the time only on each row to the last second of the day '23:59:59' plus the original date.

    I am rather stuck, everything that I have tried is not working.

    Thank you in advance for your advise, suggestions and code samples.

    Andrew SQLDBA

  • January 7, 2014 at 9:11 am

    #1678121

    Thank You to Everyone

    I just figured it out. I got up, walked away for a while, watched a little tv, and came back to hit again.

    This is what I used:

    UPDATE @TheOriginalDates SET DateWithNewTime =

    CAST(CAST(CONVERT(DATE, CurrentDate,101) AS VARCHAR) + ' 23:59:59' AS DATETIME)

    Thank you to everyone on here.

    Andrew SQLDBA

  • January 7, 2014 at 9:17 am

    #1678128

    Looks better than what I came up with:

    update @TheOriginalDates

    set DateWithNewTime = dateadd(ss,-1,dateadd(dd,1,cast(cast(CurrentDate as date)as datetime)))🙂


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • January 7, 2014 at 9:19 am

    #1678129

    Here's another way to calculate it without the need of a triple conversion 🙂

    UPDATE @TheOriginalDates SET DateWithNewTime =

    DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, CurrentDate) + 1, 0))

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 7, 2014 at 11:50 am

    #1678191

    AndrewSQLDBA (1/7/2014)

    Hello Everyone

    I hope that all is well.

    I am working with some dates that are in the datetime data format. What I would like to do is set the current time to something else, without changing the date.

    I have these dates:

    DECLARE @TheOriginalDates TABLE

    (

    CurrentDate datetime

    , DateWithNewTime datetime NULL

    )

    INSERT INTO @TheOriginalDates

    SELECT '2013-01-09 11:32:00.000', NULL UNION ALL

    SELECT '2013-06-14 15:20:00.000', NULL UNION ALL

    SELECT '2013-06-14 15:20:00.000', NULL UNION ALL

    SELECT '2013-02-14 20:22:00.000', NULL UNION ALL

    SELECT '2013-05-05 23:11:00.000', NULL UNION ALL

    SELECT '2013-01-09 11:32:00.000', NULL UNION ALL

    SELECT '2013-01-09 11:29:00.000', NULL UNION ALL

    SELECT '2013-06-14 15:20:00.000', NULL UNION ALL

    SELECT '2013-08-15 15:22:00.000', NULL UNION ALL

    SELECT '2013-01-09 11:29:00.000', NULL UNION ALL

    SELECT '2013-01-09 11:35:00.000', NULL UNION ALL

    SELECT '2013-06-14 15:20:00.000', NULL UNION ALL

    SELECT '2013-06-14 15:08:00.000', NULL UNION ALL

    SELECT '2013-06-14 15:49:00.000', NULL UNION ALL

    SELECT '2013-01-09 11:34:00.000', NULL

    -------------------------------------------------

    SELECT * FROM @TheOriginalDates

    -------------------------------------------------

    DECLARE @TheOriginalDatesWithNewTime TABLE

    (

    CurrentDate datetime

    , DateWithNewTime datetime NULL

    )

    INSERT INTO @TheOriginalDatesWithNewTime

    SELECT '2013-01-09 11:32:00.000', '2013-01-09 23:59:59' UNION ALL

    SELECT '2013-06-14 15:20:00.000', '2013-06-14 23:59:59' UNION ALL

    SELECT '2013-06-14 15:20:00.000', '2013-06-14 23:59:59' UNION ALL

    SELECT '2013-02-14 20:22:00.000', '2013-02-14 23:59:59' UNION ALL

    SELECT '2013-05-05 23:11:00.000', '2013-05-05 23:59:59' UNION ALL

    SELECT '2013-01-09 11:32:00.000', '2013-01-09 23:59:59' UNION ALL

    SELECT '2013-01-09 11:29:00.000', '2013-01-09 23:59:59' UNION ALL

    SELECT '2013-06-14 15:20:00.000', '2013-06-14 23:59:59' UNION ALL

    SELECT '2013-08-15 15:22:00.000', '2013-08-15 23:59:59' UNION ALL

    SELECT '2013-01-09 11:29:00.000', '2013-01-09 23:59:59' UNION ALL

    SELECT '2013-01-09 11:35:00.000', '2013-01-09 23:59:59' UNION ALL

    SELECT '2013-06-14 15:20:00.000', '2013-06-14 23:59:59' UNION ALL

    SELECT '2013-06-14 15:08:00.000', '2013-06-14 23:59:59' UNION ALL

    SELECT '2013-06-14 15:49:00.000', '2013-06-14 23:59:59' UNION ALL

    SELECT '2013-01-09 11:34:00.000', '2013-01-09 23:59:59'

    SELECT * FROM @TheOriginalDatesWithNewTime

    I am trying to set the time only on each row to the last second of the day '23:59:59' plus the original date.

    I am rather stuck, everything that I have tried is not working.

    Thank you in advance for your advise, suggestions and code samples.

    Andrew SQLDBA

    Why do you want to do this, Andrew? You're losing data. Those times may one day be important. If they are not, you should store the values as date.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • January 7, 2014 at 12:13 pm

    #1678196

    Chris

    Thank you for your concern and advice. I know that. But I am querying this data from another database to pump into one of my own. I have no control over the data types. This data came out of an Oracle (I even hate just saying that word) database, that is now sitting in a SQL table that is being called from a View. Oh, and with absolutely no correct data types.

    I know much better than to store data in anything other than the correct data type. That is why folks hire me, because I can usually figure out ways to pump, cleanse and correct the data types from the horrible garbage that I am given. Seems they never have the funds to do it right the first time, and to hire a database architect. But they always seem to find the money to hire a data janitor to come in later and clean up the mess.

    Andrew SQLDBA

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

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