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.  

2 comments:

  1. i want to convert the date into the same format of getdate() to have comparision with the date passed from front.? how can i do that..?

    boston long distance movers

    ReplyDelete
  2. excellent piece of information, I had come to know about your website from my friend kishore, pune,i have read atleast 8 posts of yours by now, and let me tell you, your site gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanx a lot once again, Regards, Difference Between sql and tsql




    ReplyDelete