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.
Author |
Topic |
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2014-08-01 : 03:38:42
|
HiFollowing up from a previous post where I wanted to determine the date of the last Saturday with respect to the current date so that I could create a temp table with date ranges numbered 1-4 e.g. up to that date, post link below:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=195279I now want to make this function that any time I pass it a day name (mon-sun) it will calculate the date of the previous (mon-sun) with respect to the current day. This date will then be the last date in my range.So for example I run the function and pass it 'Friday' then I want last date in my range to be the 25th of July (today being Friday so need last Friday), I pass it Thursday I want it to give me the 31st of July etc. I need last date of that date but if I call Friday and today is Friday then need last friday; I think this is the trickier part of it.This calculation I would image would need to use a combination of datediff dateadd etc. but really don't know how to do this dynamically.Currently I have a function that I pass the last date for the date range I want for my weeks range, I also give it the numbere of weeks I want in my range and also pass the date from the query that I want to know what week range it is in. ALTER FUNCTION [dbo].[daterangeweeknumber]( -- Add the parameters for the function here @refDate datetime, @Weeks INT, @lastdate datetime )RETURNS INTASBEGIN -- Run procedure to populate table -- Declare the return variable here DECLARE @weeknumber INT DECLARE @mytemptablevariable table (weeknumber INT,fromdate DATETIME,todate DATETIME) DECLARE @i INT DECLARE @weekfromdatedays INT DECLARE @weektodatedays INT set @i = 1 set @weekfromdatedays = -(@Weeks * 7) set @weektodatedays = -((@Weeks * 7)-7) while (@i <= @Weeks) begin INSERT INTO @mytemptablevariable values (@i, DATEADD(DAY, @weekfromdatedays, DATEADD(DAY, 1, @lastdate)), DATEADD(DAY, @weektodatedays, @lastdate)) set @i = @i+1 set @weekfromdatedays = @weekfromdatedays + 7 set @weektodatedays = @weektodatedays + 7 end select @weeknumber = weeknumber from @mytemptablevariable where @refdate >= fromdate AND @refdate <= todate -- Return the result of the function RETURN @weeknumber END If I call the function with date range of 5 and 30/07/14 (UK date format) as last date:It will give this data set:weeknumber fromdate todate1 2014-06-26 2014-07-02 2 2014-07-03 2014-07-09 3 2014-07-10 2014-07-16 4 2014-07-17 2014-07-23 5 2014-07-24 2014-07-30 <<< Last day 30th of July But now I want just to be able to pass it the day name - in this instance would be 'Wednesday' and want it to return date ranges with last date as Wed the 30th of July. Reason I want to use day name is that people will be running these reports on a certain day - for instance on Wednesday and last day to be Monday - and this will mean they do not have to keep changing the date in the call to function.Hope someone can help with this.Many thanksG |
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-01 : 10:37:39
|
does this help?Set datefirst 7declare @daystoints table (d char(9), i int)insert into @daystoints (d, i) values ('Sunday', 1), ('Monday', 2), ('Tuesday', 3), ('Wednesday', 4), ('Thursday', 5), ('Friday', 6), ('Saturday', 7)declare @day char(9) = 'Wednesday'declare @date date = convert(date, '01/08/14',3)declare @dayint int = (select i from @daystoints where d = @day)declare @daystosubtract int = (select case when @day = DATENAME(weekday, @date) then -7 else @dayint-6 end)select dateadd(day, @daystosubtract, @date) |
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2014-08-01 : 14:23:53
|
DECLARE @dt datetime = '8/6/2014'SELECT DATEADD(dd,(-1*CASE WHEN DatePart(DW,@dt) <= DatePart(DW,DATEADD(dd,-1,DATEADD(mm,1,DATEADD(DD ,- 1*datepart(DD,@dt)+1,@dt))) ) THEN -1*(DATEPART(DW,DATEADD(dd,-1,DATEADD(mm,1,DATEADD(DD ,- 1*datepart(DD,@dt)+1,@dt))))- DatePart(DW,@dt)) ELSE 7+ (DATEPART(DW,DATEADD(dd,-1,DATEADD(mm,1,DATEADD(DD ,- 1*datepart(DD,@dt)+1,@dt)))) - DatePart(DW,@dt) ) END ),DATEADD(dd,-1,DATEADD(mm,1,DATEADD(DD ,- 1*datepart(DD,@dt)+1,@dt))) )This could probably be simplified |
|
|
|
|
|
|
|