How to use ISO-8601 for date and datetime

27 April,2015 by Jack Vamvas

Question: I requested some sql code for release to production. The code was rejected . One of the reasons was the inconsistent representation of dates . The suggestion was to standardise on ISO-8601 format for string represenstations of dates. Why should I use ISO-8601 and what are the details of usage?

Here is an example of code used

 

declare @mydateA datetime
declare @myDateB  datetime 
set @mydateA='2013/03/06 12:23:45'
set @mydateB='2013/04/06 21:10:12'

 

 

Answer:  The ISO 8601 is an international standard for representing dates and times using numbers.

In multi-tier applications date formats are set differently across the different layers. For example, there could be a date entry at the user interface, a date set in the application layer and passing through to the database.

If tight controls are not in place, inconsistent dates can be entered into the database and depending on the code errors can occur. A typical error is Conversion failed when converting date and/or time from character string.

The main aim of ISO 8601 is to create a standard.

The ISO 8601 format for date is YYYYMMDD

The ISO 8601 format for datetime  is YYYY-MM-DDTHH:MM:SS . Note the usage of dashes and the middle T to segregate data and time

Another benefit of adopting the ISO 8601 standard is allowing less error prone interaction with other data sources such as DB2. In ETL jobs it is common to move data between different database platforms. Standardising the date and datetime formats makes data easier to integrate.

Although useful, I avoid using SQL functions such as DATENAME - which should be dealt with at the presentation layer - read more on How to return a character string from a sql datepart using DATENAME

Read More on standards

SQL Server - ISO-11179 Naming Conventions and SQL DDL - SQL ...

SQL server – 5 things SQL Server developers should know about SQL Server

SQL Server Performance Killers

Stored Procedures versus ad-hoc paramaterized queries.Which is faster?

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to use ISO-8601 for date and datetime


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer