Wednesday, April 27, 2005

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:

lawgon said...

should rather point to something open source like postgresql

மடல்காரன் said...

Very useful Tips.. great..! I like that.

Guru Prasath said...

Thanks

Brandon said...

This totally saved me time. Thank you so much.

kjyhf said...

SELECT DAY(DATEADD(d,-1,dateadd(m,1,@Date))) AS 'Last day of the month'

Mr.Viet said...

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