CODEWITHSUNDEEP
c#sqlsql-server-2014sqldataadapter
ReddShepherd
ReddShepherd

Reputation: 467

Conversion failed when converting date and/or time from character string in stored procedure

EDIT: I answered my question below, but can't accept it yet.

I'm using C# and SQL2014. I know this question is everywhere and the typical answer is to use the universal format, but that's not what I'm looking for. I have two scenarios. One works and one does not and that is what I am concerned with. Both are using the same stored procedure (albeit in different databases). If I edit the stored procedure to be a command and manually set the parameters to the same thing I am setting them to in C#, both work. Both scenarios are exactly the same except different databases, so here is what I am doing.

cmd = new sqlcommand......
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 60;            
cmd.Parameters.AddWithValue("@date", "3/16/2015");
cmd.Parameters.AddWithValue("@startTime", "12:00 AM");
cmd.Parameters.AddWithValue("@endTime", "1:00 AM");

SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();

da.Fill(dt); //Error

What is interesting is that it only fails when @date is set to "3/16/2015" I've tried about 6 other dates all around that and it all works fine. That being said, this date works fine on the other database. Does anyone have an idea of what this could be? In the stored procedure, these parameters get set to SMALLDATETIME. If need be, I can try to put some of the stored procedure in here.

Here is the part of the stored procedure that causes the issue to happen:

@date SMALLDATETIME,
@startTime SMALLDATETIME,
@endTime SMALLDATETIME


 --[DATE] is a smalldatetime column in the database
 WHERE
 [DATE] = @date --ERROR HAPPENS BECAUSE OF THIS

EDIT: Sorry for all of the confusion. After more debugging, it looks like the actual problem may be coming from this statement in the procedure:

 SELECT 
 DATEDIFF(mi, '00:00:00', [START]) AS [START], 
 CASE WHEN [END] = '00:00:00' THEN 1440 
 ELSE DATEDIFF(mi, '00:00:00', [END]) END AS [END]  
 .
 .
 .

Upvotes: 0

Views: 809

Answers (3)

ReddShepherd
ReddShepherd

Reputation: 467

My issue was using this statement in the procedure:

DATEDIFF(mi, '00:00:00', [END])

[END] column was an nvarchar that had 24:00:00 set as midnight instead of 00:00:00.

Thanks to everyone that helped me debug this.

Upvotes: 0

DarrenMB
DarrenMB

Reputation: 2380

The String "3/16/2015" is something that will break unless you have hard coded the regional settings in the thread to US English in some fashion.

Have you tried being more specific with the SQL Parameter? Sorry for the VB Syntax but I'd be guessing the C# equivalent...

Dim dt As Date = DateTime.ParseExact("3/16/2015", "M/d/yyyy", Globalization.CultureInfo.GetCultureInfo("us-en"))
cmd.Parameters.Add(New SqlClient.SqlParameter("@date", dt) With {.SqlDbType = SqlDbType.SmallDateTime})

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

This is probably based on the internal date format set up for the databases. I would recommend that you use ISO standard formats in all your code so pass in:

    cmd.Parameters.AddWithValue("@date", "2015-03-16");

Upvotes: 1

Related Questions