How Do I Format SQL Server DATETIME Values for Display and Storage?

Formatting SQL Server DATETIME Datatypes

SQL Server stores DATETIME values as two 4-byte integers, which does not natively provide a specific formatting option. To retrieve or store dates in a preferred format, conversion is necessary.

Converting to VARCHAR for Display

To retrieve a DATETIME value in the [DD-MM-YYYY] format as a VARCHAR, use the following syntax:

SELECT CONVERT(VARCHAR(10), GETDATE(), 105)

Converting Back to DATETIME for Storage

When storing a VARCHAR value in a DATETIME field, ensure it adheres to a safe format that SQL Server can consistently interpret. Approved formats include:

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

Example:

INSERT INTO MyTable (DateField) VALUES ('20101001')

Understanding SSMS Display

In SQL Server Management Studio (SSMS), DATETIME values are formatted for readability. However, this representation is distinct from the internal storage format.

Additional Notes:

• The accuracy of date formatting in SSMS depends on your regional settings.
• Avoid using separators in custom formats, as they can lead to parse errors.

The above is the detailed content of How Do I Format SQL Server DATETIME Values for Display and Storage?. For more information, please follow other related articles on the PHP Chinese website!