Added line is this color
Deleted line is this color
- -<p><font size="2"><font face="Verdana"><font size="4">How to retrieve the last -day of the month .... Or Last date in the week?</font><br> -Answer:<br> -To add a addendum ... how can I find out the number of days in a given -month.<br> -eg. If I were to give you '2003-03-01' I should get 31.<br> -If you feel its a bit tricky in nature then ... See the solutions below ...<br> -DECLARE @Date datetime<br> -SET @Date = '2000/02/1'<br> -SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the -week'<br> -SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the -week'<br> -SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Last -day of the month'<br> -Since the Last Day would yield you the number of days in a month then it anwers -my addendum question also. Some more solutions for the number of days in a -month are below:<br> -DECLARE @d DATETIME<br> -SET @d = '2003-02-1'<br> -select datepart(dd, dateadd(dd, -(datepart(dd, dateadd(mm, 1, @d))),dateadd(mm, -1, @d))) AS 'Last day of the month'<br> -GO<br> -DECLARE @d DATETIME<br> -SET @d = '2000-02-1'<br> -SELECT CASE WHEN MONTH(@d) = 1<br> - THEN 31<br> - WHEN MONTH(@d) = 2<br> - THEN CASE WHEN (YEAR(@d) % 4 = 0 AND YEAR(@d) % 100 <> 0) -OR<br> - YEAR(@d) % 400 = 0<br> - THEN 29<br> - ELSE 28<br> - END<br> - WHEN MONTH(@d) = 3<br> - THEN 31<br> - WHEN MONTH(@d) = 4<br> - THEN 30<br> - WHEN MONTH(@d) = 5<br> - THEN 31<br> - WHEN MONTH(@d) = 6<br> - THEN 30<br> - WHEN MONTH(@d) = 7<br> - THEN 31<br> - WHEN MONTH(@d) = 8<br> - THEN 31<br> - WHEN MONTH(@d) = 9<br> - THEN 30<br> - WHEN MONTH(@d) = 10<br> - THEN 31<br> - WHEN MONTH(@d) = 11<br> - THEN 30<br> - WHEN MONTH(@d) = 12<br> - THEN 31<br> - END AS 'Last day of the month'<br> -Whew .... That's a mess to understand ... Believe me they work ... :-) ... -There are hundreds of way the SQL Selects can be done ... This is just few of -them ... And one more ...<br> -<br> -SELECT DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@d),@d)))) AS 'Last -day of the month'<br> -<br> -Note: Some of the tips and tricks are subjected to some specific SQL Server -settings hence use them with loads of care.</font></font></p> - +book