Solved

SQL case statement does not work

Posted on 2016-09-21
4
23 Views
Last Modified: 2016-09-21
I have something wrong in below Case Statement.

Msg 1035, Level 15, State 10, Procedure usp_rmcore_invoicesfrt, Line 232
Incorrect syntax near 'Cast', expected 'AS'.

CASE
                   WHEN @AgingBase = 'I' Then Datediff("d", recl.invdate, Cast(CONVERT(VARCHAR(10), Getdate(), 111)))    
                   WHEN @AgingBase = 'D' Then Datediff("d", recl.duedate, Cast(CONVERT(VARCHAR(10), Getdate(), 111)))                    
                   ELSE 0
                   END                           AS AgeDays,
0
Comment
Question by:thayduck
  • 3
4 Comments
 
LVL 35

Accepted Solution

by:
YZlat earned 500 total points
ID: 41809304
simple syntax error:

CAST(VALUE AS DataType)


try:
CASE
                   WHEN @AgingBase = 'I' Then Datediff("d", recl.invdate, Cast(CONVERT(VARCHAR(10), Getdate(), 111) AS VARCHAR) )    
                   WHEN @AgingBase = 'D' Then Datediff("d", recl.duedate, Cast(CONVERT(VARCHAR(10), Getdate(), 111) AS VARCHAR) )                   
                   ELSE 0
                   END                           AS AgeDays,

Open in new window


or

CASE
                   WHEN @AgingBase = 'I' Then Datediff("d", recl.invdate, Cast(CONVERT(VARCHAR(10), Getdate(), 111) AS DATETIME2)  )   
                   WHEN @AgingBase = 'D' Then Datediff("d", recl.duedate, Cast(CONVERT(VARCHAR(10), Getdate(), 111) AS DATETIME2) )                   
                   ELSE 0
                   END                           AS AgeDays,

Open in new window

0
 

Author Comment

by:thayduck
ID: 41809350
Both of your answers corrected my problem.

Thank you for the fast response.
0
 

Author Comment

by:thayduck
ID: 41809363
Thanks..
0
 

Author Closing Comment

by:thayduck
ID: 41809373
Thanks....
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now