SQL 2012 – New SQL Functions – Dates and Formating

There are several new features in SQL Server 2012 that deal with dates and formatting.

Before 2012, calculating the last day of the month usually involved some sort of hack, like going to the first day of the following month and then subtracting a day.
The new EOMONTH Will take a date or datetime as will return the last day of that month.
select EOMONTH(‘2012-04-17’)
returns a date:

This function will return a date from integers representing the year, month and day.
select DATEFROMPARTS(2012, 4, 17)
Returns a date:

Format using .Net format values

select FORMAT(getdate(), ‘MMMM d, yyyy’)
Return a string:
April 17, 2012

Date Format Strings

FORMAT can also be used with types other than dates

select FORMAT(1, ‘P’)
returns a percentage:
100.00 %

select FORMAT(987.45, ‘C’)
Returns currency:

select FORMAT(100, ‘X’)
Returns Hexadecimal:

Numeric Formats


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: