Ashish MehtaFORMAT Date in SQL Server
FORMAT Function was introduced in SQL Server 2012, and it is available in all the later versions of SQL Server. This article will show different examples of using the new FORMAT function in SQL Server 2012 and later versions to format dates.
SYNTAX for SQL Server FORMAT Function
FORMAT (value, format [,culture])SQL Date Format with FORMAT Function
If you need output in MM/DD/YYYY format, execute the TSQL Script below.
SELECT FORMAT (getdate(), 'MM/dd/yyyy') as [Today's Date]
GO
SQL Server Date Format with FORMAT Function
If you need output in MM-DD-YY format, execute the TSQL Script below.
SELECT FORMAT (getdate(), 'MM-dd-yy') as [Today's Date]
GO
SQL Server Format Date with Format Function
If you need output in DD/MM/YYYY format, execute the TSQL Script below.
SELECT FORMAT (getdate(), 'dd/MM/yyyy') as [Today's Date];
GO
Date Format in SQL Server
If you need output in DD-MM-YY format, execute the TSQL Script below.
SELECT FORMAT (getdate(), 'dd-MM-yy') as [Today's Date]
GO
Important Note: Format dd represents day number between 1 to 31, MM represents month number between 1 to 12, yy represents two-year digits, and yyyy represents the complete year. To see the output as time in hours, minutes, and seconds, one needs to use ‘hh:mm:ss’.
SQL Time Format with FORMAT Function
If you need output in hh:mm:ss AM/PM format, execute the TSQL Script below.
SELECT FORMAT (GETDATE(), 'hh:mm:ss tt') as [Time in Hours:Minutes:Seconds Format]
GO
Get SQL Server Data Time Using Format Function
If you need the output with data and time, execute the below TSQL Script. The script will give SQL Format Datetime.
SELECT FORMAT (GETDATE(), 'dd-MM-yyyy, hh:mm:ss') AS [Data & Time]Format Date Time SQL | Date Time Format SQL
Different Options for Date and Time formatting in SQL Server
| Options | Example Outputs |
| d | Gives output as entire date. Example Output 11/16/2016 |
| dd | Gives output as day of the month between 01 and 31 |
| ddd | Gives output as day of the week in three characters. Example Mon or Tue. |
| dddd | Gives output as day of the week. Example: Monday or Tuesday |
| f | Gives output excluding seconds. Example Output Thursday, November 24, 2016 8:00 PM |
| F | Gives output including seconds. Example Output Thursday, November 24, 2016 8:00:01 PM |
| D | Gives output as Weekday, Month Date, Year format. Example Thursday, November 24, 2016 |
| M | Gives output as Month and Date. Example November 24 |
| MM | Gives output as Month between 01 to 12. Example 11 for November |
| MMM | Gives output as Month in three characters. Example Nov for November |
| MMMM | Gives output as Month Name. Example November |
| y | Gives output as Month and Year. Example: November 2016 |
| yy | Gives output as Year with two digits. Example 16 for 2016 |
| yyy | Gives output as Year with four digits. Example 2016 |
| hh | Gives output as hour between 01 to 12. |
| HH | Gives output as hour between 00 to 23. |
| mm | Gives output as Minutes between 00 to 59. |
| ss | Gives output as Seconds between 00 to 59. |
| t | Gives output either in A or P. i.e., AM or PM in short-form. |
| tt | Gives output either in AM or PM. |
SQL FORMAT DATE Using Culture
It returns a value formatted with the specified format and optional culture.
SELECT FORMAT (GETDATE(), 'd', 'en-us') AS [US English Format]
, FORMAT (GETDATE(), 'd', 'de-de') AS [German Format]
, FORMAT (GETDATE(), 'd', 'hi-IN') AS [India Format]
SQL FORMAT Currency Using Culture
One can format currency according to the culture using the Format function.
DECLARE @Price AS INT
SET @Price = '750000'
SELECT FORMAT (@Price, 'c', 'en-us') as [US Currency]
,FORMAT (@Price, 'c', 'de-DE') as [Euro Currency]
,FORMAT (@Price, 'c', 'hi-IN') as [India Currency]
NUMBER FORMAT Using FORMAT Function in SQL Server
One can format numbers according to the culture using the Format function.
DECLARE @Price AS INT
SET @Price = '750000'
SELECT FORMAT (@Price, 'N', 'en-us') as [US Currency]
,FORMAT (@Price, 'N', 'de-DE') as [Euro Currency]
,FORMAT (@Price, 'N', 'hi-IN') as [India Currency]
Conclusion
This article outlined how to use the FORMAT Function to Format Dates, Format Currency, and Format Numbers based on the culture.
More… TSQL Enhancements in SQL Server
- TRUNCATE TABLE WITH PARTITIONS
- DROP IF EXISTS
- ALTER TABLE WITH (ONLINE = ON | OFF)
- MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP
- ALTER DATABASE SET AUTOGROW_SINGLE_FILE
- ALTER DATABASE SET AUTOGROW_ALL_FILES
- COMPRESS and DECOMPRESS Functions
- STRING_SPLIT and STRING_ESCAPE Functions
- FORMATMESSAGE Statement
- SERVERPROPERTY Function
Chetna Bhalla