How to fix leap year Date

Question

I'm having issues with Leap Year what can i do to change it to 28 feb or 1st march

i'm getting the below error,

Msg 289, Level 16, State 1, Line 21 Cannot construct data type date, some of the arguments have values which are not valid. enter code here

SELECT
cast(MEMNO as int) MEMNO,
cast(YEAR(EXITDATE) as int)  
[StartYear],
case WHEN DATEFROMPARTS(cast(YEAR(GETDATE()) as int)+1, 
DATEPART(m, EXITDATE), DATEPART(d, EXITDATE))<=GETDATE() THEN cast(YEAR(GETDATE()) as int)   else cast(YEAR(GETDATE()) as int)  END  [EndYear]
FROM EXITRETIREMENT
group bY
cast(MEMNO as int),
cast(YEAR(EXITDATE) as int),
EXITDATE





 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EXITRETIREMENT](
[MEMNO] [int] NULL,
[EXITDATE] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[EXITRETIREMENT] ([MEMNO], [EXITDATE]) VALUES (45517,CAST(N'2004-02-29T00:00:00.000' AS DateTime))
GO

I've tried your query asumming memmo is smallint and exitdate is a date and its syntax is correct, what actual data type are those fields? — mbd, Mar 17 '22 at 08:26
Your `case` is to produce always the same `cast(YEAR(GETDATE())`. Are you sure? What is the logic you want ? — Serg, Mar 17 '22 at 08:37
Don't increment the YEAR of 2020-02-29, use DATEADD(year, 1, '2020/02/29') — nfgl, Mar 17 '22 at 08:41
See also https://stackoverflow.com/questions/57530882/what-are-some-examples-of-leap-year-bugs — Matt Johnson-Pint, Mar 24 '22 at 17:19

score 0 · Answer 1 · answered Mar 17 '22 at 08:45

Your query seems to be running in MSSQL, because DATEFROMPARTS exists in MSSQL.

The query:

Select
    Cast(MEMNO As int) MEMNO,
    Cast(Year(EXITDATE) As int) StartYear,
    Case
        When DateFromParts(Cast(Year(GetDate()) As int) + 1, DatePart(m, EXITDATE), DatePart(d, EXITDATE)) <= GetDate()
        Then Cast(Year(GetDate()) As int)
        Else Cast(Year(GetDate()) As int)
    End EndYear
From
    EXITRETIREMENT
Group By
    Cast(MEMNO As int),
    Cast(Year(EXITDATE) As int),
    EXITDATE

is building a date: DateFromParts(Cast(Year(GetDate()) As int) + 1, DatePart(m, EXITDATE), DatePart(d, EXITDATE))

With the following parameters:

year from the current year +1
month from EXITDATE
day from EXITDATE

When EXITDATE is 29th February, and next year is not a leap year, than you will indeed have a problem.

Consider rewriting the DATEFROMPARTS to: DATEADD(year,1,EXITDATE)

or, when it needs to be next year:

DATEADD(year,year(GETDATE()-year(EXITDATE)+1,EXITDATE)

How to fix leap year Date

1 Answers1