Conversion failed when converting date and or time from character string while inserting datetime

Question

I was trying to create a table as follows   create table table1 date1 datetime date2 datetime     First I tried inserting values as below   insert into table1 values  21-02-2012 6 10 00 PM   01-01-2001 12 00 00 AM      It has given error saying      Cannot convert varchar to datetime   Then I tried below format as one of the post suggested by our stackoverflow   insert into table1 values convert datetime  21-02-2012 6 10 00 PM  5                             convert datetime  01-01-2001 12 00 00 AM  5      But am still getting the error saying      Conversion failed when converting date and or time from character string   Any suggestions

User · Answer

I had this issue when trying to concatenate getdate() into a string that I was inserting into an nvarchar field.

I did some casting to get around it:

 INSERT INTO [SYSTEM_TABLE] ([SYSTEM_PROP_TAG],[SYSTEM_PROP_VAL]) VALUES 
   (
    'EMAIL_HEADER',
    '<h2>111 Any St.<br />Anywhere, ST 11111</h2><br />' + 
        CAST(CAST(getdate() AS datetime2) AS nvarchar) + 
    '<br /><br /><br />'
   )

That's a sanitized example. The key portion of that is:

...' + CAST(CAST(getdate() AS datetime2) AS nvarchar) + '...

Casted the date as datetime2, then as nvarchar to concatenate it.

User · Answer

the best way is this code   select   from  table 1  where date between convert date      dateTimePicker1 Text      105  and convert date      dateTimePicker2 Text      105

User · Answer

For me this worked   INSERT INTO  MyTable               ValidFrom               ValidTo          VALUES              2020-01-27 14 54 11 000               2023-01-27 14 52 50 000

User · Answer

There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT  Most of those formats are dependent on what settings you have - therefore  these settings might work some times - and sometimes not   The way to solve this is to use the  slightly adapted  ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings   The ISO-8601 format is supported by SQL Server comes in two flavors    YYYYMMDD for just dates  no time portion   note here  no dashes   that s very important  YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations    or    YYYY-MM-DDTHH MM SS for dates and times - note here  this format has dashes  but they can be omitted   and a fixed T as delimiter between the date and time portion of your DATETIME    This is valid for SQL Server 2000 and newer   So in your concrete case - use these strings   insert into table1 values  2012-02-21T18 10 00    2012-01-01T00 00 00      and you should be fine  note  you need to use the international 24-hour format rather than 12-hour AM PM format for this    Alternatively  if you re on SQL Server 2008 or newer  you could also use the DATETIME2 datatype  instead of plain DATETIME  and your current INSERT would just work without any problems   -  DATETIME2 is a lot better and a lot less picky on conversions - and it s the recommend date time data types for SQL Server 2008 or newer anyway   SELECT    CAST  02-21-2012 6 10 00 PM  AS DATETIME2       -- works just fine    CAST  01-01-2012 12 00 00 AM  AS DATETIME2    -- works just fine     Don t ask me why this whole topic is so tricky and somewhat confusing - that s just the way it is  But with the YYYYMMDD format  you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server

User · Answer

Whenever possible one should avoid culture specific date time literals  There are some secure formats to provide a date time as literal  All examples for 2016-09-15 17 30 00 ODBC  my favourite  as it is handled as the real type immediately    ts 2016-09-15 17 30 00   --Time Stamp  d 2016-09-15             --Date only  t 17 30 00               --Time only  ISO8601  the best for everywhere    2016-09-15T17 30 00  --be aware of the T in the middle   Unseperated  tiny risk to get misinterpreted as number    20160915               --only for pure date  Good to keep in mind  Invalid dates tend to show up with strange errors  There is no 31st of June or 30th of February     One more reason for strange conversion errors  Order of execution  SQL-Server is well know to do things in an order of execution one might not have expected  Your written statement looks like the conversion is done before some type related action takes place  but the engine decides - why ever - to do the conversion in a later step  Here is a great article explaining this with examples  Rusano com   quot t-sql-functions-do-no-imply-a-certain-order-of-execution quot  and here is the related question

User · Answer

The datetime format actually that runs on sql server is  yyyy-mm-dd hh MM ss

User · Answer

This is how to easily convert from an ISO string to a SQL-Server datetime   INSERT INTO time data  ImportateDateTime  VALUES  CAST CONVERT datetimeoffset  2019-09-13 22 06 26 527000   AS datetime     Source https   www sqlservercurry com 2010 04 convert-character-string-iso-date-to html

User · Answer

You can try this code  select  Convert Date   2018-04-01

User · Answer

convert datetime2   SUBSTRING  ISNULL S2 FechaReal e ETA  7 4   -   SUBSTRING  ISNULL S2 FechaReal e ETA  4 2   -   SUBSTRING  ISNULL S2 FechaReal e ETA  1 2      12 00 00 127      as fecha

User · Answer

Simple answer - 5 is Italian  yy  and 105 is Italian  yyyy   Therefore   SELECT convert datetime  21-02-12 6 10 00 PM  5    will work correctly  but  SELECT convert datetime  21-02-12 6 10 00 PM  105    will give error   Likewise    SELECT convert datetime  21-02-2012 6 10 00 PM  5    will give error  where as  SELECT convert datetime  21-02-2012 6 10 00 PM  105    will work

User · Answer

The conversion in SQL server fails sometimes not because of the Date or Time formats used  It is Merely because you are trying to store wrong data that is not acceptable to the system   Example   Create Table MyTable  MyDate    Insert Into MyTable MyDate  Values   2015-02-29     The SQL server will throw the following error   Conversion failed when converting date and or time from character string   The reason for this error is simply there is no such date  Feb-29  in Year  2015

User · Answer

Just update the date format  as like bellow  yyyy-MM-dd hh MM ss   It solves the problem for me and it works fine

User · Answer

I m Tried this and it s working with me     SELECT CONVERT date  yourDate  104

User · Answer

Please Try this   SQL Server expects dates in MM DD YYYY format If English is set as your default language Here am saving datepicker value to sql2008 database My field type is datetime in database dpdob is my datepicker name              Dim test   dpdob Text Replace  -                   Dim parts As String     test Split New Char       c              Dim firstPart As String   parts 0             Dim thirdPart As String   parts 2             Dim secondPart As String   parts 1             Dim test1   secondPart         firstPart         thirdPart            Dim dob   test1   Now use dob in your insert query

User · Answer

set Culture to english from web config file     lt globalization uiCulture  en-US  culture  en-US    gt    for example if you set the culture to arabic the thime will be    22  09  2017  02 16 57    and you get the error Conversion failed when converting date and or time from character string while inserting datetime

[sql] Conversion failed when converting date and/or time from character string while inserting datetime

Examples related to sql

Examples related to sql-server