How Can I Safely Convert Dates to and from DATETIME in SQL Server?

Converting Date to DATETIME with Specific Formatting

The provided query, SELECT CONVERT(VARCHAR(10), GETDATE(), 105), converts the current date to a VARCHAR in the [DD-MM-YYYY] format. However, the query does not store the date in the DATETIMEdatatype in the same format.

DATETIME Datatype in SQL Server

Unlike VARCHAR, DATETIME in SQL Server is stored as two 4-byte integers and does not possess specific formatting. To display the date in a desired format, it must be converted to VARCHAR using the appropriate format identifier.

Converting to DATETIME

If you have a date in VARCHAR and wish to store it in a DATETIME field, it's crucial to use a format that SQL Server will always interpret correctly. Safe formats include:

• yyyyMMdd
• yyyy-MM-ddThh:mi:ss.mmm

For example:

INSERT MyTable (DateField) VALUES ('01/10/2010') -- dd/MM/yyyy not safe
INSERT MyTable (DateField) VALUES ('20101001') -- yyyyMMdd safe

Displaying DATETIME Values

When you select a DATETIME field using tools like SSMS, you see a formatted value for convenience. However, the actual internal representation of the DATETIME value remains as two 4-byte integers.

The above is the detailed content of How Can I Safely Convert Dates to and from DATETIME in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!