A Standard SQL Date and Time Format Strings use any format specifier to convert the given date-time variable into the required format. We can use this Date and Time format specifier on both the DATETIME and datetimeoffset data types.
List of Standard SQL Date and Time Format Strings
In SQL Server 2012, Microsoft introduced the FORMAT function to format the SQL DATE and TIME. And within that FORMAT Function, we have to use either Standard or Custom format modifiers to format the given data.
The following list will show you the Standard SQL Date and Time Format Strings
| Format Specifier | Description | Example |
|---|---|---|
| “d” | Short Date Pattern | 2017-06-14T11:57:53 -> 6/14/2017 (en-US) 2017-06-14T11:57:53 -> 14/06/2017 (fr-FR) 2017-06-14T11:57:53 -> 14 (ja-JP) |
| “D” | Long Date Pattern | 2017-06-14T11:57:53 -> Wednesday, June 14, 2017 (en-US) 2017-06-14T11:57:53 -> 14 июня 2017 г (ru-RU) 2017-06-14T11:57:53 -> Mittwoch, 14. Juni 2017 (de-DE) |
| “f” | Full Date/Time pattern (Short Time) | 2017-06-14T11:57:53 -> Wednesday, June 14, 2017 11:57 AM (en-US) 2017-06-14T11:57:53 -> den 14 juni 2017 11:57 (sv-SE) 2017-06-14T11:57:53 -> Τετάρτη, 14 Ιουνίου 2017 11:57 πμ (el-GR) |
| “F” | Full Date/Time pattern (Long Time) | 2017-06-14T11:57:53 -> Wednesday, June 14, 2017 11:57:53 AM (en-US) 2017-06-14T11:57:53 -> den 14 juni 2017 11:57:53 (sv-SE) |
| “g” | General Date/Time pattern (Short Time) | 2017-06-14T11:57:53 -> 6/14/2017 11:57 AM (en-US) 2017-06-14T11:57:53 -> 14/06/2017 11:57 (es-ES) 2017-06-14T11:57:53 -> 2017/6/14 11:57 (zh-CN) |
| “G” | General Date/Time pattern (Long Time) | 2017-06-14T11:57:53 -> 6/14/2017 11:57:53 AM (en-US) 2017-06-14T11:57:53 -> 14/06/2017 11:57:53 (es-ES) |
| “M”, “m” | Month/Day pattern | 2017-06-14T11:57:53 -> June 14 (en-US) 2017-06-14T11:57:53 -> 14 Juni (id-ID) 2017-06-14T11:57:53 -> 14. juni (da-DK) |
| “o”, “O” | Round trip Date/Time pattern | With DateTime Variable Values: 2017-06-14T11:57:53 -> 2017-06-14T11:57:53.0000000 With DateTimeOffset Variable Values: 2017-06-14T11:57:53 -> 2017-06-14T11:57:53.0000000+00:00 |
| “r”, “R” | RFC1123 pattern | 2017-06-14T11:57:53 -> Wed, 14 Jun 2017 11:57:53 GMT |
| “s” | Sortable Date/Time pattern | 2017-06-14T11:57:53 -> 2017-06-14T11:57:53 |
| “t” | Short Time pattern | 2017-06-14T11:57:53 -> 11:57 AM (en-US) 2017-06-14T11:57:53 -> 11:57 (hr-HR) |
| “T” | Long Time Pattern | 2017-06-14T11:57:53 -> 11:57:53 AM (en-US) 2017-06-14T11:57:53 -> 11:57:53 ص (ar-EG) |
| “u” | Universal sortable Date/Time pattern | With DateTime Variable Values: 2017-06-14T11:57:53 -> 2017-06-14 11:57:53Z (en-US) 2017-06-14T11:57:53 -> 2017-06-14 11:57:53Z (sv-SE) 2017-06-14T11:57:53 -> 2017-06-14 11:57:53Z (el-GR)With DateTimeOffset Variable Values: 2017-06-14T11:57:53 -> 2017-06-14 11:57:53Z (en-US) 2017-06-14T11:57:53 -> 2017-06-14 11:57:53Z (sv-SE) |
| “U” | Universal Full Date/Time pattern | 2017-06-14T11:57:53 -> Wednesday, June 14, 2017 6:27:53 AM (en-US) 2017-06-14T11:57:53 -> den 14 juni 2017 11:57 (sv-SE) 2017-06-14T11:57:53 -> Τετάρτη, 14 Ιουνίου 2017 11:57 πμ (el-GR) |
| “y”, “Y” | Year Month pattern | 2017-06-14T11:57:53 -> June, 2017 (en-US) 2017-06-14T11:57:53 -> Juni 2017 (id-ID) |
Users who want to save the standard SQL date and Time Format Strings table in your mobiles can use the following image:
