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
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
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.
DECLARE @DAY INT=1 ,@MONTH INT=11 ,@YEAR INT=2012 SELECT DATEFROMPARTS(@YEAR,@MONTH,@DAY) AS ConcatedDate
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:
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
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.
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
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.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.