SQL Date Cast

Hi Experts,

I have the following code

UPDATE Invoices SET fldMP3Redeemed = 1 , fldMP3Lang = 1 , fldMP3RedeemDate = '08/27/2013 12:00:40 PM' WHERE CAST(paymentDate AS DATE) < CAST('08/19/2013' AS DATE);

Open in new window


But I am getting the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.

Open in new window


Any ideas?
APD TorontoSoftware DeveloperAsked:
Who is Participating?
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, if your field paymentDate  is already a date field:
WHERE paymentDate < convert(datetime, '08/19/2013' , 101) 

Open in new window

or shorter with implicit conversion:
WHERE paymentDate < '20130819' 

Open in new window


if it's actually a varchar field (you should change that ...
WHERE convert(datetime, paymentDate, 101) < convert(datetime, '08/19/2013' , 101) 

Open in new window


check ouf field fldMP3RedeemDate, that should be changed also:
fldMP3RedeemDate = convert(datetime, '08/27/2013 12:00:40 PM' , 101)

Open in new window

0
unknown_routineCommented:
How fldMP3RedeemDate  is defined in your database table?


Is it a date? if so
you need to update it as:


 fldMP3RedeemDate =CAST( '08/27/2013 12:00:40 PM'  as Date)

Also check paymentDate column to see if ALL of the data are  correct dates. If one if not a correct date(for example null) your query will fail.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

APD TorontoSoftware DeveloperAuthor Commented:
All dates are VARCHARs

Now I have the following, but still errors out:

UPDATE Invoices SET fldMP3Redeemed = 1 , fldMP3Lang = 1 , fldMP3RedeemDate = '08/27/2013 12:35:06 PM' WHERE CONVERT(DATETIME, paymentDate, 101) < CONVERT(DATETIME, '08/19/2013', 101);

Open in new window

0
unknown_routineCommented:
You say all dates are VARCHARs. How large is your table? Did you check all of the data in these VARCHAR fields? Even 1 wrong data will break your query.
What happens you use this query

UPDATE Invoices SET fldMP3Redeemed = 1 , fldMP3Lang = 1 , fldMP3RedeemDate 
= '08/27/2013 12:35:06 PM' WHERE CONVERT(DATETIME, paymentDate, 101) > 
CONVERT(DATETIME, '08/19/2013', 101); 

Open in new window

0
Scott PletcherSenior DBACommented:
Easiest is to use a CASE statement, to force SQL to evaluate the conditions in the order you need:



UPDATE dbo.Invoices
SET
    fldMP3Redeemed = 1,
    fldMP3Lang = 1,
    fldMP3RedeemDate = '08/27/2013 12:00:40 PM'
WHERE
    1 = CASE
        WHEN ISDATE(paymentDate) = 0 THEN 0
        WHEN CAST(paymentDate AS date) < '20130819' THEN 1
        ELSE 0
        END
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
APD TorontoSoftware DeveloperAuthor Commented:
Thanks
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
Query Syntax

From novice to tech pro — start learning today.