The same day(1,2,3..31) of every month.

Question

Post reply

The same day(1,2,3..31) of every month.

Ashwin M N

Hall of Fame

Points: 3668
More actions
July 26, 2010 at 6:08 am

#223686

I have a requirement to get the same day of every month from the start date till the end of frequency.
There will be no end date only the number of frequency.
Ex:
declare @date datetime,@freq int,@id int
set @date = '2010-01-31'
set @freq = 6
SET @id = 1
while (@id < @freq)
BEGIN
select <statement>
SET @id = @id + 1
END
i>The output for this is:
2010-01-31
2010-02-28
2010-03-31
2010-04-30
2010-05-31
2010-06-30
Since the start date is end date of January,I should get the end date of every month.
ii>If the start date is 2010-01-29 and @freq is 3 then output is
2010-01-29
2010-02-28
2010-03-29
Logic:If the date exists in next month then display the same day of start date of next month,
if does not exists then display the last day of next month till number of freq.
July 26, 2010 at 11:42 pm

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

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

asiaindian SSCrazy Points: 2493 More actions · Answer 1

Add a month to the startdate

declare @StartDate date

set @StartDate = '01-31-2010''

select DATEADD(mm, 1, @StartDate)

Repeat above code through any number of frequencies. Hope this helps.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

try:

declare @date datetime, @freq int

set @date = '2010-01-29'

set @freq = 6

SELECT DATEADD(MONTH, MM, @date) AS ReqDate

FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS MM FROM sys.columns) TallyOnFly

WHERE MM < @freq

You better create real Tally table...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1003933 More actions · Answer 3

asiaindian (7/26/2010)
Add a month to the startdate
declare @StartDate date
set @StartDate = '01-31-2010''
select DATEADD(mm, 1, @StartDate)
Repeat above code through any number of frequencies. Hope this helps.

Ok.... you have 100 frequencies... now what? 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Jeff Moden (7/26/2010)
asiaindian (7/26/2010)
Add a month to the startdate
declare @StartDate date
set @StartDate = '01-31-2010''
select DATEADD(mm, 1, @StartDate)
Repeat above code through any number of frequencies. Hope this helps.
Ok.... you have 100 frequencies... now what? 😉

Auto format using CONCATENATE in Excel... :-D:-D:-D

Don't ask me what if you have more than 64k of frequencies...:w00t:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1003933 More actions · Answer 5

Eugene Elutin (7/26/2010)
Jeff Moden (7/26/2010)
asiaindian (7/26/2010)
Add a month to the startdate
declare @StartDate date
set @StartDate = '01-31-2010''
select DATEADD(mm, 1, @StartDate)
Repeat above code through any number of frequencies. Hope this helps.
Ok.... you have 100 frequencies... now what? 😉
Auto format using CONCATENATE in Excel... :-D:-D:-D
Don't ask me what if you have more than 64k of frequencies...:w00t:

Heh... too funny. 😛 BTW... nice solution you came up with, Eugene.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 6

A calendar table is essential imho

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1003933 More actions · Answer 7

stewartc-708166 (7/26/2010)
consider using a CTE:
declare @Range tinyint, @Date datetime
select @Range = 15, @Date = '2010-01-31';
with mydateRange
as (
select @Date as dateof, row_number() over( order by @Date) num
union all
select DATEADD(mm, num, @Date), num+1
from mydateRange
where num < @range)
select * from mydateRange

Oh... be careful. I actually hope no one considers such a thing. That code uses a recursive CTE. Recursive CTE's are a form of RBAR similar to a While Loop... They're almost as slow as a While Loop and they use about 3 times the number of reads. Recursive CTE's that develop just one row at a time should be avoided just like While Loops and other forms of RBAR should be avoided for reasons of performance and resource usage.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003933 More actions · Answer 8

Dave Ballantyne (7/27/2010)
A calendar table is essential imho
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Definitely one of the more useful aux tables and one of the better links on the subject.

The only problem here is that I don't believe it's quite so easy to solve the problem on this thread using a calendar table for days that are greater than 28.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

If you don't like way posted in Post #958743 (which, as stated could benefit from using permanent tally table) and you don't have Excel to format SQL (as per Post#958732,#958745 & #958786) and you want something more "exotic", then i can offer dynamic sql way ;-):

declare @date datetime, @freq int

set @date = '2010-01-29'

set @freq = 1000

declare @sql nvarchar(max), @i int

select @i = 1, @sql = ''

while @i<=@freq

begin

set @sql = @sql + N'(' + cast(@i as varchar(10)) + N'),'

set @i=@i+1

end

set @sql = N'select dateadd(month, mm, @pdate) from (values ' + LEFT(@sql,len(@sql)-1) + N') a(mm)'

exec sp_executesql @sql, N'@pdate datetime', @pdate=@date

the above is for "SQL p0rno" lovers only! :-D:w00t::-D

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]