I have been given a spec that requires the ISO 8601 date format, does any one no the conversion codes or a way of getting these 2 examples:
ISO 8601 Extended Date 2000-01-14T13:42Z
ISO 8601 Basic Date 20090123T105321Z
I have been given a spec that requires the ISO 8601 date format, does any one no the conversion codes or a way of getting these 2 examples:
ISO 8601 Extended Date 2000-01-14T13:42Z
ISO 8601 Basic Date 20090123T105321Z
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(DateField) VALUES('2009-04-30')
and you're done.
If you need to convert a date field to ISO-8601 format on SELECT, you can use conversion code 126 or 127 (with timezone information) to achieve the ISO format.
SELECT CONVERT(DATETIME, DateField, 126) FROM MyTable
should give you:
2009-04-30 00:00:00.000
Marc
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.