Dealing with SQL dates and the new DATETIMEFROMPARTS  function

For years messing with dates in SQL has always been a pain, and you also had multiple date formats as some countries use MM/DD/YYYY AND SOME USE DD/MM/YYYY  as well as many others, the usual method was to create a function to perform an action,

something like returning the first or last day of a given month used to look like

CREATE FUNCTION DateFirstOfMonth ( @TheDate DATETIME )
RETURNS DATETIME 
AS
BEGIN 
    DECLARE @NewDate DATETIME
    SET @NewDate = CONVERT(VARCHAR(7), @TheDate, 21) +'-01 00:00:00'
    RETURN @NewDate
END 
GO

Admittedly there may be an easier way this is just an example, but now it can be replaced by the below which is somewhat easier to read and understand

CREATE FUNCTION DateFirstOfMonth ( @TheDate DATETIME )
RETURNS DATETIME
AS
BEGIN 
    RETURN  
	DATETIMEFROMPARTS (
		DATEPART(yyyy,@TheDate),
		DATEPART(mm,@TheDate),
		1,
		DATEPART(hh,@TheDate),
		DATEPART(mm,@TheDate),
		DATEPART(ss,@TheDate),
		DATEPART(ms,@TheDate)
	 )
END 
GO

The result, in the new procedure I’ve made use of a new’ish function called DATETIMEFROMPARTS , which basically does what it says, it builds a datetime from its separate parts. In my simple example, I’ve also included the time whereas in my original procedure, I just wiped it out back to zero.

The DATETIMEFROMPARTS function is available in sql from SQL 2012 onwards 

Some more examples This also makes use of DATEFROMPARTS for times when you don’t care about the time element

SELECT DATETIMEFROMPARTS(2015,1,31,9,10,20,0) union
SELECT DATEFROMPARTS(2015,1,31) 

The results

There is also a new TIMEFROMPARTS function for those who do care about the time element, this also allows you to specify the extra sub-millisecond info and the level of precision you want.

SELECT TIMEFROMPARTS(9,10,20,1,4) union
SELECT TIMEFROMPARTS(9,10,20,10,4) union
SELECT TIMEFROMPARTS(9,10,20,100,4) union
SELECT TIMEFROMPARTS(9,10,20,1000,4)