CODEWITHSUNDEEP
sql-servervb.netdatetimetype-conversion
Hellcat8
Hellcat8

Reputation: 558

System.Data.SqlClient.SqlException: conversion failed when converting datetime from character string depending on OS language

I'm struggling with DateTime formats and conversion for many days now and after unsuccessful searches, I decided to ask on SO.

I import DateTime values from a .csv file and convert this string value to DateTime using this line of C# code :

DateTime.TryParse("24-08-17", new CultureInfo("fr-FR", true), DateTimeStyles.AssumeLocal, out dt);

I don't use ParseExact or TryParseExact because the input string format for DateTime values can change.

The problem is :

On my colleague computer which is running the French version of Windows 10 -> everything is fine.

On my computer which is running the en-US version of Windows 10 -> I got a

System.Data.SqlClient.SqlException : conversion failed when converting datetime from character string

when this DateTime value is used in a SQL parameter executed by this line of VB.Net code :

cmd.ExecuteScalar

The output SQL statement is the following :

select count(*) from xxx where yyy = 212 AND (www <> 2 AND www <> 5) AND zzz = 316882 AND isGgg = 0  and ((date_p >='24-Aug-17 00:00:00' and date_p <='22-Nov-17 00:00:00') or (Date_s >='24-Aug-17 00:00:00' and Date_s <='22-Nov-17 00:00:00'))

I could just install the FR language pack for Windows on my computer but I don't consider this as a real solution.

I think the problem could be solved by modifying the DateTime.TryParse line of code, but after trying a lot of things found on the web, I still can't get my problem solved.

Any ideas ?

Thanks.

Upvotes: 1

Views: 2235

Answers (1)

IvanH
IvanH

Reputation: 5139

Your error is message from SQL server and has nothing to do with parsing. It is necessary to format date properly in a query. I would recomend add ;Current Language=Englishto the connection string and use yyyy-mm-dd format for date.

Be carful with TryParse because it sometimes changes month and date TryParseExact is much more reliable.

Upvotes: 1

Related Questions