0

Hi I created a table in which one column is of date type and also works as PK. I tried to insert value 2009-01-07 into this column and had this error. Isn't Date default format yyyy-mm-dd? I don't understand this.

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

This is my query:

INSERT INTO Table_Name
Values ('2009-01-07', other column values)
Andios
  • 1
  • 2
  • 3
    Something else is going on, because `'2009-01-07'` can be inserted as a date. Try creating an entirely new (table and/or) column as a date and see if you can insert into that column. It sounds to me as if you might have a trigger or check constraint calling a function or some language settings - or something similar - on the table which fails working on the value. Or that it's one of the `other column values` that fails of course. – Allan S. Hansen Sep 15 '16 at 06:11
  • 3
    Can you write another _INSERT_ query calling columns' name explicitly? For example: `INSERT INTO Table_Name (Date_PK, ...) VALUES ('2009-01-07', ...)`. Just want to make sure which column raises the exception. `2009-01-07` should be OK. – Mincong Huang Sep 15 '16 at 06:14
  • Possible duplicate of [Conversion failed when converting date and/or time from character string while inserting datetime](http://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i) – captainsac Sep 15 '16 at 06:27
  • Execute Select GETDATE(), Refer the output of it and change your date format accordingly – captainsac Sep 15 '16 at 06:28
  • 2
    Make sure you list the target column _names_: `insert into table_name (col1, col2, ...) values (...)` - probably the columns in the table are not in the same order as they appear in your `values` list –  Sep 15 '16 at 06:33

2 Answers2

0

Your value '2009-01-07' should be converted.

Date literals are always a deep source of troubles... Best was, to use either

  • Unseparated: 20090107
  • ODBC : {d'2009-01-07'}
  • ISO8601 : 2009-01-07T00:00:00

But your format is short ISO 8601 and should work...

Some possible reasons:

  • Other values in your VALUES list
  • a trigger
  • a constraint
  • As a_horse_with_no_name stated in comment: Without a column list after INSERT INTO Table(col1, col2, ...) There is a great risk to state your values in a different order thus pairing values with the wrong columns...
  • Invalid (but good looking) dates such as 2016-06-31

Or a - well known - issue with SQL-Server. Sometimes the order of execution is absolutely not the way one expected it. There are several issues with conversion errors...

What you can try

  • Use ODBC format (which is treated as DATETIME immediately)
  • DECLARE a variable with this value and put it in place
Shnugo
  • 66,100
  • 9
  • 53
  • 114
0

Thank you all for the prompt replies. I read and tried all of them and found out why.

  1. '2009-01-07' can be inserted into a Column with "Date" as data type if no CONSTRAINT has issue with that;
  2. my problem was caused by a CHECK constraint on that column. Originally I set CONSTRAINT as Column_Name = 'Wednesday' After I modified it to DATEName(dw,[Column_Name]) = 'Wednesday' the inserting began to work.

Thanks again.

Andios
  • 1
  • 2