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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.