Incorrect Syntax near '7'

Question

Post reply

Incorrect Syntax near '7'

jhand6603

SSC-Addicted

Points: 448
More actions
November 16, 2010 at 10:07 am

#231344

Can someone tell me why I'm getting this message when there's no '7' in my expression.
Case when datediff (d, getdate (), [Request_UDF,End Date]) <= 01/31/2011 the 'Phase I'
When datediff (d, getdate (), [Request_UDF,End Date]) > 01/31/2011 and datediff (d, getdate (), [Request_UDF,End Date]) <= 03/31/2011 then 'Phase II'
End

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

dates have to be encased is single quotes, as they are not a number.

instead of this:

<= 01/31/2011

--change to this for example

<= '01/31/2011'

change it everywhere you had date strings.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mattrick Ten Centuries Points: 1176 More actions · Answer 2

First, you are presumably attempting to compare the integer returned by datediff to a date, but since that date is not enclosed in single quotes, you are actually comparing the integer returned by datediff to 1 divided by 31 divided by 2011.

What, exactly, are you attempting to check for?

Matt

jhand6603 SSC-Addicted Points: 448 More actions · Answer 3

Thanks for your response.

Just tried your fix. Got the following error msg:

"Conversion failed when converting the varchar value '01/31/2011' to data type int."

jhand6603 SSC-Addicted Points: 448 More actions · Answer 4

I want to pull all the records with an end dates on or before 01/31/11 and between 02/01/11 and 03/31/11.

Mattrick Ten Centuries Points: 1176 More actions · Answer 5

Try this:

CASE

WHEN [Request_UDF,End Date] <= '01/31/2011' THEN 'Phase I'

WHEN [Request_UDF,End Date] BETWEEN '02/01/2011' AND '03/31/2011' THEN 'Phase II'

END

Matt

jhand6603 SSC-Addicted Points: 448 More actions · Answer 6

jhand6603

SSC-Addicted

Points: 448

November 16, 2010 at 10:40 am

#1251474

Much simplier.........ok, thanks I will give a try

jhand6603 SSC-Addicted Points: 448 More actions · Answer 7

I got it again! Incorrect Syntax near '7'...........what does that mean? There's no 7 in the expression

Mattrick Ten Centuries Points: 1176 More actions · Answer 8

It means exactly that. There is an error near the character '7'. In order to help you further, I will need to see the whole query, not just a single CASE block.

Matt

WayneS SSC Guru Points: 95460 More actions · Answer 9

jhand6603 (11/16/2010)
I got it again! Incorrect Syntax near '7'...........what does that mean? There's no 7 in the expression

Well, it's probably somewhere else in your query or procedure. Can you post the entire bit of code that you're trying to run?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Hank Walters SSC Rookie Points: 39 More actions · Answer 10

Maybe make sure you have a name for the column like "SomeName =" (with no quotes) prior to the CASE and a comma after END if there is anything else in the query following the CASE.

You could also add "AS SomeName" (no quotes) after the END of the CASE along with a comma if anything follows.

Oh, and don't forget the comma after what ever comes before the CASE statement.

jhand6603 SSC-Addicted Points: 448 More actions · Answer 11

Actually the case is the whole expression. I'm not writing a script, its an expression to create a custom field in a report designer application.

Thanks for your help, I'm going to put in a ticket with application designer.