• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 58
  • Last Modified:

CAST(date_column + time_column as datetime)

Hi guys

Question for you:  What's the fastest way to add a date column and a time column and cast it as a single datetime column?

Example:
Declare @dt date = '20160727', @tm time = '03:30:00'
SELECT CAST( ???? as datetime) 

Open in new window


Thanks in advance.
Jim
0
Jim Horn
Asked:
Jim Horn
2 Solutions
 
Dustin SaundersDirector of OperationsCommented:
I use:
SELECT CONVERT(DATETIME, CONVERT(CHAR(8), @dt, 112) 
  + ' ' + CONVERT(CHAR(8), @tm, 108))

Open in new window

0
 
Manuel Marienne-DuchêneITMCommented:
CAST(@dt + @tm AS DATETIME) with space in first position in time
0
 
Dustin SaundersDirector of OperationsCommented:
But don't you need extra steps to add in the space in time (say if it were from a column)?  [maybe we should add physics to the categories if we're talking about spacetime :P ]

Anyways, I guess if I used it a lot I'd just stick it in a UDF and call that when needed (seems easiest for me though I don't know if there's a more performance efficient way?)

CREATE FUNCTION concatDT (@dt date,@tm time)
RETURNS datetime
AS
BEGIN
RETURN(	SELECT CONVERT(DATETIME, CONVERT(CHAR(8), @dt, 112) 
	+ ' ' + CONVERT(CHAR(8), @tm, 108)))
END

Open in new window

SELECT dbo.concatDT(@dt,@tm)

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Scott PletcherSenior DBACommented:
SELECT CAST(@dt AS datetime) + @tm
0
 
Dustin SaundersDirector of OperationsCommented:
I tried:
Declare @dt date = '20160727', @tm time = '03:30:00'
SELECT CAST(@dt AS datetime) + @tm

Open in new window

and got:
Msg 402, Level 16, State 1, Line 2
The data types datetime and time are incompatible in the add operator.


but
Declare @dt date = '20160727', @tm time = '03:30:00'
SELECT CAST(@dt AS datetime) + CAST(@tm AS datetime)

Open in new window


does work and is less code than mine.
0
 
Scott PletcherSenior DBACommented:
I tried:
Declare @dt date = '20160727', @tm time = '03:30:00'
SELECT CAST(@dt AS datetime) + @tm
and got [an error]

Hmm, worked fine for me, at least through SQL 2012.  Maybe that's a later SQL version restriction?  I know they've made some changes in that area.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Thanks guys.  Chose Scott's answer as it was a less expensive option, which works on my 2008R2 box.
0
 
Dustin SaundersDirector of OperationsCommented:
Yeah, I agree that's better way.  It's weird that it doesn't run on my SQL test servers (I tried on SQL 2012 SP3 and 2014); so must have appeared somewhere around there, or maybe I angered some vengeful SQL God.

Might be a good note or item to test though, in case you upgrade production SQL versions in the future it might break the query.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now