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 |