-1

When I run the below code it thinks there are 29 days in Feb, how do I check for leap year. 

MyDate between (DateAdd(day,1,EOMONTH(Getdate(),-1))) AND GETDATE()
Abdul Rasheed
  • 6,486
  • 4
  • 32
  • 48
David Carreon Sr.
  • 21
  • 2
  • For what year? What is `GETDATE()` returning? – tadman Mar 02 '17 at 04:44
  • 2017 and getdate is returning 3/1/2017 – David Carreon Sr. Mar 02 '17 at 04:46
  • The `EOMONTH` function should return Feb 28th, 2017 for that value if you're calling it correctly. `SELECT EOMONTH(GETDATE(), -1)` returns `2017-02-28`. What's with the `DateAdd` part? – tadman Mar 02 '17 at 04:52
  • 1
    possible duplicate of http://stackoverflow.com/questions/6534788/check-for-leap-year – sumit Mar 02 '17 at 04:55
  • Does `SELECT EOMONTH('03-01-2017', -1)` return `2017-02-29`?? Are you sure the issue is not in how you are using the `between`? – Andrew Mar 02 '17 at 05:03
  • When I use the DateAdd it get a running total for each day in the month – David Carreon Sr. Mar 02 '17 at 05:04
  • Without using the DateAdd and just the code you gave me I only get data for 2/28/2017 and not 2/1/2017 through 2/28/2017 – David Carreon Sr. Mar 02 '17 at 05:06
  • My code wasn't supposed to replace yours, I was just trying to find why you think your SQL Server thinks Feb 2017 has 29 days (which is false). If you clearly state what you really want, we may give you a better way to achieve that. You code seems to try to check that `MyDate` is a date of the current month in a very complex manner. – Andrew Mar 02 '17 at 05:07
  • Yes, it returns 2017-02-29 – David Carreon Sr. Mar 02 '17 at 05:16
  • I'm trying to retrieve the data for the current month. When I run the report tomorrow morning, I should only see data for 3/1/2017. When I run the report on Friday, I should only see data for 3/1/2017 and 3/2/2017. What's happening today is that my code thinks there are 29 days in Feb so I don't get any data back. – David Carreon Sr. Mar 02 '17 at 05:20
  • I **HIGHLY** doubt SQL Server would create a datetime for 2017-02-29. Everybody would know of such an horrendous issue by now. Anyway, in order to do that validation, I would just do `MyDate > DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)` (I don't think there would be a date greater than *now*). But if you insist to do that validation as well, just use `between` like in your code. – Andrew Mar 02 '17 at 05:31

1 Answers1

0

Use below query to check leap year :

 DECLARE @YEAR INT = 2019
 SELECT CASE WHEN (@YEAR % 4 = 0 AND @YEAR % 100 <> 0) OR @YEAR % 400 = 0 THEN   
 'LEAP YEAR' ELSE 'NORMAL YEAR' END
Mansoor
  • 4,061
  • 1
  • 17
  • 27