Adding Date and Time - 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)

Adding Date and Time

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-11 : 13:22:20

StartDate and StartTime (both are datetime field)

How do I concatenate startdate and starttime ? (my target column is datetime)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-11 : 13:26:37

try this
assuming startdate has datepart alone and starttime timepart


UPDATE table SET targetcol=dateadd(ss,datediff(ss,0,starttime),startdate)

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-11 : 13:30:25

PERFECT. THANKS.

some rows are missing the starttime, how do i then just load the startdate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-11 : 13:32:51

missing means NULL or 00:00:00.000 timepart?

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-11 : 13:33:31

NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-11 : 13:34:59

UPDATE table SET targetcol=dateadd(ss,NULLIF(datediff(ss,0,starttime),0),startdate)

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-11 : 13:36:40

Didnt do the trick? h

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-11 : 13:40:45

oops sorry i had it otherway around

UPDATE table SET targetcol=dateadd(ss,ISNULL(datediff(ss,0,starttime),0),startdate)

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-12-11 : 13:43:39

GREAT. worked like charm. Thanks Visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-11 : 13:50:03

welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-12 : 01:28:13

Another way

UPDATE table SET targetcol=startdate+isnull(starttime,0)

Madhivanan

Failing to plan is Planning to fail

doran_doran
Posting Yak Master

179 Posts

Posted - 2010-02-12 : 12:25:03

Why am I getting following error?? I am using this code dateadd(ss,ISNULL(datediff(ss,0,starttime),0),startdate)

Difference of two datetime columns caused overflow at runtime.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 12:27:54

quote:
Originally posted by doran_doran

Why am I getting following error?? I am using this code dateadd(ss,ISNULL(datediff(ss,0,starttime),0),startdate)

Difference of two datetime columns caused overflow at runtime.

may be because result datediff(ss,0,starttime) was outside range of integer value

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

doran_doran
Posting Yak Master

179 Posts

Posted - 2010-02-12 : 12:29:16

Visakh, Thanks for the prompt respond. What is the remedy for this issue?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 12:34:48

what values does starttime typically consists of?

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

doran_doran
Posting Yak Master

179 Posts

Posted - 2010-02-12 : 12:42:29

I see a variety (and I have no control over the data). I am simply taking this and loading into another database.
I see followings
NULL
1899-12-30 12:00:00.000
1899-12-30 15:37:00.000
1900-01-01 11:42:00.000
1900-01-01 23:17:00.000
2009-12-14 01:01:00.000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 12:45:09

in such cases what should be your result of concatenation? take time part alone? i dont think thats logical

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

doran_doran
Posting Yak Master

179 Posts

Posted - 2010-02-12 : 12:47:18

That's what I intended to do. My date is in one field and time is one field. I want to put the date and time from respected fields into one datetime field called "StartDateTime". Any good syntax you have in mind?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 12:56:02

try this


UPDATE table SET targetcol=startdate + ' ' + CONVERT(varchar(8),starttime,8)

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

doran_doran
Posting Yak Master

179 Posts

Posted - 2010-02-12 : 13:06:29

Works but shows null where there is null in time or date field. so if null in time then just the date should appear and visa versa

doran_doran
Posting Yak Master

179 Posts

Posted - 2010-02-12 : 13:13:13

This is working but dont know if it's good enough.

StartDate + ' ' + ISNULL(CONVERT(varchar(8), StartTime,8),'00:00:00.000')

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources