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

Question

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

What dbms are you talking about? For MSSQL there is [`DATEADD`](https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql) for myql there is [`ADDTIME`](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_addtime) — derpirscher, May 18 '21 at 07:30
Your sample value infers this, but it depends if your datetimes always start at 00:00:00.000. — Zorkolot, May 21 '21 at 18:55

score 5 · Accepted Answer · answered May 18 '21 at 11:12

5

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'

answered May 18 '21 at 11:12

Gordon Linoff

1,242,037
58
646
786

this work as well. very simple and efficient. thank you – Wymo Oo May 24 '21 at 15:57

Aleix CC · Answer 2 · 2021-05-18T09:34:22.127

1

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

edited May 18 '21 at 09:34

answered May 18 '21 at 07:38

Aleix CC

1,601
1
7
18

Awesome! Feel free to upvote the answer so other people will be able to refer it as correct :) Thanks! – Aleix CC May 18 '21 at 07:43
accepted the answer instead. unable to upvote cause not enought reputation. once again, thank you – Wymo Oo May 18 '21 at 08:19
1

Thanks for clarifying, @DaleK! Edited the reply accordingly and will not ask for upvotes again. – Aleix CC May 18 '21 at 09:35

score 0 · Answer 3 · answered May 18 '21 at 16:33

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.

yep, that is indeed a good point. I should consider the fraction of a seconds as well. thank you! — Wymo Oo, May 19 '21 at 04:37

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

3 Answers3