CODEWITHSUNDEEP
c#sql.nettype-conversion
ShadowG
ShadowG

Reputation: 683

Mysterious data type conversion to another data type error

I have a web project working perfecly on my web server and on my local machine.

I sign up on another server provider because I need to migrate my application to a better place, and when I run my application I am receiving this error message:

the conversion of a varchar data type to a datetime data type resulted in an out-of-range value

But I don't receive this error locally nor on the other server, so I thought it would be some SQL configuration, sadly this error just occur on the code, when I run query on the database, it works perfectly. So I don't really know what to do at all!

I think its got to be the C# .net configuration but I have no clue where to start.

Since the application is working on everywhere else and for longtime, I've discarded the possibility of coding problem.

By the way, the error only occur when the code executes the query on the line: sqlDa.Fill(dt);

************************* UPDATED **********************

My web.config file which sets the culture of the application is the same as on the other servers as well. This server is located in another country so the culture is the problem.

Is there anywhere else I need to change the culture for the server? or/and how can I see the culture of my sql data base?

Upvotes: 1

Views: 304

Answers (6)

ShadowG
ShadowG

Reputation: 683

Well guys, for everyone that helped me thx a lot..

I solved my problem, and I doubt someone will have the same problem as mine, but anyway, here is what i did to fix it:

You'll Need to have full access to the server, else you would have to use the CONVERT() function on every query, and that would be too much work.

So what I did was to change the default langue of my user on the sql server, and that solved my problem 100%

Ps: Never store a DateTime value on another data type. With that in mind, you will not have the same problem as mine!

Upvotes: 2

zmbq
zmbq

Reputation: 39013

As others have pointed out, it's a locale/culture issue. You are probably passing a date in the where clause: SELECT ... WHERE RecordDate>=<your date>

When passing dates to the database, always format them with the yyyy-MM-dd format. The database will understand it regardless of its settings.

Upvotes: 0

Guffa
Guffa

Reputation: 700152

The error message comes from the database, so it's likely that you are sending a string value to the database that it would need to convert to a datetime value. The conversion fails because the string contains a different date format from what the database expects.

You should use parameterised queries so that you send the value as a datetime value, and let the database driver worry about how it is sent to the database.

Upvotes: 1

Sam Holder
Sam Holder

Reputation: 32936

Could it be converting the string into a date is being affected by the server being in a different locale? You shouldn't be storing dates as strings, you are just asking for trouble.

How are you doing the conversion in the code? What locale are your dates stored in in the db? Are you specifying the local to use when doing the conversion to a date?

Upvotes: 1

daryal
daryal

Reputation: 14919

I suspect that this is related with the locale of the server; or the locale of the SQL machine; or basicly it is because they do not match.

Upvotes: 1

Henk Holterman
Henk Holterman

Reputation: 273169

the conversion of a varchar data type to a datetime data type

Is culture dependent. All it takes is for the other server to run under a different locale.

You should review your (C# / SQL) code to see where this happens and take control of the conversion.
Best idea would be to eliminate it altogether. A string is not a Date.

Upvotes: 2

Related Questions