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 the CONVERT function.

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

Code Output Format
101 mm/dd/yyyy USA
102 yyyy.mm.dd ANSI
103 dd/mm/yyyy British/French
104 dd.mm.yyyy German
105 dd-mm-yyyy Italian
106 dd mon yyyy
107 Mon dd, yyyy
108 hh:mm:ss
109 mon dd yyyy hh:mi:ss:mmmAM (or PM) Default + millisec
110 mm-dd-yyyy USA
111 yyyy/mm/dd Japan
112 yyyymmdd ISO
113 dd mon yyyy hh:mi:ss:mmm (24h) Europe default + millisec
114 hh:mi:ss:mmm (24h)
120 yyyy-mm-dd hh:mi:ss (24h) ODBC canonical
121 yyyy-mm-dd hh:mi:ss.mmm (24h) ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset
126 yyyy-mm-ddThh:mi:ss.mmm (no spaces) ISO8601
127 yyyy-mm-ddThh:mi:ss.mmmZ (no spaces) ISO8601 with time zone Z
130 dd mon yyyy hh:mi:ss:mmmAM Hijiri
131 dd/mm/yy hh:mi:ss:mmmAM Hijiri

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