Conversion failed when converting date and/or time from character string.

Question

0.00/5 (No votes)

See more:

Hi,

when I am inserting values into a database table, I am getting exception for datetime varaibles:

Here is my query>

INSERT INTO [dbo].[DOMAIN] ([CREATED_DATE], [LAST_UPDT_DATE]) VALUES (cast('16/02/2016 12:58:45 a.m.' as DATETIME),cast('16/02/2016 12:58:45 a.m.'as DATETIME));

Error Msg:
Conversion failed when converting date and/or time from character string.

note:I set my system region settings to non US, so date foramt will be like:16/02/2016 12:58:45 a.m.

What I have tried:

INSERT INTO [dbo].[DOMAIN] ([CREATED_DATE], [LAST_UPDT_DATE]) VALUES (cast('16/02/2016 12:58:45 a.m.' as DATETIME),cast('16/02/2016 12:58:45 a.m.'as DATETIME));

Posted 15-Feb-16 23:36pm

Member 10714689

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-02-15T23:45:00

Probably, it's the "a.m." part of your date / time value.
I'm pretty sure you aren't entering that query manually, but from an application - so change the application to pass the DateTime value directly. If you don't, then when you change "a.m." to "am" or "AM" then you are likely to meet your next problem:

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

Because SQL assumes US format dates rather than European, and treats "16" as the month number.
Always pass validated data in storable form to SQL - numbers as integer or float values, dates as DateTime values - as parameters instead of concatenating strings: it's a lot safer, and it reduces the chance of errors in conversion.