Combine Date and Time into Datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combine Date and Time into Datetime

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2012-01-26 : 10:22:48

Hi,
I have a table with a Date and a Time column and I want to combine them into a Datetime column. I googled it and found that:

Update mytable set dt = d + t

should work, but I get "Operand data type date is invalid for add operator".
How do I do this?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 10:36:29

you should cast and then concatenate


Update mytable set dt = cast(d as varchar(12)) + ' ' + cast(t as varchar(8))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-26 : 10:36:59

30 seconds

declare @d date, @t time; SELECT @d = '09/11/2001', @t ='09:03'
SELECT CONVERT(datetime2,CONVERT(varchar(10),@d) + ' ' +CONVERT(varchar(16),@t))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2012-01-26 : 10:39:20

Thank you. Cast and concatenate worked for me.

Dave

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-26 : 10:40:10

Or you can cast the DATE to DATETIME data type and use the addition operator, as in:

DECLARE @date Date, @time TIME;
SET @date = '20110126';
SET @time =  '10:34:38.780';
SELECT  CAST (@date AS DATETIME) + @time;

I am a little suspicious of this, but Microsoft says you can, so who am I to argue? http://msdn.microsoft.com/en-us/library/ms187716.aspx

It does not work with DATETIME2 datatype though.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 10:45:09

quote:
Originally posted by DaveBF

Thank you. Cast and concatenate worked for me.

Dave

One small point though

if you're sure that you want to consider date and time part you may be better off putting them in same field with type datetime. this will save some effort in concatenating them each time which involves unwanted cast operations also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources