How to use ISO-8601 for date and datetime

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?