Search code examples
sqldatecase

With SQL, how do remove a day from a date value if it falls on a certain day?

I am having an issue removing a day from a column if it falls on a specific day.

How do I remove a day if the date value falls on '2/29/2024'? I need to remove it from ANY year that date occurs so it will be on 2/28.

I tried this and it still leaves the date as 2/29/2024:

   CASE WHEN UOS.ExpirationDate LIKE '2/29%' THEN
   convert(char(10), DATEADD(day, -1, UOS.ExpirationDate), 101) 
   ELSE convert( char(10), UOS.ExpirationDate, 101 ) END as ExpirationDate

When I hardcode it like this, it gives me '2/28/2024':

   CASE WHEN UOS.ExpirationDate = '2/29/2024' THEN
   convert(char(10), DATEADD(day, -1, UOS.ExpirationDate), 101) 
   ELSE convert( char(10), UOS.ExpirationDate, 101 ) END as ExpirationDate

I need for it to subtract a day from 2/29 regardless of the year.

Solution

  • Many SQL implementations have a way to extract specific parts of a date (such as month(date) or day(date) functions).

    Once you figure out what these functions are in your dbms, you could do the following:

    CASE WHEN month(UOS.ExpirationDate) = 2 AND day(UOS.ExpirationDate) = 29 THEN
convert(char(10), DATEADD(day, -1, UOS.ExpirationDate), 101) 
ELSE convert( char(10), UOS.ExpirationDate, 101 ) END as ExpirationDate