In this article, we will learn how to use the Date Formats in SQL.
SQL Server has many built-in Date Formats.
GETDATE() is a date function used to return the current database system date and time.
FORMAT() is a string function used to format a value with the specified format.
| Format | SQL Statement |
|---|---|
| MM/dd/yyyy |
declare @date datetime = '2019-01-01' SELECT FORMAT(@date, 'MM/dd/yyyy'); Output: 01/01/2019 |
| M/dd/yyyy |
declare @date datetime = '2019-01-01' SELECT FORMAT(@date, 'M/dd/yyyy'); Output: 1/01/2019 |
| dddd, MMMM dd, yyyy |
SELECT FORMAT(getdate(), 'dddd, MMMM dd, yyyy'); Output: Tuesday, December 10, 2019 |
| ddd, MMMM dd, yyyy |
SELECT FORMAT(getdate(), 'ddd, MMMM dd, yyyy'); Output: Tue, December 10, 2019 |
| dd MMM yyyy |
declare @date datetime = '2019-01-01' SELECT FORMAT(@date, 'dd MMM yyyy'); Output: 01 Jan 2019 |
| d MMM yyyy |
declare @date datetime = '2019-01-01' SELECT FORMAT(@date, 'd MMM yyyy'); Output: 1 Jan 2019 |
| dd/MM/yy |
SELECT FORMAT(getdate(), 'dd/MM/yy'); Output: 10/12/19 |
| hh:mm:ss tt |
SELECT FORMAT(getdate(), 'hh:mm:ss tt'); Output: 03:06:05 PM |
| h:mm:ss tt |
SELECT FORMAT(getdate(), 'h:mm:ss tt'); Output: 3:06:05 PM |
| h:m:ss tt |
SELECT FORMAT(getdate(), 'h:m:ss tt'); Output: 3:6:05 PM |
| MM/dd/yyyy hh:mm:ss |
SELECT FORMAT(getdate(), 'MM/dd/yyyy hh:mm:ss'); Output: 12/10/2019 03:08:29 |
Also, check How To Use Date Functions In SQL
