XXXX to year for calculation

[ewithoud]

Hi Guys,

this is probably an easy one, but I'm not sure how to quickly add in the information without having to create a table to hold the data.

In the system I'm working with people are able to enter Holidays in a text field.

Either 01/01/2011 for a specific date.
or 01/01/XXXX if the date recurs every year.

When I calculate the time between two dates to check if any of the dates are a holiday I would basically do a count and check for the dates using the following view:

Create view [dbo].[ViewBusinessHolidays] as
(select dtdate from(
SELECT Convert (datetime,stringvalue, 103) as dtdate
         
  FROM tblTabletoCheck

where Code = 'Holiday' ) as a

where Datename (dw,dtdate) not in ('Saturday', 'Sunday')
)
GO

but as 'XXXX' is not a date this would not work. How would I substitute XXXX so that I would still be able to calculate a time difference between let's say 23-12-2004 to 25-5-2011 (the date duration can overlap multiple years).


Many Thanks,

Erik

Implementation Consultant at

http://www.clickhome.com.au

 

[Qik3Coder]

You'd have to create a table variable.
Load it with all the years that are in the span of dates that they passed in (This will be a small while loop and then a cross join)

Then join this data with the "fixed" holidays.

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]