SQL – How to convert datetime to formatted date string dd-mm-yyyy

When you have a date or datetime column in an SQL database, it will output a value that looks like 2017-01-01 00:00:00.000

This is the standard format used for datetime columns in SQL Server and saves the details as YYYY-MM-DD HH:MI:SS

But if you need to display the datetime in a human readable format you will need to convert it using CONVERT to cast it as a VARCHAR.

For example, to convert the column ‘Date_Of_Birth’ to dd-mm-yyyy format.

CONVERT( VARCHAR, Date_Of_Birth, 105 )

What about other date formats?

You can easily use different formats by replacing the 105 value with the value for your format

CodeOutputFormat
101mm/dd/yyyyUSA
102yyyy.mm.ddANSI
103dd/mm/yyyyBritish/French
104dd.mm.yyyyGerman
105dd-mm-yyyyItalian
106dd mon yyyy
107Mon dd, yyyy
108hh:mm:ss
109mon dd yyyy hh:mi:ss:mmmAM (or PM)Default + millisec
110mm-dd-yyyyUSA
111yyyy/mm/ddJapan
112yyyymmddISO
113dd mon yyyy hh:mi:ss:mmm (24h)Europe default + millisec
114hh:mi:ss:mmm (24h)
120yyyy-mm-dd hh:mi:ss (24h)ODBC canonical
121yyyy-mm-dd hh:mi:ss.mmm (24h)ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset
126yyyy-mm-ddThh:mi:ss.mmm (no spaces)ISO8601
127yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)ISO8601 with time zone Z
130dd mon yyyy hh:mi:ss:mmmAMHijiri
131dd/mm/yy hh:mi:ss:mmmAMHijiri

Reference: https://www.w3schools.com/sql/func_convert.asp

Was this article helpful?

Related Articles