Days of the month using SQL
To find the days in a month using SQL, use the following method. Here TSQL syntax is used for example.
--------------------
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'
--------------------
for more sql tricks goto.. Extremeexperts
6 comments:
should rather point to something open source like postgresql
Very useful Tips.. great..! I like that.
Thanks
This totally saved me time. Thank you so much.
SELECT DAY(DATEADD(d,-1,dateadd(m,1,@Date))) AS 'Last day of the month'
Create FUNCTION [dbo].[ufn_GetLastDayOfMonth]
(
@pInputDate DATETIME
)
RETURNS DATETIME
BEGIN
DECLARE @vOutputDate DATETIME
SET @vOutputDate = CAST(FLOOR(CAST(@pInputDate AS DECIMAL(12, 5))) -
(DAY(@pInputDate) - 1) AS DATETIME)
SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))
RETURN @vOutputDate
END
Post a Comment