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.
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.
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With