SQL Query works, but it doesn't?

Question

0.00/5 (No votes)

See more:

I've got a complex query going on here that figures out what the average response time between two events is, and takes into account working hours and weekends. It works fine when I run it in Microsoft SQL Server Management Studio, but I get the error "Incorrect Syntax near 'DATEPART'" when I run it from my asp.net c# website. Anybody have any ideas?

SQL

select
   AVG(weekdiff) as 'Response Time'
from
   (select
      TicketID,
      modifiedCreateDate,
      AssignedDateTime,
      DATEDIFF(hour,
      modifiedcreatedate,
      AssignedDateTime) as actualhours,
      DATEDIFF(day,
      modifiedcreatedate,
      AssignedDateTime)actualdays,
      DATEDIFF(hour,
      modifiedcreatedate,
      AssignedDateTime)-( DATEDIFF(day,
      modifiedcreatedate,
      AssignedDateTime)*13)- ((datediff(wk,
      modifiedCreateDate,
      AssignedDateTime) * 2) - case
         when datepart(dw,
         modifiedCreateDate) = 1 then 1
         else 0
      end + case
         when datepart(dw,
         AssignedDateTime) = 1 then 1
         else 0
      end)*11 as Weekdiff
   FROM
      (SELECT
         [TicketID],
         (CASE
            WHEN DATEPART(hour,
            OriginDateTime) > '17' then dateadd(hour,
            6,
            CAST (CAST(DATEPART(year,
            (DATEADD (day,
            1,
            OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(month,
            (DATEADD (day,
            1,
            OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(day,
            (DATEADD (day,
            1,
            OriginDateTime))) AS varchar(4)) AS datetime))
            WHEN DATEPART(hour,
            OriginDateTime) < '6' then dateadd(hour,
            6,
            CAST (CAST(DATEPART(year,
            OriginDateTime) AS varchar(4)) + '-' + CAST(DATEPART(month,
            OriginDateTime) AS varchar(4)) + '-' + CAST(DATEPART(day,
            OriginDateTime) AS varchar(4)) AS datetime))
            WHEN DATEPART(dw,
            OriginDateTime) = '7' then dateadd(hour,
            6,
            CAST (CAST(DATEPART(year,
            (DATEADD (day,
            2,
            OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(month,
            (DATEADD (day,
            2,
            OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(day,
            (DATEADD (day,
            2,
            OriginDateTime))) AS varchar(4)) AS datetime))
            WHEN DATEPART(dw,
            OriginDateTime) = '1' then dateadd(hour,
            6,
            CAST (CAST(DATEPART(year,
            (DATEADD (day,
            1,
            OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(month,
            (DATEADD (day,
            1,
            OriginDateTime))) AS varchar(4)) + '-' + CAST(DATEPART(day,
            (DATEADD (day,
            1,
            OriginDateTime))) AS varchar(4)) AS datetime))
            ELSE OriginDateTime
         end) AS modifiedCreateDate,
         [AssignedDateTime]
      FROM
         [LFM_Archive].[dbo].[IT_Track]
      WHERE
         DATEDIFF(HOUR,[OriginDateTime],[AssignedDateTime]) IS NOT NULL)x
   where
      DATEPART(WEEK, [modifiedCreateDate]) = DATEPART(WEEK, getdate())
   )y

EDIT:

So, we tried doing a view instead, but running the query SELECT AVG(workdaydiff) from LFM_Archive.dbo.Statistics still throws the incorrect syntax near DATEPART error from the webpage. Here's the view:

 SELECT ticketid,
       modifiedcreatedate,
       assigneddatetime,
       Datediff(hour, modifiedcreatedate, assigneddatetime)
       AS actualhours,
       Datediff(day, modifiedcreatedate, assigneddatetime)
       AS actualdays,
       ( Datediff(hour, modifiedcreatedate, assigneddatetime) -
         Datediff(day, modifiedcreatedate, assigneddatetime) * 13 ) - (
       Datediff(wk, modifiedcreatedate, assigneddatetime) * 2 - CASE
                                                                  WHEN
       Datepart(dw, modifiedcreatedate) = 1 THEN 1
                                                                  ELSE 0
                                                                END + CASE
       WHEN Datepart(dw, assigneddatetime) = 1 THEN 1
       ELSE 0
                                                                      END ) * 11
       AS
       WorkHourDiff,
       Datediff(day, modifiedcreatedate, assigneddatetime) - (
       Datediff(wk, modifiedcreatedate, assigneddatetime) * 2 - CASE
          WHEN Datepart(dw,
       modifiedcreatedate) = 1 THEN 1
                                                                  ELSE 0
                                                                END + CASE
                                                                        WHEN
       Datepart(
                                                                        dw,
       assigneddatetime)
       = 1 THEN 1
                                                                        ELSE 0
                                                                      END )
       AS
       workdaydiff
FROM   (SELECT ticketid,
               ( CASE
                   WHEN Datepart(hour, origindatetime) > '17' THEN
                   Dateadd(hour, 6,
                   Cast(
                   Cast(Datepart(year, (Dateadd(day, 1, origindatetime))) AS
                   VARCHAR(4)
                   )
                                       + '-'
                                       + Cast(Datepart(month, (Dateadd(day, 1,
                   origindatetime))
                   )
                   AS VARCHAR
                   (4))
                                       + '-'
                                       + Cast(Datepart(day, (Dateadd(day, 1,
                   origindatetime)))
                   AS
                   VARCHAR(4)) AS
                                  DATETIME))
                   WHEN Datepart(hour, origindatetime) < '6' THEN
                   Dateadd(hour, 6,
                     Cast(
                   Cast(Datepart(year, origindatetime) AS VARCHAR(4))
                                  + '-'
                                  + Cast(Datepart(month, origindatetime) AS
                   VARCHAR(4))
                                  + '-'
                                  + Cast(Datepart(day, origindatetime) AS
                   VARCHAR(4))
                   AS
                   DATETIME))
                   WHEN Datepart(dw, origindatetime) = '7' THEN
                   Dateadd(hour, 6, Cast(
                   Cast(Datepart(year, (Dateadd(day, 2, origindatetime))) AS
                   VARCHAR(4))
                      + '-'
                      + Cast(Datepart(month, (Dateadd(day, 2, origindatetime)))
                   AS VARCHAR(4))
                      + '-'
                      + Cast(Datepart(day, (Dateadd(day, 2, origindatetime))) AS
                   VARCHAR(4)) AS
                   DATETIME))
                   WHEN Datepart(dw, origindatetime) = '1' THEN
                   Dateadd(hour, 6, Cast(
                   Cast(Datepart(year, (Dateadd(day, 1, origindatetime))) AS
                   VARCHAR(4))
                      + '-'
                      + Cast(Datepart(month, (Dateadd(day, 1, origindatetime)))
                   AS VARCHAR(4))
                      + '-'
                      + Cast(Datepart(day, (Dateadd(day, 1, origindatetime))) AS
                   VARCHAR(4)) AS
                   DATETIME))
                   ELSE origindatetime
                 END ) AS modifiedCreateDate,
               assigneddatetime
        FROM   dbo.it_track
        WHERE  ( Datediff(hour, origindatetime, assigneddatetime) IS NOT NULL ))
       AS x

Posted 2-Apr-14 13:41pm

dfarr1

Updated 3-Apr-14 6:43am

v2

Add a Solution

Comments

ZurdoDev 2-Apr-14 21:15pm

How are you calling it in C#? Is it an Stored Procedure? How do you call it in SMS?

dfarr1 3-Apr-14 12:17pm

It's a sql query called as a report with a variable to modify the range of dates it covers, like where datepart(week, [modifiedcreatedate]) = datepart(week,getdate()). In SMS I'm running it as a query without the variable (as is above). I've tried running with and without the time modifier to no avail.

Guruprasad.K.Basavaraju 3-Apr-14 13:00pm

What line of this code is throwing the error ? Could you please post the complete error ?

dfarr1 3-Apr-14 14:58pm

It compiles fine - when you go to production and run the query on an onclick event, I am catching the exception message in a label. The message reads: "Incorrect syntax near 'DATEPART'"

3 solutions

Solution 3

Could you try the below query please. The only reason I could think of is the variable inside the DATEPART may not be a valid DATE.

SQL

select
   AVG(weekdiff) as 'Response Time'
from
   (select
      TicketID,
      modifiedCreateDate,
      AssignedDateTime,
      DATEDIFF(hour,
      modifiedcreatedate,
      AssignedDateTime) as actualhours,
      DATEDIFF(day,
      modifiedcreatedate,
      AssignedDateTime)actualdays,
      DATEDIFF(hour,
      modifiedcreatedate,
      AssignedDateTime)-( DATEDIFF(day,
      modifiedcreatedate,
      AssignedDateTime)*13)- ((datediff(wk,
      modifiedCreateDate,
      AssignedDateTime) * 2) - case
         when datepart(dw,
         CAST(modifiedCreateDate AS DATETIME)) = 1 then 1
         else 0
      end + case
         when datepart(dw,
         CAST(AssignedDateTime AS DATETIME)) = 1 then 1
         else 0
      end)*11 as Weekdiff
   FROM
      (SELECT
         [TicketID],
         (CASE
            WHEN DATEPART(hour,
            CAST(OriginDateTime AS DATETIME)) > '17' then dateadd(hour,
            6,
            CAST (CAST(DATEPART(year,
            (DATEADD (day,
            1,
            CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(month,
            (DATEADD (day,
            1,
            CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(day,
            (DATEADD (day,
            1,
            CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) AS datetime))
            WHEN DATEPART(hour,
            CAST(OriginDateTime AS DATETIME)) < '6' then dateadd(hour,
            6,
            CAST (CAST(DATEPART(year,
            CAST(OriginDateTime AS DATETIME)) AS varchar(4)) + '-' + CAST(DATEPART(month,
            CAST(OriginDateTime AS DATETIME)) AS varchar(4)) + '-' + CAST(DATEPART(day,
            CAST(OriginDateTime AS DATETIME)) AS varchar(4)) AS datetime))
            WHEN DATEPART(dw,
            CAST(OriginDateTime AS DATETIME)) = '7' then dateadd(hour,
            6,
            CAST (CAST(DATEPART(year,
            (DATEADD (day,
            2,
            CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(month,
            (DATEADD (day,
            2,
            CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(day,
            (DATEADD (day,
            2,
            CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) AS datetime))
            WHEN DATEPART(dw,
            CAST(OriginDateTime AS DATETIME)) = '1' then dateadd(hour,
            6,
            CAST (CAST(DATEPART(year,
            (DATEADD (day,
            1,
            CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(month,
            (DATEADD (day,
            1,
            CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(day,
            (DATEADD (day,
            1,
            CAST(OriginDateTime AS DATETIME)))) AS varchar(4)) AS datetime))
            ELSE CAST(OriginDateTime AS DATETIME)
         end) AS modifiedCreateDate,
         [AssignedDateTime]
      FROM
         [LFM_Archive].[dbo].[IT_Track]
      WHERE
         DATEDIFF(HOUR,[CAST(OriginDateTime AS DATETIME)],[AssignedDateTime]) IS NOT NULL)x
   where
      DATEPART(WEEK, CAST(modifiedCreateDate AS DATETIME)) = DATEPART(WEEK, getdate())
   )y

Posted 3-Apr-14 9:58am

Guruprasad.K.Basavaraju

Comments

dfarr1 3-Apr-14 16:18pm

I tried using this as the query string, but the error remains to be "Incorrect syntax near 'DATEPART'" :(

Guruprasad.K.Basavaraju 3-Apr-14 18:14pm

could you give couple of records from [LFM_Archive].[dbo].[IT_Track] table ?

I tried different DatePart queries from my c# code and it works perfect.

SqlConnection sqlConnection1 = GetConnection();
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
string str1 = "4/3/2014";
string str2 = "select datepart(dw,CAST('"+ str1 +"' AS DATETIME)) as ss;";
cmd.CommandText = str2 ;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;

// sqlConnection1.Open();

reader = cmd.ExecuteReader();
string ssss = "";
while (reader.Read())
{
ssss = reader[0].ToString();
}

Guruprasad.K.Basavaraju 3-Apr-14 18:27pm

I guess the problem is not in your query but the C# code where you are adding the variable you are talking about. Can you paste a snippet of that part ?

dfarr1 3-Apr-14 18:45pm

I changed to the view I posted up earlier, cleaned up a few dateparts, but still am getting the error. Here's the best representation of what it looks like:

string qpart1 = " DATEPART(WEEK, [modifiedCreateDate]) = DATEPART(WEEK, getdate()) ";
string sql6 = "select AVG(WorkHourDiff) AS 'Response Time' from LFM_Archive.dbo.IT_Statistics where" + qpart1;
SqlCommand cmd6 = new SqlCommand(sql6, conn);
conn.Open();
SqlDataReader reader6 = cmd6.ExecuteReader();
while (reader6.Read())
{
int value = 0;
value = (int)reader6["Response Time"];
}
conn.Close();

I've also tried ramming the query string straight into the sqlcommand but that didn't make a difference.

Guruprasad.K.Basavaraju 3-Apr-14 19:11pm

hmmm.. don't know what to say.. The exact same code of yours works well for me..

I have screenshot but don't know how to send.

Guruprasad.K.Basavaraju 3-Apr-14 18:24pm

I even tried the exact query of yours with my own table. See below, it works both in SQL and C#

Select
AVG(Weekdiff) as 'Response Time'
from
(select
TicketID,
MEPE_CREATE_DTM,
MEPE_TERM_DT,
DATEDIFF(hour,
MEPE_CREATE_DTM,
MEPE_TERM_DT) as actualhours,
DATEDIFF(day,
MEPE_CREATE_DTM,
MEPE_TERM_DT)actualdays,
DATEDIFF(hour,
MEPE_CREATE_DTM,
MEPE_TERM_DT)-( DATEDIFF(day,
MEPE_CREATE_DTM,
MEPE_TERM_DT)*13)- ((datediff(wk,
MEPE_CREATE_DTM,
MEPE_TERM_DT) * 2) - case
when datepart(dw,
CAST(MEPE_CREATE_DTM AS DATETIME)) = 1 then 1
else 0
end + case
when datepart(dw,
CAST(MEPE_TERM_DT AS DATETIME)) = 1 then 1
else 0
end)*11 as Weekdiff
FROM
(SELECT
[TicketID],
(CASE
WHEN DATEPART(hour,
CAST(MEPE_EFF_DT AS DATETIME)) > '17' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
(DATEADD (day,
1,
CAST(MEPE_EFF_DT AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(month,
(DATEADD (day,
1,
CAST(MEPE_EFF_DT AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(day,
(DATEADD (day,
1,
CAST(MEPE_EFF_DT AS DATETIME)))) AS varchar(4)) AS datetime))
WHEN DATEPART(hour,
CAST(MEPE_EFF_DT AS DATETIME)) < '6' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
CAST(MEPE_EFF_DT AS DATETIME)) AS varchar(4)) + '-' + CAST(DATEPART(month,
CAST(MEPE_EFF_DT AS DATETIME)) AS varchar(4)) + '-' + CAST(DATEPART(day,
CAST(MEPE_EFF_DT AS DATETIME)) AS varchar(4)) AS datetime))
WHEN DATEPART(dw,
CAST(MEPE_EFF_DT AS DATETIME)) = '7' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
(DATEADD (day,
2,
CAST(MEPE_EFF_DT AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(month,
(DATEADD (day,
2,
CAST(MEPE_EFF_DT AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(day,
(DATEADD (day,
2,
CAST(MEPE_EFF_DT AS DATETIME)))) AS varchar(4)) AS datetime))
WHEN DATEPART(dw,
CAST(MEPE_EFF_DT AS DATETIME)) = '1' then dateadd(hour,
6,
CAST (CAST(DATEPART(year,
(DATEADD (day,
1,
CAST(MEPE_EFF_DT AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(month,
(DATEADD (day,
1,
CAST(MEPE_EFF_DT AS DATETIME)))) AS varchar(4)) + '-' + CAST(DATEPART(day,
(DATEADD (day,
1,
CAST(MEPE_EFF_DT AS DATETIME)))) AS varchar(4)) AS datetime))
ELSE CAST(MEPE_EFF_DT AS DATETIME)
end) AS MEPE_CREATE_DTM,
[MEPE_TERM_DT]
FROM
SOME_TABLE_OF_MY_OWN
WHERE
DATEDIFF(HOUR,CAST(MEPE_EFF_DT AS DATETIME),[MEPE_TERM_DT]) IS NOT NULL)x
where
DATEPART(WEEK, CAST(MEPE_CREATE_DTM AS DATETIME)) = DATEPART(WEEK, getdate())
)y

Solution 2

Quote:
CAST (CAST(DATEPART(year,
(DATEADD (day,
1,
OriginDateTime))) AS varchar(4))

What about a difference in the date time format between the two situations? Your current query totally depends on a specific date time format, it is not at all compatible for different regional settings.
I think that it is possible to set the standard language for a connection (not sure how it affects those settings). But a better solution would be to make the query independent from the date time format.

Posted 2-Apr-14 20:35pm

Bernhard Hiller

Comments

dfarr1 3-Apr-14 12:22pm

That might work, but we're not worried about the d/t format because when it's inserted or updated into the DB it is always the same format as defined by in the C# backend.

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)

lakshmankamathk · Accepted Answer · 2014-04-02T17:30:00

Solution 1

I would suggest you to move this query to Stored Procedure. Use this Stored Procedure in your code instead of using the query. Also try to use sql profiler while debugging your code to trace the query execution

Posted 2-Apr-14 17:30pm

lakshmankamathk

Comments

dfarr1 3-Apr-14 12:21pm

Because the last portion of the query is a variable (to get the output by day, week, month, last 3 months, year, last 5 years) so a stored procedure may not be appropriate. I am thinking that a view might though, and then just do the variable from the C# and leave the bulk of the query there.

dfarr1 3-Apr-14 12:43pm

check the question for the attempt of the view