Append Numeric Time To DateTime field

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)

Append Numeric Time To DateTime field

Author

Topic

eric_ht
Starting Member

37 Posts

Posted - 2010-06-23 : 14:42:44

I'm apparently doing something stupid and overlooking the obvious but I cannot get a numeric time field appended to a datetime field.

This is jde software that stores date and time in two separate numeric fields. I translate the date field into a datetime field successfully. I then want to append the time field into the datetime field. Everything I try adds the time to the datetime field instead of appending.

Thanks in advance

Sachin.Nand

2937 Posts

Posted - 2010-06-23 : 14:50:32

Convert the datetime and time field to varchar & then append it.
By the way how are you adding time to datetime?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

eric_ht
Starting Member

37 Posts

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

Currently I'm just using concat..which of course doesn't work.

Sachin.Nand

2937 Posts

Posted - 2010-06-23 : 14:55:30

quote:
Originally posted by eric_ht

Currently I'm just using concat..which of course doesn't work.

Is it Oracle?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

eric_ht
Starting Member

37 Posts

Posted - 2010-06-23 : 14:56:44

No it is Sql Server 2005

eric_ht
Starting Member

37 Posts

Posted - 2010-06-23 : 15:15:25

Obviously I am not understanding about converting the two varchar fields together. Here is the code that gives me the error: Conversion failed when converting datetime from character string.


DECLARE @CurrentDate varchar(10)
SET @currentDate = '2009-05-13'
DECLARE @Time float 
SET @Time = 144302
DECLARE @FinalDate datetime
set @FinalDate =  CONVERT(varchar(25), CAST(@currentDate as varchar(10)) + CAST(@Time as varchar(6)), 100)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-23 : 15:59:26

SELECT CONVERT(DATETIME, @CurrentDate + ' ' + STUFF(STUFF(REPLACE(STR(@Time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':'), 120)

N 56°04'39.26"
E 12°55'05.63"

eric_ht
Starting Member

37 Posts

Posted - 2010-06-23 : 16:05:03

SON OF A GUN! That worked.

Thanks for all y'alls help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources