x

Adjust date to day-before if it is a holiday

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.

more ▼

asked Oct 26, 2009 at 02:56 AM in Default

Rameesh gravatar image

Rameesh
1 1 1 1

I appreciate that English may not be your first language, but questions here are answered for free by generous people, please take the trouble to spell-check your text and compose a meaningful "question" and choose appropriate tags, otherwise you are abusing the service people provide here for your benefit
Nov 09, 2009 at 04:09 PM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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)
more ▼

answered Oct 26, 2009 at 04:18 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(comments are locked)
10|1200 characters needed characters left

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')
more ▼

answered Feb 03, 2010 at 07:10 AM

Manjunathns Sampangi gravatar image

Manjunathns Sampangi
11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x94
x10

asked: Oct 26, 2009 at 02:56 AM

Seen: 999 times

Last Updated: Nov 09, 2009 at 04:07 PM