No. Of Days in month - SQL Server Forums

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

Transact-SQL (2000)

No. Of Days in month

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-08-24 : 05:21:16

Dear all,

How can I get the total number of days in a month..?

Thanks and regards
krishnakumar.C

vishnu.cm
Starting Member

7 Posts

Posted - 2006-08-24 : 05:32:59

select datediff(DD,'02-01-2006','03-01-2006') // this calculates the no:of days in feb , llly you can find for any given month , (Note that the date format here is mm-dd-yyyy)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-24 : 05:47:17

declare @MonthNumber int
select @MonthNumber = 3 -- we're looking for march
select day(dateadd(m, 1, convert(datetime, '2006' + right('0'+convert(varchar(2), @MonthNumber), 2) + '01'))-1) as numOfDaysInMonth

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-08-24 : 06:16:36

Thanks Guys

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 06:38:24

Or, given the date only

DECLARE @dt DATETIME

SELECT @dt = '20040215'

SELECT DATEDIFF(day, DATEADD(month, DATEDIFF(month, 0, @dt), 0), DATEADD(month, 1 + DATEDIFF(month, 0, @dt), 0))

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-24 : 06:45:57

declare @mth datetime
select @mth = '20060904'

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

CODO ERGO SUM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-24 : 06:48:25

or just

DECLARE @dt DATETIME
SELECT @dt = '20040215'
select day(dateadd(m, 1, @dt) - day(@dt))

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-24 : 07:07:32

quote:
Originally posted by spirit1

or just

DECLARE @dt DATETIME
SELECT @dt = '20040215'
select day(dateadd(m, 1, @dt) - day(@dt))

Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

Doesn't work with this date; it gives 29.


declare @dt datetime
select @dt = '20040131'
select day(dateadd(m, 1, @dt) - day(@dt))

CODO ERGO SUM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-24 : 07:11:33

thanx for pointing it out Michael.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

sql_lover
Starting Member

14 Posts

Posted - 2007-03-21 : 03:54:46

give a try to this

declare @date as smalldatetime
SET @date=getdate() -- Any Date of a month for which days to be found
select datediff(day
,cast(month(@date) as varchar(20))+'/1/'+cast(year(@date) as varchar(20))+' '
,cast(month(@date)+1 as varchar(20))+'/1/'+cast(year(@date) as varchar(20))+' ')

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-21 : 08:17:34

Stick with the DateAdd and DateDiff functions, avoid concatenations and formatting. They are easier to follow, don't rely on date formats, and are more efficient.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 08:59:33

I vote for MVJ's suggestion

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

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources