Home
Middle Tier - Do you submit all dates to SQL Server in ISO format?
  v1.0 Posted at 15/11/2019 9:29 AM by Tiago Araujo

All dates submitted to SQL Server must be in ISO format date. This ensures that language or database settings do not interfere with inserts and updates of data. You should NEVER need to change the default language of users or of the database in SQL Server. For example, any insert into a SQL Server database with Visual Basic should call Format(ctlStartDate,"yyyy-mm-dd") or VB.NET Ctype(ctlStartDate.Text,Date).ToString("yyyy-MM-dd") before attempting the insert or update. This will ensure consistency of treatment when dealing with dates in your SQL Server backend.​​

​SET DATEFORMAT mdy

print convert( datetime, '2003-07-01' )

-- returns Jul 1 2003 12:00AM

print convert( datetime, '01/07/2003' )

-- returns Jan 7 2003 12:00AM

print convert( datetime, '20030701' )

-- returns Jul 1 2003 12:00AM

SET DATEFORMAT dmy

print convert( datetime, '2003-07-01' )

-- returns Jan 7 2003 12:00AM, opposite of above

print convert( datetime, '01/07/2003' )

-- returns Jul 1 2003 12:00AM, opposite of above

print convert( datetime, '20030701' )

-- returns Jul 1 2003 12:00AM, only one which is same as above

​Code - ISO format date is the best.​


The extended format can still mix up month & day in some circumstances, whereas the basic format is the only one that always works correctly.

To be even more pedantic, when you include the time as well as the date, the value isn't really an ISO value at all! The ISO representation of a date/time would be '20030701T0958', whereas for SQL you should send it as '20030701 09:58'. This isn't even the extended ISO format as it is missing the obligatory "T" character (ref. section 5.4.1 of the standard).

(The standard does allow you to "be omitted in applications where there is no risk of confusing", but it doesn't allow you to add a space or mix basic date with extended time.)

So, if you want to be absolutely correct then it may be best to remove the reference to ISO, so that your rule works for date/time as well as just dates.

The technical term used in the SQL help is "Unseparated String Format" (easily searched for).

The help specifies that this format is unaffected by the SET DATEFORMAT command (which depends on any locale settings for SQL Server or the computer it is installed on).

"The SET DATEFORMAT session setting does not apply to all-numeric date entries (numeric entries without separators). Six- or eight-digit strings are always interpreted as ymd."

​What is ISO format date? ​

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: