Date Conversion in SQL Server

Hi folks,

Here are the fields I have

eventDate = smallDateTime 2005-12-12 00:00:00
eventTime = varchar(20) 1:00:00
newEventDate = dateTime [desired result: 2005-12-12 1:00:00]

When I run the following script:
update healthEvent
set newEventDate = cast(substring(convert(varchar,eventDate,120),1,10 )+' '+eventTime as DateTime)

I get "Syntax error converting datetime from character string." error.

Any ideas why?

Thanks!

-Parul-- Comment
What in the name of all that is relational and logical would somebody design a POS like this?

-- Answer
Because you are not putting them together properly.

The date string at 1 AM is like this:
2007-05-17 01:00:00.000

The date string at 1 PM is like this:
2007-05-17 13:00:00.000

You are trying to slam the 1 against the space after the date without the leading zero.

Start from the inside and work out. When I convert, I like to explicitly define the size of my char variable inside of the convert instead of letting a varchar guess at what i want (convert(char(10),eventDate,120).

In this case I would use an 11 char convert to pick up the space after the date portion plus a 0 plus the ltrim(rtrim(time)) component.


declare @.eventDate smallDateTime
select @.eventDate = '2005-12-12 00:00:00'
declare @.eventTime varchar(20)
select @.eventTime = '1:00:00'
declare @.newEventDate dateTime --[desired result: 2005-12-12 1:00:00]
set @.newEventDate = cast(substring(convert(varchar,@.eventDate,120),1,1 0 )+' 0'+@.eventTime as DateTime)
select @.newEventDate
-- Results
----------------
2005-12-12 01:00:00.000
(1 row(s) affected)|||Thanks so much for your reply.

Unfortunatley, I am still getting the same error as I try to insert @.newEventDate into another date field.|||Plus do I need that leading zero at all times? What if I have @.eventTime as '11:00:00'|||You will have to do a CASE statement for your time segment on the datalength of the ltrim(rtrim(time segment)) . If len = 5 add the zero else don't.