DATETIMEFROMPARTS Function: Build Dates From Parts

The DATETIMEFROMPARTS function builds a datetime value from seven numbers. It takes the year, month, day, hour, minute, second and millisecond, and it needs no string. That removes a whole class of date bugs.

Build a Value From Seven Numbers

The call is DATETIMEFROMPARTS(year, month, day, hour, minute, seconds, milliseconds). Every argument is a whole number, and the result is a datetime. The function needs SQL Server 2012 or later. This is the simplest call.

SELECT DATETIMEFROMPARTS(2019, 2, 3, 11, 12, 13, 557) AS BuiltValue;

The value reads as February 3, 2019, at 11:12:13 and 557 milliseconds. A common habit is to build such a value from text instead. Text depends on the language of the session, and the function doesn’t.

Pass whole numbers. A fraction in the millisecond slot is cut off, and a number written as text is converted. Both work, and both hide mistakes.

SELECT DATETIMEFROMPARTS(2019, 2, 3, 11, 12, 13, 557.9) AS DecimalMilliseconds,
       DATETIMEFROMPARTS(N'2019', N'2', 3, 11, 12, 13, 0) AS NumbersAsText;

Keep the arguments as integers, so a wrong value fails or shows up where you can see it.

Datetime Rounds the Milliseconds

A datetime stores time in steps of about three milliseconds. The last digit lands on .000, .003 or .007. The function follows that rule, so the milliseconds you pass in are not always the ones you get back.

SELECT DATETIMEFROMPARTS(2019, 2, 3, 11, 12, 13, 555) AS Ms555,
       DATETIMEFROMPARTS(2019, 2, 3, 11, 12, 13, 556) AS Ms556,
       DATETIMEFROMPARTS(2019, 2, 3, 11, 12, 13, 558) AS Ms558,
       DATETIMEFROMPARTS(2019, 2, 3, 11, 12, 13, 559) AS Ms559;

Three inputs give .557, and 559 gives .560. The rounding can even change the date. On the last second of a day, 997 stays put. The value 999 rounds up to midnight of the next day.

SELECT DATETIMEFROMPARTS(2019, 2, 3, 23, 59, 59, 997) AS Ms997, DATETIMEFROMPARTS(2019, 2, 3, 23, 59, 59, 999) AS Ms999;

A day filter that ends at 23:59:59.999 therefore catches rows stamped at midnight of the next day too. Use a half open range instead. Filter for the start of the day or later, and before the start of the next day.

Invalid Parts Raise Msg 289

The function checks every argument. A day that doesn’t exist in that month stops the statement with message 289.

SELECT DATETIMEFROMPARTS(2019, 2, 30, 11, 12, 13, 0) AS BadDay;

Msg 289, Level 16, State 3, Line 1
Cannot construct data type datetime, some of the arguments have values which are not valid.

The same message appears for month 13, year 1752, hour 24 and milliseconds 1000. A datetime can’t hold a year before 1753. Each statement below runs in its own batch, so all four fail in turn.

SELECT DATETIMEFROMPARTS(2019, 13, 3, 11, 12, 13, 0) AS BadMonth;
GO
SELECT DATETIMEFROMPARTS(1752, 12, 31, 0, 0, 0, 0) AS BadYear;
GO
SELECT DATETIMEFROMPARTS(2019, 2, 3, 24, 0, 0, 0) AS BadHour;
GO
SELECT DATETIMEFROMPARTS(2019, 2, 3, 23, 59, 59, 1000) AS BadMilliseconds;

That is a feature. The function fails at once, with the same message in every language. A date written as text can be read as another date, as the German example below shows.

NULL Gives NULL

If any argument is NULL, the result is NULL, and no error is raised. That matters when the parts come from table columns.

SELECT DATETIMEFROMPARTS(2019, 2, 3, 11, 12, 13, NULL) AS WithNull;

Test the parts before you build. Supply a default only when a default is correct. A NULL date in a report is easier to find than a wrong date.

Why Not Convert a String?

A string needs the right language to be read right. The next script sets the session language to German and builds the same moment three ways. SQL Server answers with a status message in German. At the end the script sets the language to us_english again. The setting lasts for the session only.

SET LANGUAGE German;
SELECT DATETIMEFROMPARTS(2019, 2, 3, 11, 12, 13, 0) AS FromParts,
       CAST('2019-02-03 11:12:13' AS datetime) AS CastWithDashes,
       CAST('20190203' AS datetime) AS CastPlain;
SET LANGUAGE us_english;

Under German, the dashed text was read as year, day and month, and it became March 2. The plain eight digit text was safe, and the function was safe. Build from parts, or use the plain format with no separators.

The Other FROMPARTS Functions

The DATETIMEFROMPARTS function has five relatives. They cover the other date and time types, take the same kind of numbers and return a typed value. One query builds all five.

SELECT DATEFROMPARTS(2019, 2, 3) AS D,
       TIMEFROMPARTS(11, 12, 13, 5, 1) AS T,
       SMALLDATETIMEFROMPARTS(2019, 2, 3, 11, 12) AS SDT,
       DATETIME2FROMPARTS(2019, 2, 3, 11, 12, 13, 5570000, 7) AS DT2,
       DATETIMEOFFSETFROMPARTS(2019, 2, 3, 11, 12, 13, 0, -5, 0, 0) AS DTO;

The next table names each one.

In the time and datetime2 versions, the fraction is a count of units. The last argument is the precision. A fraction of 5 with precision 1 means half a second. The same fraction with precision 3 means 5 milliseconds. Pick the precision first, then the fraction.

Month math is a good use of the date version. The first day of a month is DATEFROMPARTS(YEAR(d), MONTH(d), 1). For the last day, EOMONTH(d) is shorter. To step to the next month, let DATEADD roll the year, because month 13 raises an error.

SELECT DATEFROMPARTS(YEAR(d.Today), MONTH(d.Today), 1) AS FirstOfMonth, EOMONTH(d.Today) AS LastOfMonth
FROM (SELECT CAST('2019-02-14' AS date) AS Today) AS d;
GO
SELECT DATEADD(MONTH, 1, DATEFROMPARTS(2019, 12, 1)) AS NextMonthStart;

You could argue that a string is shorter to type. It is. A literal such as 20190203 is fine for a fixed date in a script. The DATETIMEFROMPARTS function wins with variables or columns. No string has to be built and read back.

What to Remember

The DATETIMEFROMPARTS function builds a datetime from numbers, so language settings can’t change the result. Remember the datetime step of about three milliseconds, and never end a day filter at 23:59:59.999. A NULL part gives NULL, and an invalid part gives Msg 289.

For finer time, use DATETIME2FROMPARTS and choose the precision. For a date alone, use DATEFROMPARTS. Pass the parts, not a string.

A date is not a string, it is a set of numbers that agree with a calendar.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.