CODEWITHSUNDEEP
c#sql-server
GrantC
GrantC

Reputation: 81

DateTime conversion error when querying SQL

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?

Upvotes: 1

Views: 553

Answers (4)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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.

Upvotes: 0

John Woo
John Woo

Reputation: 263693

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;

Upvotes: 0

marc_s
marc_s

Reputation: 754250

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.

Upvotes: 4

mattcawley
mattcawley

Reputation: 191

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.

Upvotes: 1

Related Questions