SQL Server: combine date column and time column, insert into datetime column

Question

I have a database with a date and a time stored separately in datetime columns (not my idea). I need to combine their values to put into another table with a datetime column. As simple as this seems, I just don't seem to be able to do it.

I can get my date value:

cast(sampledate as date)   --  returns '2014-11-01'

And my date value:

cast(CollectionTime as time)   -- returns '06:46:00.0000000'

I've tried a few different ways of putting them together that look OK.

For example:

concat(cast(sampledate as date) , ' ' , cast(CollectionTime as time))  -- returns '2014-11-05 08:14:00.0000000'

But when I try to insert this into a datetime column, or even just cast it as a datetime value, it doesn't work:

cast(concat(cast(sampledate as date) , ' ' , cast(CollectionTime as time)) as datetime)
--  I get the error 'Conversion failed when converting date and/or time from character string.'

This link warned me against using the FORMAT function, and I've been to some other sites that tell me what NOT to do, but I just can't seem to do this simple thing. Can anyone help? Thanks.

EDIT: Figured it out. This link solved it for older versions of SQL, but not current versions. However, it works fine if you cast to datetime2(0), not datetime.

Does this answer your question? https://stackoverflow.com/questions/18622384/combining-concatenating-date-and-time-into-a-datetime — Hassan Ashas, Aug 20 '21 at 20:45
Hassan, thanks, those used to work in SQL2008, but I've tried that, and it doesn't seem to work in 2016. Should have specified that in my question. — buckshot, Aug 20 '21 at 20:48
The duplicate link [fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=7b17efdf01f1f88ea2e0ea0e18b07f19) works just fine in current versions of sql server — SMor, Aug 20 '21 at 21:01
You can simply convert two elements to datetime and add them together. Take a peek at https://stackoverflow.com/questions/68519014/concatenating-date-and-time-in-sql-to-get-datetime-1900-01-01-instead-of-actual/68519068#68519068 — John Cappelletti, Aug 20 '21 at 21:05

John Cappelletti · Answer 1 · 2021-08-22T19:23:46.427

As I commented above, here is an example where you can add the two datetimes together.

If either column is NOT datetime, simply convert that column to a datetime

Declare @YourTable table (sampledate datetime,CollectionTime datetime)
Insert Into @YourTable values
('2019-06-25 00:00:00','09:09:31')


Select *
      ,NewDateTime = sampleDate + CollectionTime
 From  @YourTable

Results

sampledate                CollectionTime            NewDateTime
2019-06-25 00:00:00.000   1900-01-01 09:09:31.000   2019-06-25 09:09:31.000

SQL Server: combine date column and time column, insert into datetime column

1 Answers1