SQL 2012 – New SQL Functions – Dates and Formating

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

EOMONTH:
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:
2012-04-30

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

FORMAT:
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:
$987.45

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

Numeric Formats

Advertisements

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: