Join Date from DateTime with Time from 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

Join Date from DateTime with Time from DateTime

Author

Topic

Valein
Starting Member

13 Posts

Posted - 2010-01-27 : 03:26:20

Hey guys - quick question here:

I got one datetime field with a date and another datetime field with the time.

What would a select look like that list these two datetime fields as one datetime column with date and time combined in the same column?

Thanks for any help!

Stian

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-27 : 03:31:04

It look like

select convert(varchar,date_col),101)+' '+convert(varchar,time_col,108)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-27 : 03:40:18

quote:
Originally posted by senthil_nagore

It look like

select convert(varchar,date_col),101)+' '+convert(varchar,time_col,108)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Why are you complicating it?

It should be

select date_col+time_col from your_table

Madhivanan

Failing to plan is Planning to fail

Valein
Starting Member

13 Posts

Posted - 2010-01-27 : 04:15:59

Thanks a lot guys - I'll try it out!

Stian

Valein
Starting Member

13 Posts

Posted - 2010-01-27 : 04:30:59

It works very well with the following

select convert(nvarchar,Sum_MoeteDato,101)+' '+convert(nvarchar,Sum_MoeteTid,108) FROM Sum_UtvMoeter

Just a last question - how do I convert the result into a datetime value? I am storing this in another table and want to keep it as a datetime, not varchar.

Stian

P.s. Madhivanan, I could not get your suggestion to work properly. Probably because the datetime value for time also contains a date.

For example, with a date value of "2007-08-23 00:00:00.000" and a time value of "1899-12-30 21:00:00.000", the result I get is "2007-08-21 21:00:00.000" - not quite sure what happened there.

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 04:42:39

If Sum_MoeteTid is the time only, and when you SELECT it you see 1900-01-01, plus the time, you can just "add" that to your date

select Sum_MoeteDato + Sum_MoeteTid FROM Sum_UtvMoeter

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 04:44:33

"a time value of "1899-12-30 21:00:00.000""

Just seen that ... not sure why that would be -27 hours from 1900-01-01 ?

Are all your times in 1899, or are most 1900-01-01 ?


select MIN(Sum_MoeteTid), MAX(Sum_MoeteTid) FROM Sum_UtvMoeter WHERE Sum_MoeteTid IS NOT NULL

will give the the Min/Max range values

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-27 : 05:51:17

quote:
Originally posted by madhivanan

quote:
Originally posted by senthil_nagore

It look like

select convert(varchar,date_col),101)+' '+convert(varchar,time_col,108)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Why are you complicating it?

It should be

select date_col+time_col from your_table

Madhivanan

Failing to plan is Planning to fail

Sorry madhi,

I thought '+' acts as a arithmetic operator when the two operands are datetime , but i don't know how it will acts as a concatenation operator when the operands are datetime? at least any one of the operand must be a varchar datatype??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-27 : 05:55:07

quote:
Originally posted by Valein

It works very well with the following

select convert(nvarchar,Sum_MoeteDato,101)+' '+convert(nvarchar,Sum_MoeteTid,108) FROM Sum_UtvMoeter

Just a last question - how do I convert the result into a datetime value? I am storing this in another table and want to keep it as a datetime, not varchar.

Stian

P.s. Madhivanan, I could not get your suggestion to work properly. Probably because the datetime value for time also contains a date.

For example, with a date value of "2007-08-23 00:00:00.000" and a time value of "1899-12-30 21:00:00.000", the result I get is "2007-08-21 21:00:00.000" - not quite sure what happened there.

select cast(convert(nvarchar,Sum_MoeteDato,101)+' '+convert(nvarchar,Sum_MoeteTid,108) as datetime) FROM Sum_UtvMoeter

Read more about datatype convention in BOL.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Valein
Starting Member

13 Posts

Posted - 2010-01-27 : 05:58:10

quote:
Originally posted by Kristen

"a time value of "1899-12-30 21:00:00.000""

Just seen that ... not sure why that would be -27 hours from 1900-01-01 ?

Are all your times in 1899, or are most 1900-01-01 ?

select MIN(Sum_MoeteTid), MAX(Sum_MoeteTid) FROM Sum_UtvMoeter WHERE Sum_MoeteTid IS NOT NULL

will give the the Min/Max range values

All of them are 1899

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 05:59:28

DATETIME is just a floating point number (slight simplification!). So if a datetime value is time-only the date part will effectively be "0" (which is "visualised" as 1900-01-01). So you can add a time-only datetime to a date (or a date+time) value safely - its just arithmetic, no implicit Cast involved or anything spooky.

Equally you can get the difference between two datetimes with Date1 - Date2 - the result is the "time" between them, so you might wind up with 1900-01-02 01:00:00 if the amount of time is 25 hours (i.e. 1 day plus 1 hour)

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 06:01:31

Here's a little test rig:


DECLARE @MyTime datetime,
	@MyDate datetime

SELECT	@MyTime = CONVERT(datetime, '01:02:03'),
	@MyDate = CONVERT(datetime, '20100127')

SELECT	@MyDate AS [Date],
	@MyTime AS [Time],
	@MyDate + @MyTime AS [Combined]

Valein
Starting Member

13 Posts

Posted - 2010-01-27 : 06:02:20

quote:
Originally posted by senthil_nagore

quote:
Originally posted by Valein

It works very well with the following

select convert(nvarchar,Sum_MoeteDato,101)+' '+convert(nvarchar,Sum_MoeteTid,108) FROM Sum_UtvMoeter

Just a last question - how do I convert the result into a datetime value? I am storing this in another table and want to keep it as a datetime, not varchar.

Stian

P.s. Madhivanan, I could not get your suggestion to work properly. Probably because the datetime value for time also contains a date.

For example, with a date value of "2007-08-23 00:00:00.000" and a time value of "1899-12-30 21:00:00.000", the result I get is "2007-08-21 21:00:00.000" - not quite sure what happened there.

select cast(convert(nvarchar,Sum_MoeteDato,101)+' '+convert(nvarchar,Sum_MoeteTid,108) as datetime) FROM Sum_UtvMoeter

Read more about datatype convention in BOL.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Thanks Senthil - I did try that cast from reading up on datatype conversion, but I get this error message:

"The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value."

This probably happens because some of the Time fields have a null value - could that be it?

Stian

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 06:05:18

"All of them are 1899"

Hmmm ... I wonder if that is some sort of timezone thing.

What does my little test rig, above, show you?

I get


Date                    Time                    Combined
----------------------- ----------------------- -----------------------
2010-01-27 00:00:00.000 1900-01-01 01:02:03.000 2010-01-27 01:02:03.000

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-27 : 06:06:00

No, if the some of the record is null it returns as null..

Its Working for me..

Try it!

select cast(convert(nvarchar,getdate(),101)+' '+convert(nvarchar,getdate(),108) as datetime)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 06:07:40

"select cast(convert(nvarchar,getdate(),101)+' '+convert(nvarchar,getdate(),108) as datetime)"

The conversion from datetime to string and back again is "expensive".

Doesn't matter for a one-off, or small database, but not ideal

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-27 : 06:11:10

Ya ofcourse, can you suggest any other way?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-27 : 06:14:53

Here we have bad data, else we can try as madhi's

P.s. Madhivanan, I could not get your suggestion to work properly. Probably because the datetime value for time also contains a date.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-27 : 06:14:55

quote:
Originally posted by Valein

It works very well with the following

select convert(nvarchar,Sum_MoeteDato,101)+' '+convert(nvarchar,Sum_MoeteTid,108) FROM Sum_UtvMoeter

Just a last question - how do I convert the result into a datetime value? I am storing this in another table and want to keep it as a datetime, not varchar.

Stian

P.s. Madhivanan, I could not get your suggestion to work properly. Probably because the datetime value for time also contains a date.

For example, with a date value of "2007-08-23 00:00:00.000" and a time value of "1899-12-30 21:00:00.000", the result I get is "2007-08-21 21:00:00.000" - not quite sure what happened there.

Then you may need to add 2 days

select date_col+dateadd(day,2,time_col) from your_table

Madhivanan

Failing to plan is Planning to fail

Valein
Starting Member

13 Posts

Posted - 2010-01-27 : 06:20:29

Okay guys, tested some more with all of your brilliant suggestions.

Using Kristens test rig with the data sample in question from my database it looks like this:

DECLARE @MyTime datetime,
@MyDate datetime

SELECT @MyTime = CONVERT(datetime, '01:02:03'),
@MyDate = CONVERT(datetime, '2007-08-23')

SELECT @MyDate AS [Date],
@MyTime AS [Time],
@MyDate + @MyTime AS [Combined]

This give me the following error message:
"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

If I replace the date '2007-08-23' with '20070823'
...I get the correct result, which is '2007-08-23 01:02:03.000'

Seems to have something to do with how my date is formatted.
What do you guys think?

Stian

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-27 : 06:21:45

quote:
Originally posted by senthil_nagore

Here we have bad data, else we can try as madhi's

P.s. Madhivanan, I could not get your suggestion to work properly. Probably because the datetime value for time also contains a date.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

declare @date datetime, @time datetime

select @date='2009-01-02',@time='16:45:44'
select @date,@time,@date+@time

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources