Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL DATETIME ISO 8601

I have been given a specification that requires the ISO 8601 date format, does any one know the conversion codes or a way of getting these 2 examples:

ISO 8601 Extended Date 2000-01-14T13:42Z  ISO 8601 Basic Date 20090123T105321Z 
like image 753
MartGriff Avatar asked May 01 '09 20:05

MartGriff


People also ask

How do I format a date in ISO 8601?

ISO 8601 represents date and time by starting with the year, followed by the month, the day, the hour, the minutes, seconds and milliseconds. For example, 2020-07-10 15:00:00.000, represents the 10th of July 2020 at 3 p.m. (in local time as there is no time zone offset specified—more on that below).

What is the proper date format for SQL?

SQL Server comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS.

Is ISO 8601 always UTC?

The toISOString() method returns a string in ISO format (ISO 8601 Extended Format), which can be described as follows: YYYY-MM-DDTHH:mm:ss. sssZ . The timezone is always UTC as denoted by the suffix "Z".


2 Answers

When dealing with dates in SQL Server, the ISO-8601 format is probably the best way to go, since it just works regardless of your language and culture settings.

In order to INSERT data into a SQL Server table, you don't need any conversion codes or anything at all - just specify your dates as literal strings

INSERT INTO MyTable(DateColumn) VALUES('20090430 12:34:56.790') 

and you're done.

If you need to convert a date column to ISO-8601 format on SELECT, you can use conversion code 126 or 127 (with timezone information) to achieve the ISO format.

SELECT CONVERT(VARCHAR(33), DateColumn, 126) FROM MyTable 

should give you:

2009-04-30T12:34:56.790 
like image 117
marc_s Avatar answered Oct 04 '22 22:10

marc_s


This

SELECT CONVERT(NVARCHAR(30), GETDATE(), 126) 

will produce this

2009-05-01T14:18:12.430 

And some more detail on this can be found at MSDN.

like image 28
Cyberherbalist Avatar answered Oct 04 '22 20:10

Cyberherbalist