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

avatar 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

avatar image

Madhivanan
1.1k 2 5 9

(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

avatar 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.

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:

x82
x8

asked: Oct 26, 2009 at 02:56 AM

Seen: 1187 times

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

Copyright 2016 Redgate Software. Privacy Policy