If date is a Friday? - 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

General SQL Server Forums

New to SQL Server Programming

If date is a Friday?

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2011-11-11 : 14:45:23

Is there any way to figure out if a date is any particular day? That is to say I'm looking to analyze data based on date. If a purchase is made on date X, then X + 1 day is allowed to introduce new information. However, that excludes Saturday or Sunday! So if a row is introduced on:

11/06/11 (a Sunday) - 1 day is allowed - Completed on a Monday
11/07/11 (a Monday) - 1 day is allowed
11/08/11 (a Tuesday) - 1 day is allowed
11/09/11 (a Wednesday) - 1 day is allowed
11/10/11 (a Thursday) - 1 day is allowed
11/11/11 (a Friday) - 3 days are allowed - Completed on a Monday
11/12/11 (a Saturday) - 2 days are allowed - Completed on a Monday

And my table would need to look like this

Date	Time_Allowed
11/6/2011	1
11/7/2011	1
11/8/2011	1
11/9/2011	1
11/10/2011	1
11/11/2011	3
11/12/2011	2

Is there a comparison function that allows me to check the date for each day?

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-11-11 : 15:11:37

Peso has function to identify weekday vs weekend:

http://weblogs.sqlteam.com/peterl/archive/2009/01/14/Easy-script-for-calculating-weekday-or-weekend_for_a_date.aspx

SergioM
Posting Yak Master

170 Posts

Posted - 2011-11-17 : 18:14:53

Hey, that's a cool script, but it doesn't help me. Friday gets 3 days, Saturday 2 days and Sunday gets 1 day. So even if I were to group them into weekend vs weekday, I would still need to treat them differently.

It's the right idea, but is there something similar?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-11-17 : 18:35:45

[code]DECLARE @Sample TABLE
(
theDate DATE NOT NULL,
TimeAllowed TINYINT NOT NULL
)

INSERT @Sample
(
theDate,
TimeAllowed
)
VALUES ('20111106', 1),
('20111107', 1),
('20111108', 1),
('20111109', 1),
('20111110', 1),
('20111111', 3),
('20111112', 2)

SELECT theDate,
DATENAME(WEEKDAY, theDate) AS theWeekday,
TimeAllowed,
CASE
WHEN DATEDIFF(DAY, '19000101', theDate) % 7 < 4 THEN 1
ELSE 7 - DATEDIFF(DAY, '19000101', theDate) % 7
END AS SwePeso
FROM @Sample
ORDER BY theDate[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources