CODEWITHSUNDEEP
sqlsql-serverdatetimesqldatetime
Wymo Oo
Wymo Oo

Reputation: 65

How to add hours, minutes and seconds to a datetime column in SQL?

I have the following datetime column in my table "UpdatedTime"

Sample value: 2021-12-31 00:00:00.000

I need to manipulate the hours, minutes and seconds to become 23:59:59 (i.e. a second before midnight.)

Expected Value: 2021-12-21 23:59:59.000

Thanks in advance

Upvotes: 5

Views: 23701

Answers (3)

Ben Thul
Ben Thul

Reputation: 32697

Assuming you're on a sufficiently modern version of MSSQL, this should work:

DECLARE @dt DATETIME = '2021-12-31 00:00:00.000'
SELECT DATETIMEFROMPARTS(
    DATEPART(YEAR, @dt),
    DATEPART(MONTH, @dt),
    DATEPART(DAY, @dt),

    23, /* hour */
    59, /* minute */
    59, /* second */

    0 /* fractional seconds*/

);

I'd also be remiss if I didn't mention that by calculating the datetime you've requested, you might be employing an antipattern. If your goal in calculating the above is to do something like:

select *
from dbo.yourTable
where DateCreated >= @StartOfDay
   and DateCreated <= @EndOfDay;

You'll eventually have an issue with the above if the DateCreated column admits a value like '2021-12-21 23:59:59.997'.

If that is indeed your goal, it's better to write that query as:

select *
from dbo.yourTable
where DateCreated >= @StartOfDay
   and DateCreated < @StartOfNextfDay;

That is use a half-open interval instead of a fully closed interval.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I would use dateadd(), but I would phrase it as:

select dateadd(second, 24 * 60 * 60 - 1, UpdatedTime)

Or just add a time value:

select UpdatedTime + '23:59:59'

Upvotes: 6

Aleix CC
Aleix CC

Reputation: 2089

You can use the DATEADD() function as follows (check SQL Fiddle for clarity):

SELECT 
  *, 
  DATEADD(hour, 23, DATEADD(minute, 59, DATEADD(second, 59, date_))) as updated_datetime
FROM dates_;

OUTPUT:

date_                   updated_datetime
----------------------- -----------------------
2021-01-01 00:00:00.000 2021-01-01 23:59:59.000

Upvotes: 1

Related Questions