Microsoft SQL Server Dates Date & Time Formatting using FORMAT
Example
SQL Server 2012
You can utilize the new function: FORMAT().
Using this you can transform your DATETIME fields to your own custom VARCHAR format.
Example
DECLARE @Date DATETIME = '2016-09-05 00:01:02.333'
SELECT FORMAT(@Date, N'dddd, MMMM dd, yyyy hh:mm:ss tt')
Monday, September 05, 2016 12:01:02 AM
Arguments
Given the DATETIME being formatted is 2016-09-05 00:01:02.333, the following chart shows what their output would be for the provided argument.
| Argument | Output |
|---|---|
| yyyy | 2016 |
| yy | 16 |
| MMMM | September |
| MM | 09 |
| M | 9 |
| dddd | Monday |
| ddd | Mon |
| dd | 05 |
| d | 5 |
| HH | 00 |
| H | 0 |
| hh | 12 |
| h | 12 |
| mm | 01 |
| m | 1 |
| ss | 02 |
| s | 2 |
| tt | AM |
| t | A |
| fff | 333 |
| ff | 33 |
| f | 3 |
You can also supply a single argument to the FORMAT() function to generate a pre-formatted output:
DECLARE @Date DATETIME = '2016-09-05 00:01:02.333'
SELECT FORMAT(@Date, N'U')
Monday, September 05, 2016 4:01:02 AM
| Single Argument | Output |
|---|---|
| D | Monday, September 05, 2016 |
| d | 9/5/2016 |
| F | Monday, September 05, 2016 12:01:02 AM |
| f | Monday, September 05, 2016 12:01 AM |
| G | 9/5/2016 12:01:02 AM |
| g | 9/5/2016 12:01 AM |
| M | September 05 |
| O | 2016-09-05T00:01:02.3330000 |
| R | Mon, 05 Sep 2016 00:01:02 GMT |
| s | 2016-09-05T00:01:02 |
| T | 12:01:02 AM |
| t | 12:01 AM |
| U | Monday, September 05, 2016 4:01:02 AM |
| u | 2016-09-05 00:01:02Z |
| Y | September, 2016 |
Note: The above list is using the en-US culture. A different culture can be specified for the FORMAT() via the third parameter:
DECLARE @Date DATETIME = '2016-09-05 00:01:02.333'
SELECT FORMAT(@Date, N'U', 'zh-cn')
2016年9月5日 4:01:02
