How to Fix 'Conversion failed when converting date and/or time from character string' Errors in SQL Server?

"Conversion failed when converting date and/or time from character string" Issue in SQL

While attempting to create a table with datetime columns and insert specific date and time values, an error message "Conversion failed when converting date and/or time from character string" was encountered. Here's how to address this issue:

Conversion Issues

The initial attempt to insert values in the format '21-02-2012 6:10:00 PM' resulted in an error because SQL Server requires specific date and time formats.

ISO-8601 Format

To resolve this issue, use the ISO-8601 date format, which is supported by SQL Server regardless of language or date format settings. The format consists of the following options:

• YYYYMMDD for dates without a time portion (no dashes)
• YYYY-MM-DDTHH:mm:ss for dates with time (dashes optional, fixed 'T' delimiter)

Corrected Insert Statement

Applying the ISO-8601 format to the insert statement yields the corrected code:

insert into table1 values('2012-02-21T18:10:00', '2012-01-01T00:00:00');

DATETIME2 Datatype

Alternatively, for SQL Server 2008 or newer, using the DATETIME2 datatype simplifies the conversion process and allows for different date formats without issue:

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  

By adhering to the ISO-8601 format or utilizing the DATETIME2 datatype, you can overcome the "Conversion failed" error and insert date and time values correctly in SQL Server.

The above is the detailed content of How to Fix 'Conversion failed when converting date and/or time from character string' Errors in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!