DateTime conversion error when querying SQL

Question

I am having problems with a sql query from C# to an Sql Server, not sure what is causing it....

The code looks like this:

SqlCommand cmd = new SqlCommand(
    "SELECT tscoc_Name, 
            tscoc_Start, 
            tscoc_End 
            FROM 
            tbl_SchedulerOnCall 
            WHERE (
                tscoc_Start > @fd 
                AND 
                tscoc_End < @ld) 
                AND 
                tscoc_Start = @state", 
    con);
cmd.Parameters.AddWithValue("fd", SqlDbType.DateTime).Value = 
    startDate.ToString("yyyy-MM-dd");
cmd.Parameters.AddWithValue("ld", SqlDbType.DateTime).Value = 
    startDate.AddDays(14).ToString("yyyy-MM-dd");

The SQL query is this:

exec sp_executesql 
N'SELECT tscoc_Name, 
         tscoc_Start, 
         tscoc_End 
FROM 
tbl_SchedulerOnCall 
WHERE (
    tscoc_Start > @fd 
    AND 
    tscoc_End < @ld) 
    AND 
    tscoc_Start = @state', 
N'@fd datetime, @ld datetime, @state nvarchar(2)', 
@fd = 'Oct  8 2012 12:00:00:000AM', 
@ld = 'Oct 22 2012 12:00:00:000AM', 
@state = N'SA'

And the error is:

Syntax error converting datetime from character string.

Anyone have any idea what is happening?

what is the current format of your date in string?

John Woo
– John Woo

2012-10-10 07:06:16 +00:00
Commented Oct 10, 2012 at 7:06 — John Woo
– John Woo, Commented Oct 10, 2012 at 7:06

marc_s · Accepted Answer · 2012-10-10 07:07:47Z

4

You're converting your DateTime values (assuming startDate is indeed of type DateTime in your .NET code) to string when assigning to the SqlParameter - what for? That's totally useless and pointless... and it causes potential issues with date/time and language and regional settings - as you're seeing...

Just use:

cmd.Parameters.AddWithValue("fd", SqlDbType.DateTime).Value = startDate;
cmd.Parameters.AddWithValue("ld", SqlDbType.DateTime).Value = startDate.AddDays(14);

and then you should be fine.

answered Oct 10, 2012 at 7:07

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GrantC Over a year ago

Yes, you are right. That was test code. The sql statement I posted, was when my code was identical to yours.

mattcawley · Accepted Answer · 2012-10-10 07:06:42Z

1

You shouldn't need to convert the datetime to a string in order to add it as a parameter value. Just add the raw DateTime object itself, and the .NET SQL code will do the rest.

answered Oct 10, 2012 at 7:06

mattcawley

1914 bronze badges

1 Comment

GrantC Over a year ago

I agree, the ToString was there for testing, I got the error on lots of combinations I tried.

John Woo · Accepted Answer · 2012-10-10 07:09:54Z

0

use DateTime.ParseExact. This is useful when you have non standard format for your date in string.

  string dateString = "Oct  8 2012 12:00:00:000AM";
  string format = "MMM dd yyyy hh:mm:ssFFFtt";
  CultureInfo provider = CultureInfo.InvariantCulture;
  DateTime result = DateTime.ParseExact(dateString, format, provider);

  cmd.Parameters.AddWithValue("fd", SqlDbType.DateTime).Value = result;

answered Oct 10, 2012 at 7:09

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Damien_The_Unbeliever · Accepted Answer · 2012-10-10 07:20:26Z

0

Why are you using AddWithValue? The second parameter to that is meant to be the value you want to pass, not the data type.

Just do:

SqlCommand cmd = new SqlCommand("SELECT tscoc_Name, tscoc_Start, tscoc_End FROM tbl_SchedulerOnCall WHERE (tscoc_Start > @fd AND tscoc_End < @ld) AND tscoc_Start = @state", con);
cmd.Parameters.AddWithValue("fd", startDate);
cmd.Parameters.AddWithValue("ld", startDate.AddDays(14));

Converting dates to strings is usually the first step (as here) to creating a bug.

answered Oct 10, 2012 at 7:20

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Collectives™ on Stack Overflow

DateTime conversion error when querying SQL

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related