Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 get no of days in month

Author  Topic 

chahat_mca
Starting Member

1 Post

Posted - 2006-06-23 : 00:58:57
Hello to all

i am facing a problem. i want to fetch the no of days in month if i pass the month as interger.pls help me

chahat

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2006-06-23 : 01:11:38
Google it out and the first link that you get is this:
http://www.sql-server-helper.com/functions/get-days-in-month.aspx


Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-23 : 02:49:06
You will also need to define the year.

declare @year	int,
@month int

select @year = 2006,
@month = 6
select datediff(day,
dateadd(day, 0, dateadd(month, ((@year - 1900) * 12) + @month - 1, 0)),
dateadd(day, 0, dateadd(month, ((@year - 1900) * 12) + @month, 0))
)



KH

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-23 : 10:27:24
Easier to just start with a date.

declare @dt datetime
set @dt = getdate()

select
DT = @dt,
[Days in Month] =day(dateadd(mm,datediff(mm,-1,@dt),-1))

Results:

DT Days in Month
------------------------------------------------------ -------------
2006-06-23 10:24:23.347 30

(1 row(s) affected)


CODO ERGO SUM
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-23 : 11:05:48
quote:
Originally posted by Michael Valentine Jones

Easier to just start with a date.

declare @dt datetime
set @dt = getdate()

select
DT = @dt,
[Days in Month] =day(dateadd(mm,datediff(mm,-1,@dt),-1))

Results:

DT Days in Month
------------------------------------------------------ -------------
2006-06-23 10:24:23.347 30

(1 row(s) affected)


CODO ERGO SUM



That's just excellent !


KH

Go to Top of Page

shah134pk
Starting Member

1 Post

Posted - 2012-04-24 : 01:13:41
This way is very short and best to get DayOfMonth. if u like then plz tell me.

DECLARE @SystemDate DateTime, @StartDate DateTime, @EndDate DateTime
SET @SystemDate = '26-Apr-2012'
SELECT @StartDate = DATEADD(dd, -Day(@SystemDate) + 1, @SystemDate)
SELECT @EndDate = CONVERT(VARCHAR(20), DATEADD(dd, -(DAY(DATEADD(mm, 1, @SystemDate))),DATEADD(mm, 1, @SystemDate)),101)
-–SELECT @StartDate StartDate, @EndDate EndDate
SELECT DateDiff(WeekDay,@StartDate,@EndDate) + 1 AS DayOfMonth

from shah…
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-24 : 06:07:45
quote:
Originally posted by khtan

You will also need to define the year.

declare @year	int,
@month int

select @year = 2006,
@month = 6
select datediff(day,
dateadd(day, 0, dateadd(month, ((@year - 1900) * 12) + @month - 1, 0)),
dateadd(day, 0, dateadd(month, ((@year - 1900) * 12) + @month, 0))
)



KH




This is enough

declare @year int,
@month int

select @year = 2006,
@month = 6

select day(dateadd(day, 0, dateadd(month, ((@year - 1900) * 12) + @month, 0))-1)


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -