How to concatenate/convert date and time into a datetime?

Question

How to concatenate/convert date and time into a datetime?

SQL Guy 1

SSCoach

Points: 15979
More actions
August 3, 2015 at 10:18 am

#306388

We have 2 columns:
StartDate - data type Date
StartTime - data type Time
I need to combine them into a DateTime data type. For now, I convert each of them into varchar, insert space in between, and convert to DateTime, like this:
convert(datetime, convert(varchar(10), EndDate , 101) + ' ' + CONVERT(varchar(20), EndTime,24))
Is there a better, more elegant way to do this?
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76995 More actions · Answer 1

Converting dates/times to character and back is horribly inefficient, but if your table is small enough, it may still be fast enough to not significantly affect performance. Another option is the following.

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, StartDate), CAST(StartTime AS DATETIME))

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

djj SSCoach Points: 18831 More actions · Answer 2

I have had luck with

DECLARE @Date DATE = '1/1/2015', @Time TIME = '15:20'

SELECT cast(@Date as datetime) + cast(@Time as datetime)

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

djj (8/3/2015)
I have had luck with
DECLARE @Date DATE = '1/1/2015', @Time TIME = '15:20'
SELECT cast(@Date as datetime) + cast(@Time as datetime)

Indeed. On a couple million rows, that typically shaves some tens of milliseconds from the duration compared doing the math on the date first. Prior to 2012, you can also skip the conversion of the date, and save some tens of milliseconds more.

The latter time saving probably isn't worth it, since you'd have to change the code the moment you migrated to 2012+. Then again, neither time saving might be worth it if you only run this a few times a day or if the data set is small 🙂

Cheers!

drew.allen SSC Guru Points: 76995 More actions · Answer 4

djj (8/3/2015)
I have had luck with
DECLARE @Date DATE = '1/1/2015', @Time TIME = '15:20'
SELECT cast(@Date as datetime) + cast(@Time as datetime)

I expect that to stop working at some future point, because it depends on how the DATETIME datatype is implemented, whereas DATEADD is independent of how it is implemented. This only works with DATETIME and SMALLDATETIME for backwards compatibility. It does not work with newer datatypes such as DATE, TIME, or DATETIME2.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 5

djj (8/3/2015)
I have had luck with
DECLARE @Date DATE = '1/1/2015', @Time TIME = '15:20'
SELECT cast(@Date as datetime) + cast(@Time as datetime)

This looks like the most elegant solution. But I ran it in production, and it's performance slightly slower from what I had before, I am really surprised about that. The table is not large, it has 16 k rows.

But thanks anyway, it's much shorter. Especially where I have to calculate duration between StartDateTime and EndDateTime.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Just another way to do it:

declare @Date date = '20150803',

@Time time = '15:00:00';

select dateadd(second,datediff(second,0,@Time),cast(@Date as datetime))

go

drew.allen SSC Guru Points: 76995 More actions · Answer 7

Lynn Pettis (8/3/2015)
Just another way to do it:
declare @Date date = '20150803',
@Time time = '15:00:00';
select dateadd(second,datediff(second,0,@Time),cast(@Date as datetime))
go

I considered this approach, but discarded it, because the precision of the TIME datatype is smaller than a second, and I wasn't sure what precision the OP needed. This isn't a problem with DATE and DAY, because DAY is the smallest precision of a DATE field.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

drew.allen (8/4/2015)
Lynn Pettis (8/3/2015)
Just another way to do it:
declare @Date date = '20150803',
@Time time = '15:00:00';
select dateadd(second,datediff(second,0,@Time),cast(@Date as datetime))
go
I considered this approach, but discarded it, because the precision of the TIME datatype is smaller than a second, and I wasn't sure what precision the OP needed. This isn't a problem with DATE and DAY, because DAY is the smallest precision of a DATE field.
Drew

Then you change second to millisecond or what ever precision is necessary to capture the data appropriately. Just as you would change the cast from datetime to datetime2 if needed.

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 9

Jacob Wilkins (8/3/2015)
djj (8/3/2015)
I have had luck with
DECLARE @Date DATE = '1/1/2015', @Time TIME = '15:20'
SELECT cast(@Date as datetime) + cast(@Time as datetime)
Indeed. On a couple million rows, that typically shaves some tens of milliseconds from the duration compared doing the math on the date first. Prior to 2012, you can also skip the conversion of the date, and save some tens of milliseconds more.
The latter time saving probably isn't worth it, since you'd have to change the code the moment you migrated to 2012+. Then again, neither time saving might be worth it if you only run this a few times a day or if the data set is small 🙂
Cheers!

No, you wouldn't have to change it once you migrate. Despite how badly MS has put the screws to date and time functionality, the functionality of DATETIME still works just fine all the way through 2016. If they ever change DATETIME functionality, there will be a roadtrip to Redmond on my part and it won't be pretty when I get there. 🙂

Also, post your alternative and lets do a million row test to see what's up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 10

drew.allen (8/3/2015)
djj (8/3/2015)
I have had luck with
DECLARE @Date DATE = '1/1/2015', @Time TIME = '15:20'
SELECT cast(@Date as datetime) + cast(@Time as datetime)
I expect that to stop working at some future point, because it depends on how the DATETIME datatype is implemented, whereas DATEADD is independent of how it is implemented. This only works with DATETIME and SMALLDATETIME for backwards compatibility. It does not work with newer datatypes such as DATE, TIME, or DATETIME2.
Drew

Heh... like I said...

Jeff Moden (8/4/2015)[hrIf they ever change DATETIME functionality, there will be a roadtrip to Redmond on my part and it won't be pretty when I get there. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 11

SQL Guy 1 (8/3/2015)
djj (8/3/2015)
I have had luck with
DECLARE @Date DATE = '1/1/2015', @Time TIME = '15:20'
SELECT cast(@Date as datetime) + cast(@Time as datetime)
This looks like the most elegant solution. But I ran it in production, and it's performance slightly slower from what I had before, I am really surprised about that. The table is not large, it has 16 k rows.
But thanks anyway, it's much shorter. Especially where I have to calculate duration between StartDateTime and EndDateTime.

Curious... how many times did you test it. First runs are usually slower than subsequent runs because of compile time and caching.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 12

Jacob Wilkins (8/3/2015)
Indeed. On a couple million rows, that typically shaves some tens of milliseconds from the duration compared doing the math on the date first. Prior to 2012, you can also skip the conversion of the date, and save some tens of milliseconds more.
The latter time saving probably isn't worth it, since you'd have to change the code the moment you migrated to 2012+[/i]. Then again, neither time saving might be worth it if you only run this a few times a day or if the data set is small 🙂
Cheers!

I probably just wasn't clear enough the first time.

As the emphasis hopefully makes clearer than I did the first time, I was referring to djj's solution without converting the date to datetime. That did indeed change in 2012.

If this runs in 2012, I'll buy a hat and eat it 🙂

DECLARE @Date DATE = '1/1/2015', @Time TIME = '15:20'

SELECT @Date + cast(@Time as datetime)

Cheers!

djj SSCoach Points: 18831 More actions · Answer 13

You all realize I did not say it was better or worse or that it was the only way to go, all I said was I have had luck using it. 😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Jacob Wilkins (8/4/2015)
Jacob Wilkins (8/3/2015)
Indeed. On a couple million rows, that typically shaves some tens of milliseconds from the duration compared doing the math on the date first. Prior to 2012, you can also skip the conversion of the date, and save some tens of milliseconds more.
The latter time saving probably isn't worth it, since you'd have to change the code the moment you migrated to 2012+[/i]. Then again, neither time saving might be worth it if you only run this a few times a day or if the data set is small 🙂
Cheers!
I probably just wasn't clear enough the first time.
As the emphasis hopefully makes clearer than I did the first time, I was referring to djj's solution without converting the date to datetime. That did indeed change in 2012.
If this runs in 2012, I'll buy a hat and eat it 🙂
DECLARE @Date DATE = '1/1/2015', @Time TIME = '15:20'
SELECT @Date + cast(@Time as datetime)
Cheers!

You don't need to buy and eat a hat. Code ran fine on a SQL Server 2008 R2 system, failed on my SQL Server 2012 DE system.