datetime add datetime - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

datetime add datetime

Author

Topic

dalibor
Starting Member

21 Posts

Posted - 2012-12-17 : 10:42:47

Hello,

I have this problem:


DECLARE @hhmmss datetime
DECLARE @dt datetime

SET @hhmmss = {ts '1899-12-31 11:10:00.000'}
SET @dt = {ts '2012-03-12 00:00:00.000'}


select dateadd(s,datediff(s,0,convert(datetime,@hhmmss)),@dt)
select @dt + @hhmmss

--In both cases result is:
2012-03-11 11:10:00.000

I dont understand, why 2012-03-11?
why is not the result 2012-03-12?

I need date(@dt) + time(@hhmmss).
This is 2012-03-12 11:10:00.000.

Please help.

Dalibor

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-17 : 10:54:39

This is because unlike for example Excel where the reference date is 12/30/1899, the reference date in SQL Server is 1/1/1900. So use

SET @hhmmss = {ts '1900-01-01 11:10:00.000'}

dalibor
Starting Member

21 Posts

Posted - 2012-12-17 : 12:16:45

I have MS SQL Server 2008 R2 Express, but i need solution for SQL server 2005.

dalibor
Starting Member

21 Posts

Posted - 2012-12-17 : 12:36:19

Yes, i have this solution:

DECLARE @hhmmss datetime
DECLARE @dt datetime
DECLARE @newDatetime datetime
SET @hhmmss = {ts '1899-12-31 11:10:00.000'}
SET @dt = {ts '2012-03-12 00:00:00.000'}

DECLARE @Hour int
DECLARE @Min int

DECLARE @shhmm varchar(20)
SET @Hour = DATEPART(HOUR,@hhmmss)
SET @Min = DATEPART(MINUTE,@hhmmss)

SET @newDatetime = DATEADD(HOUR,@Hour,@dt)
SET @newDatetime = DATEADD(MINUTE,@Min,@newDatetime)
SELECT @newDatetime

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-12-24 : 07:54:07

Also you don't need ODBC notations

DECLARE @hhmmss datetime
DECLARE @dt datetime

SET @hhmmss = '11:10:00.000'
SET @dt = '2012-03-12 00:00:00.000'

select dateadd(s,datediff(s,0,convert(datetime,@hhmmss)),@dt)
select @dt + @hhmmss

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-24 : 15:34:45

quote:
Originally posted by dalibor

Hello,

I have this problem:

DECLARE @hhmmss datetime
DECLARE @dt datetime

SET @hhmmss = {ts '1899-12-31 11:10:00.000'}
SET @dt = {ts '2012-03-12 00:00:00.000'}

select dateadd(s,datediff(s,0,convert(datetime,@hhmmss)),@dt)
select @dt + @hhmmss

--In both cases result is:
2012-03-11 11:10:00.000

I dont understand, why 2012-03-11?
why is not the result 2012-03-12?

I need date(@dt) + time(@hhmmss).
This is 2012-03-12 11:10:00.000.

Please help.

Dalibor

see this to understand how dates are internally stored in sql server

http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources