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)
 Get Dates from given parameters.

Author  Topic 

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-02-13 : 18:55:19
Good day!

Supposed I had a stored proc that will get the dates of the specified parameters: YEAR, MONTH, Day_To_Get.


Ex…
spJ_GetDates @Year, @Month, @Day_To_Get

exec spJ_GetDates 2005, 02, ‘Saturday’
this will return all saturday's of Feb 2005

result:

02/05/2005
02/12/2005
02/19/2005
02/26/2005

How?


Want Philippines to become 1st World COuntry? Go for World War 3...

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-13 : 19:10:05
select convert(datetime,convert(varchar(4),@Year) + right('00' + convert(varchar(2),@Month), 2) + '01') + ints.i
from
(select i = i1.i + i2.i + i3.i + i4.i + i5.i
from
(select i = 0 union select 1) as i1 ,
(select i = 0 union select 2) as i2 ,
(select i = 0 union select 4) as i3 ,
(select i = 0 union select 8) as i4 ,
(select i = 0 union select 16) as i5) ints
where ints.i <= datepart(dd,dateadd(dd,-1,dateadd(mm,1,convert(datetime,convert(varchar(4),@Year) + right('00' + convert(varchar(2),@Month), 2) + '01'))))
and datename(dw,convert(datetime,convert(varchar(4),@Year) + right('00' + convert(varchar(2),@Month), 2) + '01') + ints.i) = @Day_To_Get



==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-13 : 20:20:17
Nigel, that is WAY cool! (especially for less than 15 minutes from the request)
Here is a totaly different approach:

set nocount on
declare @FDOM dateTime
,@dt datetime

declare @dates table ([dates] datetime)

--FirstDayOfMonth
Set @FDOM = convert(datetime, convert(varchar(2),@month) + '/1/' + convert(varchar(4), @Year))

--first occurence of @Day_To_Get
Select @dt =
case
when datename(weekday,@FDOM) = @Day_to_get then @FDOM
when datename(weekday,dateAdd(day,1,@FDOM)) = @Day_to_get then dateAdd(day, 1, @FDOM)
when datename(weekday,dateAdd(day,2,@FDOM)) = @Day_to_get then dateAdd(day, 2, @FDOM)
when datename(weekday,dateAdd(day,3,@FDOM)) = @Day_to_get then dateAdd(day, 3, @FDOM)
when datename(weekday,dateAdd(day,4,@FDOM)) = @Day_to_get then dateAdd(day, 4, @FDOM)
when datename(weekday,dateAdd(day,5,@FDOM)) = @Day_to_get then dateAdd(day, 5, @FDOM)
when datename(weekday,dateAdd(day,6,@FDOM)) = @Day_to_get then dateAdd(day, 6, @FDOM)
end

While Datepart(month, @dt) = @month
Begin
insert @dates values (@dt)
Select @dt = dateadd(day,7,@dt)
End

Select * from @dates


Be One with the Optimizer
TG
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-13 : 20:36:53
Simpler version of my earlier post
select dte.d + ints.i
from
(select i = i1.i + i2.i + i3.i + i4.i + i5.i
from
(select i = 0 union select 1) as i1 ,
(select i = 0 union select 2) as i2 ,
(select i = 0 union select 4) as i3 ,
(select i = 0 union select 8) as i4 ,
(select i = 0 union select 16) as i5) ints
cross join
(select d = convert(datetime,convert(varchar(4),@Year) + right('00' + convert(varchar(2),@Month), 2) + '01')) dte
where datepart(mm,dte.d) = datepart(mm,dte.d + ints.i)
and datename(dw, dte.d + ints.i) = @Day_To_Get

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-02-15 : 22:09:37
Tnx a lot NR and TG!

How about doing it by dateRange?

Ex…
spJ_GetDates @from, @To, @Day_To_Get

exec spJ_GetDates '02/01/2005', '02/12/2005', ‘Saturday’
this will return Feb 5 and 12, 2005

result:

02/05/2005
02/12/2005


How?


I've learned i lot in here. tnx a lot guys....

Want Philippines to become 1st World COuntry? Go for World War 3...
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-15 : 22:45:11
That change makes my version even a little simpler:

set nocount on
declare @dt datetime

declare @dates table ([dates] datetime)


--first occurence of @Day_To_Get
Select @dt =
case
when datename(weekday,@from) = @Day_to_get then @from
when datename(weekday,dateAdd(day,1,@from)) = @Day_to_get then dateAdd(day, 1, @from)
when datename(weekday,dateAdd(day,2,@from)) = @Day_to_get then dateAdd(day, 2, @from)
when datename(weekday,dateAdd(day,3,@from)) = @Day_to_get then dateAdd(day, 3, @from)
when datename(weekday,dateAdd(day,4,@from)) = @Day_to_get then dateAdd(day, 4, @from)
when datename(weekday,dateAdd(day,5,@from)) = @Day_to_get then dateAdd(day, 5, @from)
when datename(weekday,dateAdd(day,6,@from)) = @Day_to_get then dateAdd(day, 6, @from)
end

While @dt <= @to
Begin
insert @dates values (@dt)
Select @dt = dateadd(day,7,@dt)
End

Select * from @dates


Be One with the Optimizer
TG
Go to Top of Page

jhermiz

3564 Posts

Posted - 2005-02-15 : 22:55:37
quote:
Originally posted by nr

select convert(datetime,convert(varchar(4),@Year) + right('00' + convert(varchar(2),@Month), 2) + '01') + ints.i
from
(select i = i1.i + i2.i + i3.i + i4.i + i5.i
from
(select i = 0 union select 1) as i1 ,
(select i = 0 union select 2) as i2 ,
(select i = 0 union select 4) as i3 ,
(select i = 0 union select 8) as i4 ,
(select i = 0 union select 16) as i5) ints
where ints.i <= datepart(dd,dateadd(dd,-1,dateadd(mm,1,convert(datetime,convert(varchar(4),@Year) + right('00' + convert(varchar(2),@Month), 2) + '01'))))
and datename(dw,convert(datetime,convert(varchar(4),@Year) + right('00' + convert(varchar(2),@Month), 2) + '01') + ints.i) = @Day_To_Get



==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.



Err nr, that was slick...you're an SQL Wizard


Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection --
[url]http://jhermiz.blogspot.com/[/url]
Go to Top of Page
   

- Advertisement -