How Can I Safely Store DD-MM-YYYY Dates in SQL Server's DATETIME Datatype?

SQL Server Date Format Conversion: DATETIME to DD-MM-YYYY

In SQL Server, when retrieving date values in the DD-MM-YYYY format, users often encounter the need to store these values in a DATETIME datatype while preserving the same format. This question explores a solution to this requirement.

The query provided, SELECT CONVERT(VARCHAR(10), GETDATE(), 105), successfully converts the current date to the desired DD-MM-YYYY format in a VARCHAR datatype. However, storing this value in a DATETIME datatype requires a different approach.

In SQL Server, DATETIME values are stored as 2 4-byte integers, making them inherently unformatted. To display or retrieve dates in specific formats, one must convert them to a VARCHAR datatype using the appropriate format identifier.

To achieve the desired format in a DATETIME datatype, consider the following:

• Ensure the date is formatted safely before inserting it. DD-MM-YYYY is not a universally safe format as it can be interpreted as MM-DD-YYYY in some settings.
• Use safe formats such as yyyyMMdd or yyyy-MM-ddThh:mi:ss.mmm.

• For example, to insert a date in the dd/MM/yyyy format safely:

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

• Use a safe format instead:

  INSERT MyTable (DateField) VALUES ('20101001') -- yyyyMMdd safe

Please note that when selecting DATETIME fields, SSMS displays formatted values for convenience. The actual internal representation is an 8-byte integer.

The above is the detailed content of How Can I Safely Store DD-MM-YYYY Dates in SQL Server's DATETIME Datatype?. For more information, please follow other related articles on the PHP Chinese website!