Dynamic date on Leap Year

[benchpolo]

I'm trying to write a code that will pick-up the end of the previous month, but I encountered a road block when the month such as February is a leap year. 2011 has 28 days and 2012 has 29 days. Trying to figure out how to incorporate leap year in the code below:

DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, GETDATE())+1, 0))

Thanks.

[Rabbit]

DateAdd(day, -1, CONVERT(VARCHAR(2), Month(GETDATE())) + '/01/' + CONVERT(VARCHAR(4), YEAR(GETDATE())))

[ck9663]

Here is a list of how to find specific dates in SQL Server.

Happy Coding!!!


~~ CK

[PsychoCoder]

If you're trying to determine if it's a leap year then you can use a user defined function. Here's an example on finding if the provided date is a leap year

CREATE FUNCTION dbo.udfIsLeapYear(@date DATETIME)
RETURNS BIT
AS
BEGIN
 
    IF (YEAR(@date) % 4 = 0 AND YEAR(@date) % 100 != 0) OR
        YEAR(@date) % 400 = 0
        RETURN 1
 
    RETURN 0
 
END
GO

[ck9663]

Here's a simpler one using a built-in-function.


Happy Coding!!!

~~ CK

[PsychoCoder]

@ck9663 I don't see how that is simpler than the UDF I provided?

[ck9663]

I guess it depends on how you look at it.

IsDate is a sql-server date function that returns 1 if the expression you are testing is a valid date and 0 if it is not. No need to build a function.

To illustrate:

 
select 
IsValidDate_20120229 = isdate('02/29/2012'), 
IsValidDate_20130229 = isdate('02/29/2013')
 
 

and you will have this result

IsValidDate_20120229 IsValidDate_20130229 
-------------------  --------------------
1                    0
 

I just think using a built-in function is simpler than creating a new one with case statements.


Happy Coding!!!


~~ CK