# Thread: calculating number of days from the current month

1. Registered User
Join Date
Sep 2010
Posts
153

## Unanswered: calculating number of days from the current month

Hi guys,

i have a problem. I want to calculate number of days of the current month. if november then i should get 30 and if december it should be 31. Based on days i want to divide it by 7 WHCIH I WILL DO BUT I JUST WANT TO KNOW HOW TO GET NUMBER OF DAYS

Thanks..

2. Registered User
Join Date
Jul 2010
Location
ISHHHH
Posts
130
nice querry

3. Registered User
Join Date
Feb 2004
Posts
492
Perhaps not the most elegant...
Code:
```declare @date varchar(10)
set @date = '2010-11-17'

select
case
when 1 = isdate(convert(varchar, year(@date)) + '-' + convert(varchar, month(@date)) + '-31') then 31
when 1 = isdate(convert(varchar, year(@date)) + '-' + convert(varchar, month(@date)) + '-30') then 30
when 1 = isdate(convert(varchar, year(@date)) + '-' + convert(varchar, month(@date)) + '-28') then 28
end```

4. Registered User
Join Date
Sep 2001
Location
Chicago, Illinois, USA
Posts
603
Here's my version that is a bit more concise, but certainly more obtuse:

Code:
```declare @date date
set @date='2010-08-31'

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
any formula involving strings means conversions, and those are always a ~lot~ slower than simple date arithmetic

Code:
```SELECT DAY( DATEADD(DAY,-1,
start at the inside and work your way out --

take the day of the month for today, and subtract 1 less than that from the current date

that gives you the first of the current month

then add 1 month (1st of next month)

then subtract 1 day (last dat of current month) and take the day

6. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Code:
```DECLARE @d		DATETIME
SET @d = '2000-02-12'

SELECT DateDiff(d, t, n) FROM (SELECT DateAdd(m, DateDiff(m, 0, @d)
, 0) AS t, DateAdd(m, 1 + DateDiff(m, 0, @d), 0) AS n) AS z```
-PatP

7. Registered User
Join Date
Sep 2001
Location
Chicago, Illinois, USA
Posts
603
r937, wow, I really like that improvement--similar to my logic, but without the character conversions. I'm going to make it my standard.

8. Registered User
Join Date
Sep 2010
Posts
153

## thanks guys:) the above queres are working fine:)

.

i tried with another queery and it is also working . Thanks a lot for the solution

thanks

9. Registered User
Join Date
Nov 2010
Posts
1

## thanks

Thanks! I appreciate it.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•