In this article we will look into FORMAT function in SQL Server to format the date and time.
Suppose to get DD/MM/YYYY use SELECT FORMAT(getdate(),'dd/MM/yyyy') as date another example MM-DD-YY use SELECT FORMAT(getdate(),'MM-dd-yy') as date
SQL Server Format with Output Examples:
|
Query |
output |
|
SELECT FORMAT
(getdate(), 'dd/MM/yyyy ') as date |
21/03/2018 |
|
SELECT FORMAT
(getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date |
21/03/2018, 11:36:14 |
|
SELECT FORMAT
(getdate(), 'dddd, MMMM, yyyy') as date |
Wednesday, March,
2018 |
|
SELECT FORMAT (getdate(),
'MMM dd yyyy') as date |
Mar 21 2018 |
|
SELECT FORMAT
(getdate(), 'MM.dd.yy') as date |
03.21.18 |
|
SELECT FORMAT
(getdate(), 'MM-dd-yy') as date |
03-21-18 |
|
SELECT FORMAT
(getdate(), 'hh:mm:ss tt') as date |
11:36:14 AM |
|
SELECT FORMAT
(getdate(), 'd','us') as date |
03/21/2018 |
we can use a lot of options for the date and time formatting, which
are listed below.
- dd - this is day of month from 01-31
- dddd - this is the day spelled out
- MM - this is the month number from 01-12
- MMM - month name abbreviated
- MMMM - this is the month spelled out
- yy - this is the year with two digits
- yyyy - this is the year with four digits
- hh - this is the hour from 01-12
- HH - this is the hour from 00-23
- mm - this is the minute from 00-59
- ss - this is the second from 00-59
- tt - this shows either AM or PM
- d - this is day of month from 1-31 (if this is used on its own it will display the entire date)
- us - this shows the date using the US culture which is MM/DD/YYYY
