'How to fix leap year Date

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
sqldate-formatleap-year


Solution 1:[1]

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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Luuk

Related Questions