One of the most frequently
asked questions in SQL Server forums is how to format a datetime value or
column into a specific date format. Do you think January 5th 2013 should be shown as 1/5/2013 or
5/1/2013? The best answer is to each their own. The FORMAT function lets
you pick the style, or better yet it can be relevant to the culture of the computer
running the query.
It is worth to note that the
outputs of these date formats are of VARCHAR data types already and not of DATETIME data type. With this in mind,
any date comparisons performed after the datetime value has been formatted are
using the VARCHAR value of the date and time and not its original DATETIME value.
The FORMAT( ) function is used to format how a field is
to be displayed.
SQL
FORMAT( ) Syntax:
SELECT
FORMAT(column_name,format) FROM table_name;
SQL FORMAT( )
Example:
Assumed that we have a table named “Products” given below.
|
ProductID
|
ProductName
|
Price
|
|
1
|
Ipoh
Coffee
|
46
|
|
2
|
Gula
Malacca
|
19.45
|
|
3
|
Røgede
sild
|
9.5
|
|
4
|
Spegesild
|
12
|
|
5
|
Zaanse
koeken
|
9.5
|
|
6
|
Chocolade
|
12.75
|
We are going to select Product Name and the price for
today. Date format is YYYY-MM-DD.
The syntax for
Format:
SELECT
ProductName, Price, FORMAT(Now( ),'YYYY-MM-DD') AS PriceDate
FROM
Products;
Output:
|
ProductName
|
Price
|
PriceDate
|
|
Ipoh Coffee
|
46
|
2014-05-29
|
|
Gula Malacca
|
19.45
|
2014-05-29
|
|
Røgede sild
|
9.5
|
2014-05-29
|
|
Spegesild
|
12
|
2014-05-29
|
|
Zaanse koeken
|
9.5
|
2014-05-29
|
|
Chocolade
|
12.75
|
2014-05-29
|
The SQL statements used below to return the
different date formats use the SYSDATETIME( ) date function. The SYSDATETIME( )
function returns a datetime value that contains the date and time of the
computer on which the instance of SQL Server is running. The SYSDATETIME( )
function used below can be replaced by the GETDATE( ) or GETUTCDATE( ) functions.
The results will be the same unless the date format includes the nanosecond
portion of the time.
To make the date format results consistent,
the date and time used to generate the sample output is June 8, 2011
1:30:45.9428675 PM.
|
Date Format
|
FORMAT Function
|
Sample Output
|
|
Mon DD YYYY
HH:MIAM (or PM) |
SELECT
FORMAT(SYSDATETIME( ), 'Mon d yyyy h:mmtt')
|
Jun 8 2011 1:30PM
|
|
MM/DD/YY
|
SELECT
FORMAT(SYSDATETIME( ), 'MM/dd/yy') AS [MM/DD/YY]
|
06/08/11
|
|
MM/DD/YYYY
|
SELECT
FORMAT(SYSDATETIME( ), 'MM/dd/yyyy') AS [MM/DD/YYYY]
|
06/08/2011
|
|
YY.MM.DD
|
SELECT
FORMAT(SYSDATETIME( ), 'yy.MM.dd') AS [YY.MM.DD]
|
11.06.08
|
|
YYYY.MM.DD
|
SELECT
FORMAT(SYSDATETIME( ), 'yyyy.MM.dd') AS [YYYY.MM.DD]
|
2011.06.08
|
|
DD/MM/YY
|
SELECT
FORMAT(SYSDATETIME( ), 'dd/MM/yy') AS [DD/MM/YY]
|
08/06/11
|
|
DD/MM/YYYY
|
SELECT
FORMAT(SYSDATETIME( ), 'dd/MM/yyyy') AS [DD/MM/YYYY]
|
08/06/2011
|
|
DD.MM.YY
|
SELECT
FORMAT(SYSDATETIME( ), 'dd.MM.yy') AS [DD.MM.YY]
|
08.06.11
|
|
DD.MM.YYYY
|
SELECT
FORMAT(SYSDATETIME( ), 'dd.MM.yyyy') AS [DD.MM.YYYY]
|
08.06.2011
|
|
DD-MM-YY
|
SELECT FORMAT(SYSDATETIME(
), 'dd-MM-yy') AS [DD-MM-YY]
|
08-06-11
|
|
DD-MM-YYYY
|
SELECT
FORMAT(SYSDATETIME( ), 'dd-MM-yyyy') AS [DD-MM-YYYY]
|
08-06-2011
|
|
DD Mon YY
|
SELECT
FORMAT(SYSDATETIME( ), 'dd MMM yy') AS [DD MON YY]
|
08 Jun 11
|
|
DD Mon YYYY
|
SELECT FORMAT(SYSDATETIME(
), 'dd MMM yyyy') AS [DD MON YYYY]
|
08 Jun 2011
|
|
Mon DD, YY
|
SELECT
FORMAT(SYSDATETIME( ), 'MMM dd, yy') AS [Mon DD, YY]
|
Jun 08, 11
|
|
Mon DD, YYYY
|
SELECT
FORMAT(SYSDATETIME( ), 'MMM dd, yyyy') AS [Mon DD, YYYY]
|
Jun 08, 2011
|
|
HH:MM:SS
|
SELECT FORMAT(SYSDATETIME(
), 'HH:mm:ss')
|
13:30:45
|
|
Mon D YYYY
H:MI:SS.NNNNNNNAM (or PM)
|
SELECT
FORMAT(SYSDATETIME( ), 'MMM d yyyy h:mm:ss.ffffffftt')
|
Jun 8 2011 1:30:45.9428675PM
|
|
MM-DD-YY
|
SELECT
FORMAT(SYSDATETIME( ), 'MM-dd-yy') AS [MM-DD-YY]
|
06-08-11
|
|
MM-DD-YYYY
|
SELECT
FORMAT(SYSDATETIME( ), 'MM-dd-yyyy') AS [MM-DD-YYYY]
|
06-08-2011
|
|
YY/MM/DD
|
SELECT
FORMAT(SYSDATETIME( ), 'yy/MM/dd') AS [YY/MM/DD]
|
11/06/08
|
|
YYYY/MM/DD
|
SELECT
FORMAT(SYSDATETIME( ), 'yyyy/MM/dd') AS [YYYY/MM/DD]
|
2011/06/08
|
|
YYMMDD
|
SELECT FORMAT(SYSDATETIME(
), 'yyMMdd') AS [YYMMDD]
|
110608
|
|
YYYYMMDD
|
SELECT
FORMAT(SYSDATETIME( ), 'yyyyMMdd') AS [YYYYMMDD]
|
20110608
|
|
DD Mon YYYY
HH:MM:SS.NNNNNNN(24h)
|
SELECT
FORMAT(SYSDATETIME( ), 'dd MMM yyyy HH:mm:ss.fffffff')
|
08 Jun 2011 13:30:45.9428675
|
|
HH:MI:SS.NNNNNNN(24H)
|
SELECT
FORMAT(SYSDATETIME( ), 'HH:mm:ss.fffffff') AS [HH:MI:SS:MMM(24H)]
|
13:30:45.9428675
|
|
YYYY-MM-DD
HH:MI:SS(24h)
|
SELECT
FORMAT(SYSDATETIME( ), 'yyyy-MM-dd HH:mm:ss')
|
2011-06-08 13:30:45
|
|
YYYY-MM-DD
HH:MI:SS.NNNNNNN(24h)
|
SELECT FORMAT(SYSDATETIME(
), 'yyyy-MM-dd HH:mm:ss.fffffff')
|
2011-06-08 13:30:45.9428675
|
|
MM/DD/YY HH:MI:SS
AM
|
SELECT
FORMAT(SYSDATETIME( ), 'MM/dd/yy h:mm:ss tt')
|
06/08/11 1:30:45 PM
|
|
YYYY-MM-DD
|
SELECT
FORMAT(SYSDATETIME( ), 'yyyy-MM-dd')
|
2011-06-09
|
|
HH:MI:SS (24h)
|
SELECT
FORMAT(SYSDATETIME( ), 'HH:mm:ss')
|
13:30:45
|
|
YYYY-MM-DD
HH:MI:SS.NNNNNNN
|
SELECT
FORMAT(SYSDATETIME( ), 'yyyy-MM-dd HH:mm:ss.fffffff')
|
2011-06-08 13:30:45.9428675
|
|
YYYY-MM-DDTHH:MM:SS:NNNNNNN
|
SELECT
FORMAT(SYSDATETIME( ), 'yyyy-MM-ddTHH:mm:ss.fffffff')
|
2011-06-08T13:30:45.9428675
|
|
DD Mon YYYY
HH:MI:SS.NNNNNNNAM
|
SELECT
FORMAT(SYSDATETIME( ), 'dd MMM yyyy h:mm:ss.ffffffftt')
|
08 Jun 2011 1:30:45.9428675PM
|
|
DD/MM/YYYY
HH:MI:SS.NNNNNNNAM
|
SELECT
FORMAT(SYSDATETIME( ), 'dd/MM/yyyy h:mm:ss.ffffffftt')
|
08/06/2011 1:30:45.9428675PM
|
No comments:
Post a Comment