Adjust date to day-before if it is a holiday

Question

We're having with one holiday schedule table. We have to pass a date parameter, if the date matches with holiday schedule the date should display previous date, if that also matches then date should be previous of previous date. Please find below example

If "Today" is a holiday date then display yesterday, if yesterday was also a holiday then previous date should be displayed and so on.

Answer 1

Hi

I think this query will solve your problem!!!!!

CREATE FUNCTION fx_Display131            
(@datetodisplay DATETIME)            
RETURNS TABLE            
AS            
RETURN(            
            
    SELECT max(HolidayDate) as 'PreviousDate'            
    FROM 	HolidayDate            
    WHERE 	HolidayDate<            
    		(SELECT HolidayDate            
    		FROM 	HolidayDate            
    		WHERE 	HolidayDate=@datetodisplay AND HolidayyesNo='Holiday')AND            
    		HolidayyesNo='Workingday'            
            
    		)            
            
            
            
            
            
            
            
            
GO            
SELECT * from fx_Display131('2010-02-07')

Answer 2

0

Without seeing the table structure, it is difficult to give the solution My guess is

select date_col from your_table            
where date_col=(select max(date_col) from holiday_table where date_col=@date_parameter)

answered Oct 26 '09 at 04:18 AM

Madhivanan
1.1k ● 1 ● 2 ● 6

(comments are locked)

Adjust date to day-before if it is a holiday

Follow this question

Related Questions