CODEWITHSUNDEEP
sqldate-formatleap-year
TYEKHAN
TYEKHAN

Reputation: 11

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.

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

Upvotes: 1

Views: 1703

Answers (1)

Luuk
Luuk

Reputation: 14939

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)

Upvotes: 0

Related Questions