How to Add Time to Datetime in SQL

How to add time to DateTime in SQL

Try this

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), '03:30:00')

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

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'

How to combine date from one field with time from another field - MS SQL Server

You can simply add the two.

• if the Time part of your Date column is always zero
• and the Date part of your Time column is also always zero (base date: January 1, 1900)

Adding them returns the correct result.

SELECT Combined = MyDate + MyTime FROM MyTable

Rationale (kudos to ErikE/dnolan)

It works like this due to the way the date is stored as two 4-byte
Integers with the left 4-bytes being the date and the right
4-bytes being the time. Its like doing $0001 0000 + $0000 0001 =
$0001 0001

Edit regarding new SQL Server 2008 types

Date and Time are types introduced in SQL Server 2008. If you insist on adding, you can use Combined = CAST(MyDate AS DATETIME) + CAST(MyTime AS DATETIME)

Edit2 regarding loss of precision in SQL Server 2008 and up (kudos to Martin Smith)

Have a look at How to combine date and time to datetime2 in SQL Server? to prevent loss of precision using SQL Server 2008 and up.

How to add minutes to the time part of datetime

Use DATEADD:

SELECT DATEADD(mi, @increase,   @shift_start_time);

db<>fiddle demo

How do I add 1 hour to datetime SQL column data?

You should consider using DATETIMEOFFSET as your daatype instead of DATETIME.

Defines a date that is combined with a time of a day that has time
zone awareness and is based on a 24-hour clock.

You can use it with SYSDATETIMEOFFSET().

Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.

Example:

 CREATE TABLE DateTest (id INT, order_date DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET())
 INSERT INTO DateTest (id) VALUES (1)
 SELECT * FROM DateTest

Combining (concatenating) date and time into a datetime

Assuming the underlying data types are date/time/datetime types:

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), CollectionDate, 112) 
  + ' ' + CONVERT(CHAR(8), CollectionTime, 108))
  FROM dbo.whatever;

This will convert CollectionDate and CollectionTime to char sequences, combine them, and then convert them to a datetime.

The parameters to CONVERT are data_type, expression and the optional style (see syntax documentation).

The date and time style value 112 converts to an ISO yyyymmdd format. The style value 108 converts to hh:mi:ss format. Evidently both are 8 characters long which is why the data_type is CHAR(8) for both.

The resulting combined char sequence is in format yyyymmdd hh:mi:ss and then converted to a datetime.

Adding time(hh:mm:ss) to a datetime field in SQL

Use MySQL's built-in addtime() function instead of the + operator:

SELECT checkdate,
       run_datetime,
       run_duration,
       addtime(cast(run_datetime as datetime), cast(run_duration as time)) as readytime
FROM table

The + operator converts the datetimes to numbers and adds them up as such.

---

Related Topics