Question 3....

i did some browsing on the net for how to do this k so im not that good... but i do understand it now....this is a way of finding the last day of a month by:

1 - taking the date input "(@pInputDate DATETIME)"
2 - declare a date variable which you will use to output "SET @vOutputDate DATETIME"
3 - cast the year and month of the inputdate into varchar and then + /01, then assign it as the new @vOutputDate....(in other words if ur input was 23/09/2009, it will now be 01/09/2009 which is the FIRST day of the month)
4 - now add 1 month then minus 1 day (this will make it LAST day of the month)
SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))
5 - now return the output
6 - now thank louie for pasting the whole thing so u can paste it aswell LOL


ALTER FUNCTION dbo.fnGetLastDayOfMonth (@pInputDate DATETIME)
RETURNS DATETIME
BEGIN

DECLARE @vOutputDate DATETIME

SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01'
SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))

RETURN @vOutputDate

END


now this is the query for 2nd part of question 3....cant b bothered explaining it ...u figure it out!

SELECT FirstName + LastName AS Expr1, dbo.fnGetLastDayOfMonth(GETDATE()) AS thismonthpayday, dbo.fnGetLastDayOfMonth(DATEADD(mm, - 1, GETDATE()))
AS lastmonthpayday, dbo.fnGetLastDayOfMonth(DATEADD(mm, 1, GETDATE())) AS nextmonthpayday
FROM Employees