Finding last day date code - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Finding last day date code

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2014-08-01 : 03:38:42

Hi

Following 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=195279

I 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 INT
AS
BEGIN
    -- 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	todate
1	        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 thanks

G

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-01 : 10:37:39

does this help?


Set datefirst 7
declare @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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources