Sql query error Msg 102, Level 15, State 1, Line 7

Question

0.00/5 (No votes)

See more:

Hi All,

I am getting below error while executing below query can any one help

C#

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '19800'.

querys
--------

SQL

EXEC sp_send_dbmail @profile_name='SQL Profiler', 
@recipients='your email id', 
@subject='Test message', 
@body='SMS Count.' ,

@query = 'select CAST(dateadd(s,Create_date+'19800','19700101') as Date) as 'Create_date', count(1) as count from TechM_Indus_LogDeliverSMS where char_TicketNumber__c like 'INCN%' and dateadd(s,Create_date+'19800','19700101')
>='11/01/2015 12:00:00 AM' and dateadd(s,Create_date+'19800','19700101')  <= '11/30/2015 23:59:00 PM'
group by CAST(dateadd(S,Create_date+'19800','19700101') as Date) order by Create_date asc'

Posted 26-Nov-15 0:27am

Member 12168418

Add a Solution

Comments

Bhagyeshkd 26-Nov-15 7:00am

What is the datatype for Create_date field? if yes then first convert it into string

Member 12168418 26-Nov-15 7:43am

Hi Bhagyesh,

datatype Int for Create_date

query is getting output as below
Create_date count
2015-11-01 27513
2015-11-02 29546
2015-11-03 31742
2015-11-04 28510
2015-11-05 34850
2015-11-06 33436
2015-11-07 31145
2015-11-08 27907

But when iam tring to exec as below it is getting error
@query = 'select CAST(dateadd(s,Create_date+'19800','19700101') as Date) as 'Create_date', count(1) as count from TechM_Indus_LogDeliverSMS where char_TicketNumber__c like 'INCN%' and dateadd(s,Create_date+'19800','19700101')
>='11/01/2015 12:00:00 AM' and dateadd(s,Create_date+'19800','19700101') <= '11/30/2015 23:59:00 PM'
group by CAST(dateadd(S,Create_date+'19800','19700101') as Date) order by Create_date asc'

Bhagyeshkd 26-Nov-15 8:48am

yes because your create_date field is integer and you are concatenating this with string, so you need to convert it to string
Try CAST(S,CONVERT(varchar,Create_date)+'19800','19700101') like this

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Answer 1 · 2015-11-26T03:56:00

Solution 2

The problem is you are doing dynamic sql, your sql is one big string. So, apostrophes need to be doubled when you want them.

Similar to:

SQL

query = 'SELECT * FROM table1 WHERE field1 IN(''' + 19800 + ''')...

You'll have to get your apostrophes all lined up.

Posted 26-Nov-15 3:56am

ZurdoDev

Comments

Member 12168418 27-Nov-15 2:10am

still it is not working i am not sql query backgroung please help me

ZurdoDev 27-Nov-15 9:12am

What does not working mean? You should have fixed the first error and now have a new error which tells you what is wrong.