Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Combining (concatenating) date and time into a datetime

Using SQL Server 2008, this query works great:

select CAST(CollectionDate as DATE), CAST(CollectionTime as TIME) from field 

Gives me two columns like this:

2013-01-25  18:53:00.0000000 2013-01-25  18:53:00.0000000 2013-01-25  18:53:00.0000000 2013-01-25  18:53:00.0000000     .     .     . 

I'm trying to combine them into a single datetime using the plus sign, like this:

select CAST(CollectionDate as DATE) + CAST(CollectionTime as TIME) from field 

I've looked on about ten web sites, including answers on this site (like this one), and they all seem to agree that the plus sign should work but I get the error:

Msg 8117, Level 16, State 1, Line 1
Operand data type date is invalid for add operator.

All fields are non-zero and non-null. I've also tried the CONVERT function and tried to cast these results as varchars, same problem. This can't be as hard as I'm making it.

Can somebody tell me why this doesn't work? Thanks for any help.

like image 975
Stanton Avatar asked Sep 04 '13 19:09

Stanton


People also ask

How do I concatenate a date and time in SQL?

To combine date and time column into a timestamp, you can use cast() function with concat(). select cast(concat(yourDateColumnName, ' ', yourTimeColumnName) as datetime) as anyVariableName from yourTableName; In the above concept, you will use cast() when your date and time is in string format.


1 Answers

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.

like image 69
Aaron Bertrand Avatar answered Sep 22 '22 01:09

Aaron Bertrand