How to retrieve the
last day of the month .... Or Last date in the week?
Answer:
To add a addendum ... how can I find out the
number of days in a given month.
eg. If I were to give you '2003-03-01' I
should get 31.
If you feel its a bit tricky in nature then
... See the solutions below ...
DECLARE @Date datetime
SET @Date = '2000/02/1'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the week'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the week'
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Last day of the month'
SET @Date = '2000/02/1'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the week'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the week'
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Last day of the month'
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:
DECLARE @d DATETIME
SET @d = '2003-02-1'
select datepart(dd, dateadd(dd, -(datepart(dd, dateadd(mm, 1, @d))),dateadd(mm, 1, @d))) AS 'Last day of the month'
SET @d = '2003-02-1'
select datepart(dd, dateadd(dd, -(datepart(dd, dateadd(mm, 1, @d))),dateadd(mm, 1, @d))) AS 'Last day of the month'
GO
DECLARE @d DATETIME
SET @d = '2000-02-1'
SELECT CASE WHEN MONTH(@d) = 1
THEN 31
WHEN MONTH(@d) = 2
THEN CASE WHEN (YEAR(@d) % 4 = 0 AND YEAR(@d) % 100 <> 0) OR
YEAR(@d) % 400 = 0
THEN 29
ELSE 28
END
WHEN MONTH(@d) = 3
THEN 31
WHEN MONTH(@d) = 4
THEN 30
WHEN MONTH(@d) = 5
THEN 31
WHEN MONTH(@d) = 6
THEN 30
WHEN MONTH(@d) = 7
THEN 31
WHEN MONTH(@d) = 8
THEN 31
WHEN MONTH(@d) = 9
THEN 30
WHEN MONTH(@d) = 10
THEN 31
WHEN MONTH(@d) = 11
THEN 30
WHEN MONTH(@d) = 12
THEN 31
END AS 'Last day of the month'
SET @d = '2000-02-1'
SELECT CASE WHEN MONTH(@d) = 1
THEN 31
WHEN MONTH(@d) = 2
THEN CASE WHEN (YEAR(@d) % 4 = 0 AND YEAR(@d) % 100 <> 0) OR
YEAR(@d) % 400 = 0
THEN 29
ELSE 28
END
WHEN MONTH(@d) = 3
THEN 31
WHEN MONTH(@d) = 4
THEN 30
WHEN MONTH(@d) = 5
THEN 31
WHEN MONTH(@d) = 6
THEN 30
WHEN MONTH(@d) = 7
THEN 31
WHEN MONTH(@d) = 8
THEN 31
WHEN MONTH(@d) = 9
THEN 30
WHEN MONTH(@d) = 10
THEN 31
WHEN MONTH(@d) = 11
THEN 30
WHEN MONTH(@d) = 12
THEN 31
END AS 'Last day of the month'
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 ...
SELECT
DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@d),@d)))) AS
'Last day of the month'
Note: Some
of the tips and tricks are subjected to some specific SQL Server settings hence
use them with loads of care.