«
»

Set the time for a SQL datetime

Thursday, September 16th, 2010

I spent about an hour attempting to google the best way of changing the time of a SQL datetime midquery. I would think this would be easy, and it kind of was, but finding the logic that led me to it was confoundingly difficult. So hopefully you other googlers out there will benefit from my experience. This is for SQL Server, but other versions of SQL will have similar functions available to them.

--We start with DateAdd which adds the specified time interval to a date
DateAdd(timeinterval, amountoftime, date)

--We combine this with DateDiff which gives you the difference in time between two dates. We can ask DateDiff to give us the number of days between the start of time (0) and our current time. Now we have days with no time, essentially zero'ing the time.
DateDiff(timeinterval, starttime, endtime)
DateDiff(dd, 0, mydatetime)

--Finally we combine the two statements. DateDiff gives us our days and DateAdd converts that back into a datetime.
DateAdd(anytimeinterval, 0, DateDiff(dd, 0, mydatetime))

--But what about actually setting the time? No problem, just change "anytimeinterval" and the 0 right after it to the time you want to add
DateAdd(hh, 9, DateDiff(dd, 0, mydatetime)) would add nine hours to the truncated time.

Tags: , , ,

This entry was posted on Thursday, September 16th, 2010 at 1:18 pm and is filed under Software Development, SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Set the time for a SQL datetime”

  1. Tony says:
    February 15, 2012 at 2:43 am

    Hi Thomas

    Thanks for your post, i took the liberty to send you my solution. Hope it can be off help in some way.
    I use the SQL CONVERT function to VARCHAR and then work it back again.
    Here I pass in a DATETIMEand the new time to set it to and the SQL scalar function returns the new DATETIME with the new time set.
    FUNCTION udf_SetTime
    (
    @InputDate DATETIME,
    @NewTime TIME
    )
    RETURNS DATETIME
    AS
    BEGIN

    DECLARE @NewDate DATETIME,
    @StrDate VARCHAR(30),
    @StrTime VARCHAR(30)

    SET @StrDate = CONVERT(VARCHAR, @InputDate, 101);
    SET @StrTime = CONVERT(VARCHAR, @NewTime, 108);
    SET @StrDate = (@StrDate + ‘ ‘ + @StrTime);
    RETURN CONVERT(DATETIME,@StrDate,121);

    END

    Reply
    • Andy3B says:
      September 27, 2012 at 9:36 am

      Thanks Tony.

      I was using the same approach, and getting an conversion error all the time.

      Guess what: I was just missing the space between the date and time part.

      Reply

Leave a Reply