Adding Date + TimeStamp of two different dates

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

Adding Date + TimeStamp of two different dates

Author

Topic

aoriju
Posting Yak Master

156 Posts

Posted - 2008-07-23 : 07:34:47

Which One Is Better....(Please Give Me Performance Issues)

My Aim Is to cancatenate One dates Datepart and another dates time part.....

a, SELECT Convert(Datetime,Convert(Varchar(12),Getdate(),109)+ right(GetDate(),7))

b, SELECT Created_On + Daily_order_Departure_Time
FROM TEST_Table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 07:42:14

Are Created_On and Daily_order_Departure_Time datetimes?
What is the result of adding '20080723' and '19:14:23'?

E 12°55'05.25"
N 56°04'39.16"

aoriju
Posting Yak Master

156 Posts

Posted - 2008-07-23 : 07:52:48

Both of them are datetimes

quote:
Originally posted by Peso

Are Created_On and Daily_order_Departure_Time datetimes?
What is the result of adding '20080723' and '19:14:23'?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 07:58:28

What is the accuracy? Include milliseconds? Only down to seconds?

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-23 : 07:58:35

if both are datetimes then performing direct datetime operations between then will always be faster than
converting it to varchar.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 08:02:27

You can't simply add to datetimes.
See

declare @a datetime, @b datetime
select @a  =1, @b = 1
select @a, @b, @a+@b

E 12°55'05.25"
N 56°04'39.16"

aoriju
Posting Yak Master

156 Posts

Posted - 2008-07-23 : 08:03:36

Both are datetime ....Any performance issue...On direct adding

quote:
Originally posted by spirit1

if both are datetimes then performing direct datetime operations between then will always be faster than
converting it to varchar.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 08:04:05

Here is how you "merge" two datetimes.

DECLARE	@DatePartOnly DATETIME,
	@TimePartOnly DATETIME,
	@Result DATETIME

SELECT	@DatePartOnly = GETDATE() - 2,
	@TimePartOnly = GETDATE() + 0.000234

-- Before
SELECT	@DatePartOnly AS DatePartOnly,
	@TimePartOnly AS TimePartOnly,
	@Result AS Result

SET	@Result = DATEDIFF(DAY, 0, @DatePartOnly) + DATEADD(DAY, DATEDIFF(DAY, @TimePartOnly, 0), @TimePartOnly)

-- After
SELECT	@DatePartOnly AS DatePartOnly,
	@TimePartOnly AS TimePartOnly,
	@Result AS Result

E 12°55'05.25"
N 56°04'39.16"

aoriju
Posting Yak Master

156 Posts

Posted - 2008-07-23 : 08:06:58

See peso

Both are datetime coming from table we can directly add.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 08:13:26

Only if DateColumn has no time other than '00:00:00.000' and
TimeColumn has no date other than '1900-01-01' you can add them together such as

DateColumn + TimeColumn

If either column has more information than it should, you will get wrong result.

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-23 : 08:28:56

quote:
Originally posted by Peso

You can't simply add to datetimes.
See
declare @a datetime, @b datetime
select @a =1, @b = 1
select @a, @b, @a+@b

E 12°55'05.25"
N 56°04'39.16"

What do you mean? Of course you can add two DateTimes -- and, in fact, that is how you should combine a "date only" and a "time only" value.

- Jeff
http://weblogs.sqlteam.com/JeffS

aoriju
Posting Yak Master

156 Posts

Posted - 2008-07-23 : 08:33:18

See,

fields are datetime in table ...then we can add..i am looking for performance issues

quote:
Originally posted by jsmith8858

quote:
Originally posted by Peso

You can't simply add to datetimes.
See
declare @a datetime, @b datetime
select @a =1, @b = 1
select @a, @b, @a+@b

E 12°55'05.25"
N 56°04'39.16"

What do you mean? Of course you can add two DateTimes -- and, in fact, that is how you should combine a "date only" and a "time only" value.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 08:34:10

It was a bad example. I explained the subject in more detail a few minutes later.

The example should have been

declare @a datetime, @b datetime
select @a =1.3656, @b = 2.2343423
select @a, @b, @a+@b

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources