Discussion:
SET DATEFIRST 1 in a View
(too old to reply)
Pasha
2005-11-22 20:06:03 UTC
Permalink
Hello All,

I have multiple VIEWS based on a calendar table in my database. All of them
require the weeks to start on Sunday (Default SQL Server). However, one view
(only) that I created requires Mon-Sun week. SET DATEFIRST 1 is not allowed
in Views nor Functions. Is there any workaround?

Here is my view (very simple):
SELECT DISTINCT
[DATE],
[Date] - (DATEPART(DW, [Date]) - 1) as Mon, -- supposed to return Monday

[Date] + (7 - DATEPART(DW, [Date])) as Sun -- supposed to return Sunday

from dbo.CALENDAR
David Portas
2005-11-22 20:19:09 UTC
Permalink
Post by Pasha
Hello All,
I have multiple VIEWS based on a calendar table in my database. All of them
require the weeks to start on Sunday (Default SQL Server). However, one view
(only) that I created requires Mon-Sun week. SET DATEFIRST 1 is not allowed
in Views nor Functions. Is there any workaround?
SELECT DISTINCT
[DATE],
[Date] - (DATEPART(DW, [Date]) - 1) as Mon, -- supposed to return Monday
[Date] + (7 - DATEPART(DW, [Date])) as Sun -- supposed to return Sunday
from dbo.CALENDAR
I try to avoid any reliance on DATEFIRST. Use expressions which are
invariant to that setting. For example:

-- Equivalent to DATEPART(DW) with DATEFIRST 1
SELECT DATEDIFF(DAY,'20000103',CURRENT_TIMESTAMP)%7+1

-- Equivalent to DATEPART(DW) with DATEFIRST 7
SELECT DATEDIFF(DAY,'20000102',CURRENT_TIMESTAMP)%7+1

-
David Portas
SQL Server MVP
--
Pasha
2005-11-22 20:36:05 UTC
Permalink
Thanks. I started playing around with those expressions, but did not
succeed.
Post by David Portas
Post by Pasha
Hello All,
I have multiple VIEWS based on a calendar table in my database. All of them
require the weeks to start on Sunday (Default SQL Server). However, one view
(only) that I created requires Mon-Sun week. SET DATEFIRST 1 is not allowed
in Views nor Functions. Is there any workaround?
SELECT DISTINCT
[DATE],
[Date] - (DATEPART(DW, [Date]) - 1) as Mon, -- supposed to return Monday
[Date] + (7 - DATEPART(DW, [Date])) as Sun -- supposed to return Sunday
from dbo.CALENDAR
I try to avoid any reliance on DATEFIRST. Use expressions which are
-- Equivalent to DATEPART(DW) with DATEFIRST 1
SELECT DATEDIFF(DAY,'20000103',CURRENT_TIMESTAMP)%7+1
-- Equivalent to DATEPART(DW) with DATEFIRST 7
SELECT DATEDIFF(DAY,'20000102',CURRENT_TIMESTAMP)%7+1
-
David Portas
SQL Server MVP
--
Pasha
2005-11-22 20:47:03 UTC
Permalink
Am I doing something wrong?
Post by Pasha
Thanks. I started playing around with those expressions, but did not
succeed.
Post by David Portas
Post by Pasha
Hello All,
I have multiple VIEWS based on a calendar table in my database. All of them
require the weeks to start on Sunday (Default SQL Server). However, one view
(only) that I created requires Mon-Sun week. SET DATEFIRST 1 is not allowed
in Views nor Functions. Is there any workaround?
SELECT DISTINCT
[DATE],
[Date] - (DATEPART(DW, [Date]) - 1) as Mon, -- supposed to return Monday
[Date] + (7 - DATEPART(DW, [Date])) as Sun -- supposed to return Sunday
from dbo.CALENDAR
I try to avoid any reliance on DATEFIRST. Use expressions which are
-- Equivalent to DATEPART(DW) with DATEFIRST 1
SELECT DATEDIFF(DAY,'20000103',CURRENT_TIMESTAMP)%7+1
-- Equivalent to DATEPART(DW) with DATEFIRST 7
SELECT DATEDIFF(DAY,'20000102',CURRENT_TIMESTAMP)%7+1
-
David Portas
SQL Server MVP
--
Aaron Bertrand [SQL Server MVP]
2005-11-22 21:52:11 UTC
Permalink
Who knows? What do "playing around with" and "did not succeed" mean?
http://www.aspfaq.com/5006
Post by Pasha
Am I doing something wrong?
Post by Pasha
Thanks. I started playing around with those expressions, but did not
succeed.
Aaron Bertrand [SQL Server MVP]
2005-11-22 21:52:11 UTC
Permalink
Who knows? What do "playing around with" and "did not succeed" mean?
http://www.aspfaq.com/5006
Post by Pasha
Am I doing something wrong?
Post by Pasha
Thanks. I started playing around with those expressions, but did not
succeed.
Trey Walpole
2005-11-22 21:06:05 UTC
Permalink
I like to use this to find the first of any date part:
DATEADD(<part>, DATEDIFF(<part>, 0, <from date>), 0) -- 0 = 1900-01-01
e.g.
DATEADD(month, DATEDIFF(month, 0, GetDate()), 0) = 2005-11-01

If you plug in any DATEPART part, this works for the beginning of that
part. If you use week - it always returns Monday, regardless of
DATEFIRST, since it's using invariant math [as opposed to day of week].
Caveat: If [Date] is a Sunday it will always return the next day for
Monday. e.g., on 2005-11-20, it will return 2005-11-21. But since it's
consistent, it can be accounted for if that's not the desired result.
Post by Pasha
Hello All,
I have multiple VIEWS based on a calendar table in my database. All of them
require the weeks to start on Sunday (Default SQL Server). However, one view
(only) that I created requires Mon-Sun week. SET DATEFIRST 1 is not allowed
in Views nor Functions. Is there any workaround?
SELECT DISTINCT
[DATE],
[Date] - (DATEPART(DW, [Date]) - 1) as Mon, -- supposed to return Monday
[Date] + (7 - DATEPART(DW, [Date])) as Sun -- supposed to return Sunday
from dbo.CALENDAR
Pasha
2005-11-22 21:01:18 UTC
Permalink
I think this worked:

[Date]-DATEDIFF(DAY,'20000103',[Date])%7,
[Date]-DATEDIFF(DAY,'20000103',[Date])%7+6

Thanks!
Post by Pasha
Hello All,
I have multiple VIEWS based on a calendar table in my database. All of them
require the weeks to start on Sunday (Default SQL Server). However, one view
(only) that I created requires Mon-Sun week. SET DATEFIRST 1 is not allowed
in Views nor Functions. Is there any workaround?
SELECT DISTINCT
[DATE],
[Date] - (DATEPART(DW, [Date]) - 1) as Mon, -- supposed to return Monday
[Date] + (7 - DATEPART(DW, [Date])) as Sun -- supposed to return Sunday
from dbo.CALENDAR
Loading...