Thursday, September 8, 2011

New T-SQL Functions in SQL Server Denali Part 2 - Date Time Functions

There are around 7 new DateTime functions introduced in Denali.
EOMONTH
DATEFROMPARTS
DATETIME2FROMPARTS
DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS
SMALLDATETIMEFROMPARTS
TIMEFROMPARTS

EOMONTH
EOMonth Function returns the End Date of the given date's month. Also this takes an optional parameter Month to add. If month to add value is passed to the function , that number of months added to the date and gets the end date of that month.

Syntax : EOMONTH ( start_date [, month_to_add ] )

In the below example the first column returns the End date of the Month "September 2011" for the date passed as start_date. The Second column returns the End date of the Month "February 2012" as Month to add is passed as 5.



DATEFROMPARTS
DateFromParts function will return a specific date of date datatype for the Year , Month and Day passed as parameters.In the existing version , if we have Year , Month and Date we need to do a conversion of these values to string. Then concatenate these values and convert back to a DateTime as shown in below example. This function completly avoids the need for such a complex code. Also takes care of the issue during concatenation the month and day may get interchanged due to date format as shown in below example.

Syntax : DATEFROMPARTS ( year, month, day )

DATETIMEFROMPARTS

DateTimeFromParts is similar to DateFromParts. The difference is it will return Date of DateTime datatype by taking year, month, day, hour, minute, seconds and milliseconds as input.

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

DATETIME2FROMPARTS
DateTime2FromParts is similar to DateFromParts. The difference is it will return Date of DateTime2 datatype by taking year, month, day, hour, minute, seconds, fractions and precision  as input.

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

DATETIMEOFFSETFROMPARTS
DateTimeOffsetFromParts is similar to DateFromParts. The difference is it will return Date of DateTimeOffset datatype by taking year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset and  precision as input.

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


SMALLDATETIMEFROMPARTS
SmallDateTimeFromParts is similar to DateFromParts. The difference is it will return Date of DateTime datatype by taking year, month, day, hour and minute as input.

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

TIMEFROMPARTS
TimeFromParts will return Time of Time datatype by taking hour, minute, seconds, fractions and precision as input.

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

Below is the total consolidated example for the all the Date & Time From part function


Next i will continue with the new Logical functions.  

Tuesday, September 6, 2011

New T-SQL Functions in SQL Server Denali Part 1 - Conversion Functions

It has been more than a month since i blogged.Feels good to be back in action !!!

There are around 22 new functions are introduced in Denali. In this series of posts let us take a look into functions category wise and what problem they intend to solve.

First let us take a look into the new Conversion functions

            PARSE
            TRY_PARSE
            TRY_CONVERT
 
PARSE

Parse is similar to Cast function which converts the data to required data type. But in Parse function we can include the culture in which it has to be converted.If no culture is provided , it will use the default culture. Parse function supports only converting the String values to  Numeric and Datetime Datatypes.

Syntax : PARSE(String_Value As Data_Type [USING Culture])

 

TRY_PARSE

Try_Parse function will check whether the parse function will be succesfull or not for the given string value , data type and culture.If the unable to parse the string value to the datatype the function will return NULL.Try_Parse takes the same parameters as like Parse function.This will be very usefull during data profiling to find out error records which cannot be parsed.

Syntax : TRY_PARSE(String_Value As Data_Type [USING Culture])

In the below example , the culture "en-US" returns value and "ar-SA" returns NULL as it is unable to parse the string into date with the arabian culture.

 

 
TRY_CONVERT

Try_Convert function will check whether the expression can be converted to the destination datatype or not. If unable to convert it returns NULL. This is similar to Try_Parse.But this checks the CONVERT function.This function has the same parameters as like CONVERT function.

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

In the below sample , when the conversion is succesfull it returns the converted value for string to date conversion. But while trying to convert string to int , it returns NULL as conversion cannot happen from string to int.

 

These are the three new conversion functions in Denali CTP3. Hope this post is helpfull and will continue to write on other function. The next post will be on the new function on Date and Time Category.