Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Select all months bewtween two dates Expand / Collapse
Author
Message
Posted Wednesday, April 16, 2014 4:32 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:33 AM
Points: 856, Visits: 1,506
feroz_tt (4/16/2014)
Hi All,

I'm getting some problem here

;with Numbers (Number) as
(select row_number() over (order by object_id)
from sys.all_objects)
select dateadd(month, number, '2014-09-01')
from Numbers
where number <= datediff(month, '2014-09-01', '2016-03-31')

Low no of months.

Select datediff(month, '2014-09-01', '2016-03-31')
it should give 31 instead of 18.

I don't understand where i'm wrong


How did you calculate 31?
Post #1562225
Posted Saturday, April 19, 2014 3:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 35,772, Visits: 32,443
feroz_tt (4/16/2014)
Hi All,

I'm getting some problem here

;with Numbers (Number) as
(select row_number() over (order by object_id)
from sys.all_objects)
select dateadd(month, number, '2014-09-01')
from Numbers
where number <= datediff(month, '2014-09-01', '2016-03-31')

Low no of months.

Select datediff(month, '2014-09-01', '2016-03-31')
it should give 31 instead of 18.

I don't understand where i'm wrong


I'm not sure why you think there should be "31". I can understand why someone would insist that the correct answer is "19" but not "31".

with Numbers (Number) as
(select row_number() over (order by object_id) -1
from sys.all_objects)
select Number+1,dateadd(month, number, '2014-09-01')
from Numbers
where number <= datediff(month, '2014-09-01', '2016-03-31')
;


Results:

                     
-------------------- -----------------------
1 2014-09-01 00:00:00.000
2 2014-10-01 00:00:00.000
3 2014-11-01 00:00:00.000
4 2014-12-01 00:00:00.000
5 2015-01-01 00:00:00.000
6 2015-02-01 00:00:00.000
7 2015-03-01 00:00:00.000
8 2015-04-01 00:00:00.000
9 2015-05-01 00:00:00.000
10 2015-06-01 00:00:00.000
11 2015-07-01 00:00:00.000
12 2015-08-01 00:00:00.000
13 2015-09-01 00:00:00.000
14 2015-10-01 00:00:00.000
15 2015-11-01 00:00:00.000
16 2015-12-01 00:00:00.000
17 2016-01-01 00:00:00.000
18 2016-02-01 00:00:00.000
19 2016-03-01 00:00:00.000

(19 row(s) affected)



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1563295
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse