I’m using DATEFROMPARTS to construct a date using a year and month stored in one table, and a day of the month in another. But I get this error where the day of the month is invalid for that month:
SELECT DATEFROMPARTS(2024, 4, 31);
Cannot construct data type date, some of the arguments have values which are not valid.
e.g.
SELECT DATEFROMPARTS(a.Year, a.Month, b.Day)
FROM TableA a
JOIN TableB b ON a.FK_b = b.Id
Simple solution: crop the Day parameter to be no higher than 28:
SELECT DATEFROMPARTS(a.Year, a.Month, MIN(b.Day, 28))...
Better solution: roll over into the next month, e.g. “April, 31” becomes “May, 01”:
SELECT DATEADD(DAY, b.Day - 1, DATEFROMPARTS(a.Year, a.Month, 1))...
How could I clamp the Day parameter to be “Day X, or the last day of the month, whichever is later” e.g. 2024, 4, 31 becomes 2024-04-30?
10
I suggest JOINing to your Calendar table, as that will have dates in their constitute parts in columns. As, however, you have invalid dates then you can use 2 JOINs to get the date for the 1st of the month, should the 1st JOIN fail, and then add a month to it in the SELECT.
Something like this:
SELECT ISNULL(RD.CalendarDate,DATEADD(MONTH, 1, FD.CalendarDate))
FROM dbo.YourTable YT
LEFT JOIN dbo.Calendar RD ON YT.Year = RD.CalenderYear
AND YT.Month = RD.CalendarMonth
AND YT.Day = RD.CalendarDay
LEFT JOIN dbo.Calendar FD ON YT.Year = FD.CalenderYear
AND FT.Month = FD.CalendarMonth
AND FT.CalendarDay = 1
Of course, if you have months like 13 or 0 or years like 20224 you’re in for only more “hurt”; fixing the design and/or (at least) adding a CHECK CONSTRAINT so that invalid dates can’t be added would be a must then.
Better solution: roll over into the next month, e.g. “April, 31” becomes “May, 01”
Well, if you are okay with rolling over, you can roll over the month too:
select
dateadd(
d,
b.Day - 1,
dateadd(
year,
(a.Month - 1) / 12,
datefromparts(a.Year, (a.Month - 1) % 12 + 1, 1)
)
)
https://dbfiddle.uk/lvHHUEm-
(2000, 42, 42) => 2003-07-12.