How to Solve Datetime Insertion Errors in SQL Server?

Datetime Insertion Queries in SQL Server: Resolving Syntax and Conversion Errors

To insert datetime values into a table in SQL Server, a common approach is using an SQL query. However, encountering syntax errors or conversion issues can often arise.

In the example provided, trying to insert a datetime using 18-06-12 10:34:09 AM results in an incorrect syntax error, while utilizing quotes ('18-06-12 10:34:09 AM') leads to a "Cannot convert varchar to datetime" error.

To address these issues, the following solutions can be employed:

Using YYYYMMDD Format for Unambiguous Date Determination
SQL Server prefers the YYYYMMDD format for parsing dates to avoid confusion. Therefore, the following query should work:

insert into table1(approvaldate)values('20120618 10:34:09 AM');

Utilizing CONVERT for Specific Style Conversion
If the desired datetime format is fixed, such as dd-mm-yy hh:mm:ss xm, the CONVERT function can be used to convert the string to the appropriate datetime format.

insert into table1 (approvaldate)
       values (convert(datetime,'18-06-12 10:34:09 PM',5));

Here, the style specified (5) corresponds to Italian dates.

The above is the detailed content of How to Solve Datetime Insertion Errors in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!