Syntax error converting datetime from character string

[annub]

I am writting this query to update TC03_date col.
update varreport
set tc03_date = convert(datetime,mydate + ' '+ substring(right(('000' + mytime),4), 1,2)+ ':' + substring(right(('000' + mytime),4), 3,2))

tc03_date col is datetime, datatype and mydate and mytime is varchar. Getting this error Syntax error converting datetime from character string

 

[SQLBill]

You are using CONVERT incorrectly. There are only three values allowed in CONVERT.

Refer to Books OnLine, use INDEX tab and enter CONVERT, select the TRANSACT SQL option.

Datatype - this is VARCHAR, CHAR, MONEY, DATETIME, etc.
the length (varchar(10), etc) is optional.
Expression - this is the column or data you want to convert
Style - this is the format you want it to be in (optional)

SELECT CONVERT(DATETIME, '2003-06-30', 101)

will return: 06/30/2003

Try this: (Warning! I have not tested it)

update varreport
set tc03_date = convert(datetime,(mydate + ' '+ substring(right(('000' + mytime),4), 1,2)+ ':' + substring(right(('000' + mytime),4), 3,2)))


-SQLBill

 

[annub]

I tried all different ways still get the same error. I tried to write this way

declare @temp_date varchar(20)
select @temp_date = (mydate + ' '+ substring(right(('000' + mytime),4), 1,2)+ ':' + substring(right(('000' + mytime),4), 3,2))from varreport
update varreport set
tc03_date = convert(datetime, @temp_date, 112)

But i dont get right result, In tc03_date i get is same date and same time for each record.

Thanks
Ann

 

[SQLBill]

What format is mydate and mytime in? In other words, what does the data look like?

ex: mydate is mm/dd/yyyy
mytime is hh:mmAM


-SQLBill

 

[annub]

MYDATE = 2003616 and MYTIME is not always 4 digits. My data looks something like this 2345,715,1234.


Thanks
Ann

 

[Siggy19]

Can you format MYDATE and MYTIME so that they ARE fixed length ?

That will be easiest.

If you have MYDATE = YYYYMMDD and MYTIME = HHMM then the following should work;

[/CODE]
UPDATE varreport
SET tc03_date = CONVERT(DATETIME, (SUBSTRING(mydate, 5, 2) + '/' + RIGHT(mydate, 2) + '/' + LEFT(mydate, 4) + ' ' + LEFT(mytime, 2) + ':' + RIGHT(mytime, 2) + ':' + '0000'), 101)

[/CODE]

You might not need the "+ ':' + '0000'"... I can't remember offhand.

If you are using variable length fields, you will need to calculate the various offsets using LENGTH functions etc.

 

[SQLBill]

Run this script:

select mydate + ' '+ substring(right(('000' + mytime),4), 1,2)+ ':' + substring(right(('000' + mytime),4), 3,2))

What do you get? Any errors? Any commas?

-SQLBill