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_Getexec spJ_GetDates 2005, 02, ‘Saturday’this will return all saturday's of Feb 2005result:02/05/200502/12/200502/19/200502/26/2005How?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.ifrom (select i = i1.i + i2.i + i3.i + i4.i + i5.ifrom(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) intswhere 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. |
|
|
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 ondeclare @FDOM dateTime ,@dt datetimedeclare @dates table ([dates] datetime)--FirstDayOfMonthSet @FDOM = convert(datetime, convert(varchar(2),@month) + '/1/' + convert(varchar(4), @Year))--first occurence of @Day_To_GetSelect @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) = @monthBegin insert @dates values (@dt) Select @dt = dateadd(day,7,@dt)EndSelect * from @dates Be One with the OptimizerTG |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-13 : 20:36:53
|
Simpler version of my earlier postselect dte.d + ints.ifrom (select i = i1.i + i2.i + i3.i + i4.i + i5.ifrom(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) intscross join(select d = convert(datetime,convert(varchar(4),@Year) + right('00' + convert(varchar(2),@Month), 2) + '01')) dtewhere 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. |
|
|
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_Getexec spJ_GetDates '02/01/2005', '02/12/2005', ‘Saturday’this will return Feb 5 and 12, 2005result:02/05/200502/12/2005How?I've learned i lot in here. tnx a lot guys....Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
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 ondeclare @dt datetimedeclare @dates table ([dates] datetime)--first occurence of @Day_To_GetSelect @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 <= @toBegin insert @dates values (@dt) Select @dt = dateadd(day,7,@dt)EndSelect * from @dates Be One with the OptimizerTG |
|
|
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.ifrom (select i = i1.i + i2.i + i3.i + i4.i + i5.ifrom(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) intswhere 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] |
|
|
|
|
|