Days in a month

Question

Post reply

Days in a month

Subbu S

Old Hand

Points: 389
More actions
November 24, 2010 at 4:02 am

#104006

I need to find the no.of days in each and every month.
If i give @startdate = '15 - jan - 2010'
@enddate = '16-march-2010)
Then
Output should be Jan - 31, Feb - 28, March - 31
Total : 90days

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

DECLARE @startdate DATETIME, @enddate DATETIME

SELECT @startdate = '15 - dec - 2009', @enddate = '16-march-2010'

--Output should be Jan - 31, Feb - 28, March - 31

SELECT MonthEndDate = DATEADD(dd, -1, DATEADD(month, (n.n-1) + DATEDIFF(month, 0, @startdate)+1,0))

FROM (SELECT TOP 100 n = ROW_NUMBER() OVER (ORDER BY NAME) FROM master.dbo.syscolumns) n

WHERE n <= (DATEDIFF(month, @startdate, @enddate) + 1)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Subbu S Old Hand Points: 389 More actions · Answer 2

Hi ,

Can u explain the logic behind that. I acheieved the same the same tink using another query to. But i am eager to know ur logic.

First u r filling the table with 100 rows using sys objects.

Then u r selecting N rows from the table (Where N stands for Duration between first and last month)

Then u r selecting the N value one by one.

Here is my work out

DECLARE @startdate DATETIME, @enddate DATETIME

SELECT @startdate = '15 - dec - 2009', @enddate = '16-march-2010'

Select * from (Select top 100 Row_number() over (order by name) as subbu from master.dbo.syscolumns) n where subbu<= datediff(month,@startdate,@enddate)+1

Select DATEDIFF(month, 0, @startdate)+1 /***********/-- Retunrning 1319

select DATEADD(month, (0) + 1320,0) -- 2010-01-01 00:00:00.000 its findinfg the 1st date of jan and YOU R REDUCING ONE DAY which yields DEC 31

Select Dateadd (dd,-1,'2010-01-01 00:00:00.000')

I need to know hw v r getting 1319 ?

Plz explain me

WayneS SSC Guru Points: 95460 More actions · Answer 3

You need to evaluate from the inside out.

SELECT MonthEndDate = DATEADD(dd, -1, DATEADD(month, (n.n-1) + DATEDIFF(month, 0, @startdate)+1,0))

FROM (SELECT TOP 100 n = ROW_NUMBER() OVER (ORDER BY NAME) FROM master.dbo.syscolumns) n

WHERE n <= (DATEDIFF(month, @startdate, @enddate) + 1)

1. For the first 100 rows, get a sequential number (1-100). (this is the subquery aliased as "n")

2. For each of the sequential numbers that are <= the # of months difference between the two specified dates:

2.a. Get the first day of the month after (@startdate plus the (sequential number minus 1)). - (DATEADD(month, (n.n-1) + DATEDIFF(month, 0, @startdate)+1,0)))

2.b. Subtract one day from the above date - this will be the last day of the previous month. (DATEADD(dd, -1, AboveDate))

Does this explain it to where you understand it?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Subbu S Old Hand Points: 389 More actions · Answer 4

Hi WayneS ,

Please read my complete post and reply back.I am asking how Chris Morris got 1319 in that datediff function, other than that u hav explained me.

Already i had posted the logic behind tat, but i am wonddering how he got 1319 ?

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

subbusa2050 (11/24/2010)
Hi WayneS ,
Please read my complete post and reply back. I am asking how Chris Morris got 1319 in that datediff function, other than that u hav explained me.
Already i had posted the logic behind tat, but i am wonddering how he got 1319 ?

Typing in bold doesn't add any significance to your post, Subbusa. It is considered really rude to say the least and will discourage people from replying further. Wayne was very polite in his reply and there was no need for you to reply in such a rude manner.

To answer your question, the number 0 stands for a default date, 01-January-1900 in SQL server.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Subbu S Old Hand Points: 389 More actions · Answer 6

Hi Kingston Dhasian,

Ya ofcourse i agree with you, But i was just telling and it was not in a rude manner. If he read my last post , he may hav some clues what i need.

Any way thanks for your answer 🙂 🙂

And also iam sorry Wayne

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 7

Glad you took my message in the right spirit and I hope my answer clarified your doubt. 🙂

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/