how to find last day of the month for a year in sq

# Thread: how to find last day of the month for a year in sq

1. Senior Member
Join Date
Dec 1969
Posts
168

## how to find last day of the month for a year in sq

how to find last day of the month for a year in sql server ie last date of each month for 12 months. lets say i want to find last date of all the 12 months for the year 2002.i should get the answer like for jan ---31st<BR> feb ---28th<BR> march --31st and so on for one year.<BR><BR>

2. Senior Member
Join Date
Dec 1969
Posts
404

## RE: how to find last day of the month for a year i

Why make this tricky?<BR><BR>You know the last day of the month for every month except Feb.<BR><BR>Feb=28<BR><BR>The rules for leap years are<BR>if year evenly divisible by 4; Feb = 29<BR><BR>EXCEPT when year evenly divisible by 100<BR>THEN year must also be evenly divisible by 400<BR><BR>i.e 2000 -- Feb = 29<BR>2100 -- Feb = 28

3. Senior Member
Join Date
Dec 1969
Posts
7,686

## OR

CALL ReturnLastDays(2002) <BR><BR>FUNCTION ReturnLastDays(intYear)<BR> FOR intMonth = 1 to 12<BR> Response.Write MONTHNAME(intMonth) & ": " & Day( DateSerial( intYear, (intMonth+1), 0 ) ) & "<BR>"<BR> NEXT<BR>END FUNCTION

4. Senior Member
Join Date
Dec 1969
Posts
404

## RE: OR

Cool,<BR><BR>so the 2ndargument is saying - go to the next month<BR>& 3rd arg says - give date b4 1st Date?

5. Senior Member
Join Date
Dec 1969
Posts
7,686

## something like that

DateSerial( 2002, 1, 1 ) returns 1/1/2002, but<BR><BR>DateSerial (2002, 1, 0 ) returns 12/31/2001<BR><BR>So if you set the Date parameter of the Dateserial function to 0, it returns the last day of the previous month. So in the function add one to the month (to start with January) and that&#039;s all.<BR><BR>The DAY function returns only the daynumber from the supplied date.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•