Date Query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Date Query

Author

Topic

Leigh79
Starting Member

28 Posts

Posted - 2010-12-12 : 06:45:58

Hi guys

I'm wondering if this is possible.

In my events table I have the following columns: Start Date, End Date and 7 columns for the days of the week which are set as INT with the value of 0 or 1 if true.

If there is a single entry in the table with a start date of the 18th Nov 2010 and end date of 30th Dec 2010 and the Thursday field is flagged as 1 would it be possible to return a resultset which would display each Thursday between and including the two dates?

Thanks in advance!
Leigh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-12 : 07:00:41

[code]
SELECT DATEADD(DAY, v.number, x.StartDate) AS Thursday
FROM (
SELECT StartDate,
EndDate
FROM dbo.Table1
GROUP BY StartDate,
EndDate
HAVING SUM(CASE WHEN Thursday = 1 THEN 1 ELSE 0 END) = 1
) AS x
INNER JOIN master..spt_values AS v ON v.type = 'P'
AND v.number BETWEEN 0 AND DATEDIFF(DAY, x.StartDate, x.EndDate)
WHERE DATEDIFF(DAY, 0, DATEADD(DAY, v.number, x.StartDate)) % 7 = 3[/code]

N 56°04'39.26"
E 12°55'05.63"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-12 : 07:06:46

SELECT StartDate,Enddate
FROM [Table]
Where (datepart(WEEKDAY,startdate) = 5 or (datepart(WEEKDAY,enddate)) = 5
And StartDate >= '2010118' and EndDate <='20101230'

The above will return each startdate and enddate where the date is a thursday and between the dates you indicated

You don't really need the 7 columns. The date functions allow you to identify the weekday pretty simply.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources