SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

FORMAT SQL Server Dates Using FORMAT Function in SQL Server

FORMAT 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
FORMAT SQL Server Dates Using FORMAT Function in SQL Server

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
SQL Time Format with FORMAT Function

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

Get SQL Server Data & Time Using Format Function




Different Options for Date and Time formatting in SQL Server

OptionsExample Outputs
dGives output as entire date. Example Output 11/16/2016
ddGives output as day of the month between 01 and 31
dddGives output as day of the week in three characters.
Example Mon or Tue.
ddddGives output as day of the week. Example: Monday or Tuesday
fGives output excluding seconds.
Example Output Thursday, November 24, 2016 8:00 PM
FGives output including seconds.
Example Output Thursday, November 24, 2016 8:00:01 PM
DGives output as Weekday, Month Date, Year format.
Example Thursday, November 24, 2016
MGives output as Month and Date. Example November 24
MMGives output as Month between 01 to 12.
Example 11 for November
MMM
Gives output as Month in three characters.
Example Nov for November
MMMMGives output as Month Name. Example November
yGives output as Month and Year.
Example: November 2016
yyGives output as Year with two digits.
Example 16 for 2016
yyyGives output as Year with four digits. Example 2016
hhGives output as hour between 01 to 12.
HHGives output as hour between 00 to 23.
mmGives output as Minutes between 00 to 59.
ssGives output as Seconds between 00 to 59.
tGives output either in A or P. i.e., AM or PM in short-form.
ttGives 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 DATE Using Culture


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]
SQL FORMAT Currency Using Culture

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]
NUMBER FORMAT Using FORMAT Function in SQL Server

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

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement