Menu
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 
753
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).
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.
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".
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 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.
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
