New built-in functions in SQL Server 2012: Part 1 - Conversion and DateTime Functions

Introduction

Microsoft SQL Server 2012 Release Candidate 0 has announced 3 new inbuilt conversion and 7 datetime functions. In this article, let us see how to use those new functions with their syntax and sample queries.

Those new functions are listed below.

1. Conversion functions
1. Parse
2. Try_Parse
3. Try_convert
2. DateTime Functions
1. DateFromParts
2. DateTimeFromParts
3. DateTime2FromParts
4. SmallDateTimeFromParts
5. DateTimeOffsetFromParts
6. TimeFromParts
7. EOMonth

Conversion Functions

There are three new conversion functions and for those who have already worked in the .NET Framework, they will easily figure out what will the functions do when I say parse, tryparse.

PARSE

This function will parse the value and return the result. In case if it is not able to parse, it will throw an error. You can use this function to convert strings/datetime to datetime or numeric values. Please trust me this function has performance issues compared to CAST/CONVERT.

PARSE ( string_value AS data_type [ USING culture ] )

This function expects three parameters:

• String_value - The expression which needs to be parsed.
• Data_type - To which data type we are converting to.
• CULTUre - To which culture, i.e., language such as gb-en, us-en. This is an optional parameter.

Let us see some examples to see how it works.

SELECT PARSE('08-04-2012' AS datetime USING 'en-US') AS Date
select cast('08-04-2012' AS datetime) as Date

Now the output is

So many people wonder why we have to use Parse when it produces the same output as the CAST function.

Suppose if you are not using ‘en-US’ culture, you are working in Paris, and your server date settings are native to  ‘fr-FR’, and you display date in DD/MM/YYYY format, then what will happen if you use the CAST function?

See the below queries:

SELECT PARSE('08-04-2012' AS datetime USING 'fr-fr') AS Date
select cast('08-04-2012' AS datetime) as Date

Now the output will be:

So now you might understand the real use of Parse I guess. And this is not the only one.

In my database I save inserted date as varchar and in the format “14-Aug-2012” like this. Then how will you convert that into normal datetime? That’s where the Parse function comes into use.

Consider my below queries and see the outputs.

SELECT PARSE('14-Aug-2012' AS datetime USING 'en-us') AS Date
SELECT PARSE('August 14,2012' AS datetime USING 'en-us') AS Date

Isn’t it good?? Saves developer’s time.

We have seen for datetime, now what about numeric? Let us see another example. In many countries, in decimals, instead of ‘.’  comma ‘,’ is used, especially in European countries. 125.00 is the same as 125,00 in France.

So in the database, I am having a varchar column but saving values in decimals and have records like

125,00
134,00
456,00

Now we have to go for culture options in the Parse function.

select parse('125,00' as decimal using 'en-US')
select parse('125,00' as decimal USING 'fr-FR')

These queries will give me output as

So the main advantage of the Parse function is to parse the expression for different cultures.

TRY_PARSE

It is similar to the Parse function, the only difference is when it is not able to parse, it will return a NULL instead of throwing an error as the Parse function.

TRY_PARSE ( string_value AS data_type [ USING culture ] )

This function expects three parameters:

• String_value - The expression which needs to be parsed
• Data_type - To which data type we are converting to
• CULTUre - To which culture i.e language such as gb-en, us-en; this is an optional parameter

Let us see some examples to understand how it works.

--try_parse demo
SELECT PARSE('13-04-2012' AS datetime USING 'en-us') AS Date
SELECT try_PARSE('13-04-2012' AS datetime USING 'en-us') AS Date

The output will be

And if you see in the message tab,

Because when Parse function is not able to parse, it will throw an error. But try_parse just returns null.

Now let us see another example. What will happen if I try to parse an alphanumeric string to integer?

select parse('df23' as int using 'en-US')
select try_parse('df34' as int USING 'en-US')

The output will be:

And if you check in the messages tab,

So try_parse avoids throwing an exception and returns null if it is not able to parse.

TRY_CONVERT

This function is similar to the existing Convert function but the difference is whenever it is not able to convert, it will return null.

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

This function expects three parameters:

• Data_type - To which data type we are converting to.
• Expression - The value to be converted/cast.
• style - Integer parameter that specifies how the cast expression should be. This is an optional parameter.

Let us see some examples to understand how it works.

SELECT CONVERT(datetime, '8/13/2012', 103) AS date
SELECT try_CONVERT(datetime, '8/13/2012', 103) AS date

Note here I am passing month as 13, so the conversion will fail. The first statement will throw an error. But what will happen with the secondone ?

See the output below.

And in the messages tab,

Now one of my varchar column always holds an integer value. But by mistake I saved an alphabet character once. So whenever I try to convert to integer using Cast or Convert, it throws me an error. But I don’t want error. If there are no integer values, then it should return null.

See the below queries and the output.

select try_convert(int,'a')
select convert(int,'a') 

So in general try_convert is similar to try_parse that whenever it is not able to convert, it will return null.

DateTime Functions

DateFromParts

This function returns a date for the specified year, month, and day.

DATEFROMPARTS ( YEAR,MONTH,DAY )

• Year - Year value in integer
• Month - Month value in integer, between 1 and 12
• Day - Day value in integer, between 1 and 31

Returns: Date

Let us see an example of how to use this function. Before MS SQL Server 2012, when we want to form date from year, month, and day, we used to do like this:

declare @year int=2012
declare @month int=4
declare @day int=8

SELECT Date=Convert(datetime,convert(varchar(10),@year)+'-'+convert(varchar(10),@day)+'-'+convert(varchar(10),@month),103)

And a few people used to do like this also:

declare @year int=2012
declare @month int=4
declare @day int=8
select dateadd(month,@month-1,dateadd(year,@year-1900,@day-1))

But with SQL Server 2012, the datefromparts function will make this simple.

declare @year int=2012
declare @month int=4
declare @day int=8
select date=DATEFROMPARTS(@year,@month,@day)

Remember it returns date and not datetime.

DateTimeFromParts

Remember the last line I said, the DateFromParts function will only return you the date. So what if I need to get a datetime value from year, month, day, and time as well? That’s where the DateTimeFromParts function comes into picture.

This function returns a datetime for the specified year, month, day, hour, minute, second, and precision.

DATETIMEFROMPARTS(year, month, day, hour, minute, seconds,milliseconds )

• Year - year value in integer
• Month - month value in integer, between 1 and 12
• Day - day value in integer, between 1 and 31
• Hour - hour value in integer
• Minute - minute value in integer
• Seconds - seconds value in integer
• Milliseconds - milliseconds value in integer

Returns: DateTime

Consider the below query.

declare @year int=2012
declare @month int=4
declare @day int=8
declare @hour int=5
declare @minute int=35
declare @seconds int=34
declare @milliseconds int=567
select date=DATETIMEFROMPARTS(@year,@month,@day,@hour,@minute,@seconds,@milliseconds)

The output will be

What will happen if I pass only 6 parameters like this?

declare @year int=2012
declare @month int=4
declare @day int=8
declare @hour int=5
declare @minute int=35
declare @seconds int=34
declare @milliseconds int=567
select date=DATETIMEFROMPARTS(@year,@month,@day,@hour,@minute,@seconds)

it will throw an error..

OK, what will happen if I pass six parameters and the 7^th parameter as null?

declare @year int=2012
declare @month int=4
declare @day int=8
declare @hour int=5
declare @minute int=35
declare @seconds int=34
declare @milliseconds int=567
select date=DATETIMEFROMPARTS(@year,@month,@day,@hour,@minute,@seconds,null)

This will return null. So whenever if one or more parameters are null, then the result also will be null.

DateTime2FromParts

This is similar to the above function but the difference is here we can set precision for time part and this function returns DateTime2.

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

• year - year value in integer
• month - month value in integer, between 1 and 12
• day - day value in integer, between 1 and 31
• hour - hour value in integer
• minute - minute value in integer
• fractions - fractions value in integer
• precision - precision value in integer

Return: DateTime2

Consider this below query.

declare @year int=2012
declare @month int=4
declare @day int=8
declare @hour int=5
declare @minute int=35
declare @seconds int=34
select date=DATETIME2FROMPARTS(@year,@month,@day,@hour,@minute,@seconds,0,0)

Here I am setting the fraction and precision both to 0. So the output will be

In the above query I am just changing precision to 2. Let us see what happens:

declare @year int=2012
declare @month int=4
declare @day int=8
declare @hour int=5
declare @minute int=35
declare @seconds int=34
select date=DATETIME2FROMPARTS(@year,@month,@day,@hour,@minute,@seconds,0,2)

Now you might be able to see the difference and understand what precision does. Yes it decides the precision of the datetime2 value to be returned. Let us see some more queries for this.

declare @year int=2012
declare @month int=4
declare @day int=8
declare @hour int=5
declare @minute int=35
declare @seconds int=34
select date=DATETIME2FROMPARTS(@year,@month,@day,@hour,@minute,@seconds,50,7)

This will return

declare @year int=2012
declare @month int=4
declare @day int=8
declare @hour int=5
declare @minute int=35
declare @seconds int=34
select date=DATETIME2FROMPARTS(@year,@month,@day,@hour,@minute,@seconds,567,2)

In the above query I set fractions as 567 and precision as 2, guess what will happen? Yes, it will throw an error. Unless I give precision 3 or more, this will throw an error.

SmallDateTimeFromParts

This function returns a smalldatetime value for the specified year, month, day, hour, and minute.

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

• Year - year value in integer
• Month - month value in integer, between 1 and 12
• Day - day value in integer, between 1 and 31
• Hour - hour value in integer
• Minute - minute value in integer

Return: SmallDateTime.

Consider this below query.

declare @year int=2012
declare @month int=4
declare @day int=8
declare @hour int=5
declare @minute int=35
select date=SmallDatetimeFromparts(@year,@month,@day,@hour,@minute)

The output will be

DateTimeOffsetFromParts

This function returns a datetimeoffset value for the specified date and time. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, 
             seconds, fractions, hour_offset, minute_offset, precision )

• Year - year value in integer
• Month - month value in integer, between 1 and 12
• Day - day value in integer, between 1 and 31
• Hour - hour value in integer
• Minute - minute value in integer
• Seconds - seconds value in integer
• fractions - fractions value in integer
• Hour_offset - hour portion of the time zone offset in integer
• Minute_offset - minute portion of the time zone offset in integer
• Precision - precision value in integer

Return: DateTimeOffset.

The offset arguments are used to represent the time zone offset. If the offset arguments are omitted, then the time zone offset is assumed to be 00:00, that is, there is no time zone offset. If the offset arguments are specified, then both arguments must be present and both must be positive or negative.

Consider the below query,

declare @year int=2012
declare @month int=4
declare @day int=8
declare @hour int=5
declare @minute int=35
declare @seconds int=45
select date=DATETIMEOFFSETFROMPARTS(@year,@month,@day,@hour,@minute,@seconds,567,12,40,3)

The output is

TimeFromParts

This function returns a time value for the specified  hour, minute, seconds, fractions, and precision.

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

• Hour - hour value in integer
• Minute - minute value in integer
• Seconds - seconds value in integer
• fractions - fractions value in integer
• precision - precision value in integer

Return: Time.

Consider this below query.

declare @hour int=5
declare @minute int=35
declare @seconds int=45
select date=TIMEFROMPARTS(@hour,@minute,@seconds,567,3)

The output will be

EOMonth

If you ask me, I will say that this is one of the important functions in the new version of SQL Server related with datetime. You will come to know why after I explain this.

How will you calculate the last date for the current month in SQL Server 2008?

If you ask me I will write,

declare @date1 datetime=getdate()
select dateadd(month,datediff(month,-1, @date1),-1)

Also a few peoples write this,

declare @date varchar(10)
set @date=convert(varchar,year(getdate()))+ '-' +convert(varchar,(month(getdate())+1))+'-01'
select dateadd(day,-1,@date)

Both will give the same output.

Now in SQL Server 2012, a new method is introduced to make this simple which is EOMONTH.

EOMONTH ( start_date [, month_to_add ] )

• Start_Date - the date for which end date for the month to be calculated
• Month_to_Add - number of months to add to the start_date; this is an optional parameter.

So EOMONTH will return the date which is the last date of the month entered.

I am writing another query for the above queries but using EOMONTH.

Select EOMONTH(getdate())

The output will be

In case you want to know the last date for last month or the month before that or next month, then we have to use the optional parameter Month_to_Add.

Select EOMONTH(getdate(),-1) as lastmonth
Select EOMONTH(getdate(),-2) as monthbeforethat
select EOMONTH(getdate(),1) as nextmonth

and the output will be