determine weekday - 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

Transact-SQL (2000)

determine weekday

Author

Topic

rohcky
Starting Member

38 Posts

Posted - 2006-05-08 : 15:50:33

Does anyone know how to determine if a date falls on a weekday (preferrably similar to the Weekday function in Access)? I'm converting some old access macros to a DTS and encountered this problem.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-08 : 16:20:59

[code]

select
[IS_WEEKDAY] =
case when datediff(dd,-53690,a.DT)%7 < 5
then 1 else 0 end,
[IS_WEEKDAY 2] =
-- A little harder to understand, but shorter
sign(sign((-datediff(dd,-53690,a.DT)%7)+4)+1),
WEEKDAY = datename(dw,A.DT),
DT
from
(
select
DT = aa.DT-92533
from
(
select DT = getdate() union all
select DT = getdate()+1 union all
select DT = getdate()+2 union all
select DT = getdate()+3 union all
select DT = getdate()+4 union all
select DT = getdate()+5 union all
select DT = getdate()+6 union all
select DT = getdate()+7
) aa ) a

[/code]
Results:
[code]
IS_WEEKDAY IS_WEEKDAY 2 WEEKDAY DT
----------- ------------ ------------------------------ ------------------------
1 1 Monday 1753-01-01 17:44:54.150
1 1 Tuesday 1753-01-02 17:44:54.150
1 1 Wednesday 1753-01-03 17:44:54.150
1 1 Thursday 1753-01-04 17:44:54.150
1 1 Friday 1753-01-05 17:44:54.150
0 0 Saturday 1753-01-06 17:44:54.150
0 0 Sunday 1753-01-07 17:44:54.150
1 1 Monday 1753-01-08 17:44:54.150

(8 row(s) affected)

[/code]

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-08 : 18:21:09

Michael, can you explain what the point of the math calculations is?

Here is what I came up with:

SELECT IsWeekDay =
CASE
WHEN DATEPART(dw, GETDATE()) BETWEEN 2 AND 6 THEN 'True'
ELSE 'False'
END

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-08 : 19:16:25

quote:
Originally posted by tkizer

Michael, can you explain what the point of the math calculations is?

Here is what I came up with:

SELECT IsWeekDay =
CASE
WHEN DATEPART(dw, GETDATE()) BETWEEN 2 AND 6 THEN 'True'
ELSE 'False'
END

Tara Kizer
aka tduggan

I usually do not like to provide solutions that depend on the setting of DATEFIRST, especially on a forum where I do not know the posters default DATEFIRST setting, and they may not know the importance of it.

For example, using your method:


set datefirst 3

SELECT
	IsWeekDay = 
	CASE
	WHEN DATEPART(dw, a.DT) BETWEEN 2 AND 6 THEN 1
	ELSE 0
	END,
	[IS_WEEKDAY] =
	-- A little harder to understand, but shorter
	sign(sign((-datediff(dd,-53690,a.DT)%7)+4)+1),
	WEEKDAY = left(datename(dw,A.DT),9),
	DT =convert(varchar(23),DT,121)
from
	(
	select
		DT = aa.DT-92533
	from
	(
	select DT = getdate() union all
	select DT = getdate()+1 union all
	select DT = getdate()+2 union all
	select DT = getdate()+3 union all
	select DT = getdate()+4 union all
	select DT = getdate()+5 union all
	select DT = getdate()+6 union all
	select DT = getdate()+7
	) aa ) a

Results:


IsWeekDay   IS_WEEKDAY  WEEKDAY   DT                      
----------- ----------- --------- ----------------------- 
1           1           Monday    1753-01-01 19:11:18.010
0           1           Tuesday   1753-01-02 19:11:18.010
0           1           Wednesday 1753-01-03 19:11:18.010
1           1           Thursday  1753-01-04 19:11:18.010
1           1           Friday    1753-01-05 19:11:18.010
1           0           Saturday  1753-01-06 19:11:18.010
1           0           Sunday    1753-01-07 19:11:18.010
1           1           Monday    1753-01-08 19:11:18.010

(8 row(s) affected)

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-08 : 19:23:05

rohcky,

Depending on the value of DATEFIRST, 2 and 6 in my code may need to change.

Tara Kizer
aka tduggan

rohcky
Starting Member

38 Posts

Posted - 2006-05-09 : 09:15:27

Thanks for the help all. Worked like a charm.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources