How do I add a date and time

I have a the following error:
     Conversion failed when converting datetime from character string.
when I run this query
UPDATE CAL
     SET TRANDATE=(SELECT
                                   CASE WHEN APPT = 0 THEN TRANDATE
                                                                      ELSE CONVERT(DATETIME, CAST(TRANDATE + ' ' + REPLACE(APPT, RIGHT(APPT, 2), ':' + RIGHT(APPT, 2)) AS VARCHAR))
               END
                 )
The SELECT QUERY runs fine in the Query window.
How do I resolve the Error?
Thanks
LIFEEXPERTAsked:
Who is Participating?
 
brad2575Commented:
If all you want is to put the time from APPT to the TranDate then this should work:

select Cast(CONVERT(varchar(10), TranDate, 101) + ' ' + REPLACE(APPT,  RIGHT(APPT, 2), ':' + RIGHT(APPT, 2)) as DateTime) as Trandate

From the above there was an erroor and it looks like you are still trying to add the TranDate to a date and time
0
 
brad2575Commented:
try

UPDATE CAL
     SET TRANDATE= cast((SELECT
                                   CASE WHEN APPT = 0 THEN TRANDATE
                                                                      ELSE CONVERT(DATETIME, CAST(TRANDATE + ' ' + REPLACE(APPT, RIGHT(APPT, 2), ':' + RIGHT(APPT, 2)) AS VARCHAR))
               END
                 ) as datetime)
0
 
LIFEEXPERTAuthor Commented:
Sorry same error:
Conversion failed when converting datetime from character string.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Shannon_LowderCommented:
Sounds like the error is in the conversion to datetime.  Try selecting with this in the where clause  ISDATE(CONVERT(DATETIME, CAST(TRANDATE + ' ' + REPLACE(APPT, RIGHT(APPT, 2), ':' + RIGHT(APPT, 2)) AS VARCHAR)))  = 1

Then for those not updated, you'll need to define a default date.

You could also use the ISDATE in your update, and case where ISDATE = 0, put that default date into trandate.
---
Shannon Lowder
Database Engineer
0
 
LIFEEXPERTAuthor Commented:
I had tried that but no success:
UPDATE OPTRAN
SET CAL=
                   (SELECT  CASE WHEN APPT = 0 THEN TRANDATE ELSE CONVERT(DATETIME,
                                   CAST(TRANDATE + ' ' + REPLACE(APPT, RIGHT(APPT, 2), ':' + RIGHT(APPT, 2)) AS VARCHAR))
                                   END AS Expr1)
WHERE  (ISDATE(CONVERT(DATETIME, CAST(TRANDATE + ' ' + REPLACE(APPT, RIGHT(APPT, 2), ':' + RIGHT(APPT,
               2)) AS VARCHAR))) = 1)

I don't know if it makes a difference but TRANDATE is of data type smalldate
0
 
brad2575Commented:
Small date still has time associated with it so you are trying to cast Trandate which has a date and time to a date and time with more time on it with this line:

 CAST(TRANDATE + ' ' + REPLACE(APPT, RIGHT(APPT, 2), ':' + RIGHT(APPT, 2)) AS VARCHAR))


It may be defaulted to midnight but it would still have a time on it.

0
 
LIFEEXPERTAuthor Commented:
Ok...I see that but even if I convert it to varchar and then convert it back to datetime... the error still occurs.
CONVERT(varchar(10), TRANDATE, 101)) + CONVERT(DATETIME, CAST('01/01/1900' + ' ' + REPLACE(APPT,  RIGHT(APPT, 2), ':' + RIGHT(APPT, 2)) AS VARCHAR)) AS TRANDATE

There has got be an easier way to add a value '1700' to a datetime field. :(
0
 
LIFEEXPERTAuthor Commented:
I figured it out. It data being passed was not in a format of '0035' int APPT field.

Thanks for the suggestions
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.