New DATE TIME functions in SQL Server 2012

New DATE TIME functions in SQL Server 2012

SQL Server 2012 has introduced many new functions to cater the need of SQL developer. I have provided introduction of some new conversion function before (click here to look at new conversion functions). Today I am giving introduction of few of my favorite new Datetime related functions in SQL Server 2012.

There are already many datetime functions available in SQL Server since long but still people want more to rescue their script from complexity. Microsoft listen to developer and gave some more powerful functions (though I would need few more but hoping those will be included in next version of SQL Server, may be SQL Server 2014!!!!??? :))

 EOMonth: This is one of my favorite new functions in DATETIME group in SQL Server 2012. EOMonth stands for End of Month. As name suggests, it will return the last date (only DATE not TIME) of any month. Remember, we had to make customize logic to find end of month date before SQL Server 2012???

Here is the very small query to show the usage of EOMonth function in SQL Server 2012

[sourcecode language=”sql”]

SELECT

GETDATE() AS CurrentDate

,EOMONTH(GETDATE()) AS LastDayOfMonth

,DATEADD(DAY,1,EOMONTH(GETDATE())) AS FirstDayofNextMonth

,EOMONTH(GETDATE(),-1) AS LastDayOfPreviousMonth

,EOMONTH(GETDATE(),1) AS LastDayOfNextMonth

[/sourcecode]

DATEFROMPARTS: It happens many time that we have day and/or year and/or month as a separate part and need a date from those parts based on our customize logic, what do you in that scenario? Concate the string or develop customize logic to cater this need, right? DATEFROMPARTS function will save you from developing customize logic or concating the string. See the sample script given below to see the use of DATEFROMPARTS function in SQL Server 2012.

[sourcecode language=”sql”]

DECLARE @DAY INT=1

,@MONTH INT=11

,@YEAR INT=2012

 

SELECT

DATEFROMPARTS(@YEAR,@MONTH,@DAY) AS ConcatedDate

[/sourcecode]

Remember that DATEFROMPARTS function will return only date part not time.

DATETIMEFROMPARTS: Above given DATEFROMPARTS function will return only date what, if we need DATETIME format? You have to use DATETIMEFROMPARTS function. Look at the script below:

[sourcecode language=”sql”]

DECLARE @DAY INT=1

,@MONTH INT=11

,@YEAR INT=2012

–along with all of the above variables,

–you have to pass hour, minutes, second and milliseconds too

–no matter whether you pass it as variable or static value

SELECT

DATETIMEFROMPARTS(@YEAR,@MONTH,@DAY,5,59,0,150) AS ConcatedDateTime

[/sourcecode]

TIMEFROMPARTS: Since we have DATEFROMPARTS function which provides us only DATE without TIME but some time we may need to concat the TIME without DATE. TIMEFROMPARTS function will come as a rescue in that situation.

[sourcecode language=”sql”]

DECLARE

@HOUR INT=11

,@MINUTE INT=59

,@SECONDS INT=59

,@FRACTIONS INT=150

–last argument "6" is precision for milliseconds

SELECT

TIMEFROMPARTS(@HOUR,@MINUTE,@SECONDS,150,6) AS ConcatedTime

[/sourcecode]

You can find so many DATETIME related new functions in SQL Server 2012, from BOL, as I have mentioned only few of my favorite new DATE TIME related functions.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

4 thoughts on “New DATE TIME functions in SQL Server 2012”

Comments are closed.