I use DATEDIFF function to filter records added this week only:
DATEDIFF(week, DateCreated, GETDATE()) = 0
and I noticed what it's assumed what week starts on Sunday. But in my case I would prefer to set start of week on Monday. Is it possible somehow in T-SQL?
Thanks!
Update:
Below is an example showing what DATEDIFF doesn't check @@DATEFIRST variable so I need another solution.
SET DATEFIRST 1;
SELECT
DateCreated,
DATEDIFF(week, DateCreated, CAST('20090725' AS DATETIME)) AS D25,
DATEDIFF(week, DateCreated, CAST('20090726' AS DATETIME)) AS D26
FROM
(
SELECT CAST('20090724' AS DATETIME) AS DateCreated
UNION
SELECT CAST('20090725' AS DATETIME) AS DateCreated
) AS T
Output:
DateCreated D25 D26
----------------------- ----------- -----------
2009-07-24 00:00:00.000 0 1
2009-07-25 00:00:00.000 0 1
(2 row(s) affected)
26 Jul 2009 is Sunday, and I want DATEDIFF returns 0 in third column too.
In SQL Server, there is a @@DATEFIRST function, which returns the current week start day (value of SET DATEFIRST). To change default week start day, we can set any week start day value between 1-7 to DATEFIRST. @@DATEFIRST is local to the session.
Option 2: Monday as the First Day of the WeekSELECT DATEADD(week, DATEDIFF(week, 0, RegistrationDate - 1), 0) AS Monday; In the expression above, we add the specified number of weeks to the 0 date. As you remember, 0 represents midnight on Monday, 1 January 1900.
DATEDIFF returns the total number of the specified unit between startDate and endDate.
This function accepts three parameters namely interval, first value of date, and second value of date.
Yes it possible
SET DATEFIRST 1; -- Monday
from http://msdn.microsoft.com/en-us/library/ms181598.aspx
It appears datediff doesn't respect the Datefirst, so make it do so run it like this
create table #testDates (id int identity(1,1), dateAdded datetime)
insert into #testDates values ('2009-07-09 15:41:39.510') -- thu
insert into #testDates values ('2009-07-06 15:41:39.510') -- mon
insert into #testDates values ('2009-07-05 15:41:39.510') -- sun
insert into #testDates values ('2009-07-04 15:41:39.510') -- sat
SET DATEFIRST 7 -- Sunday (Default
select * from #testdates where datediff(ww, DATEADD(dd,-@@datefirst,dateadded), DATEADD(dd,-@@datefirst,getdate())) = 0
SET DATEFIRST 1 -- Monday
select * from #testdates where datediff(ww, DATEADD(dd,-@@datefirst,dateadded), DATEADD(dd,-@@datefirst,getdate())) = 0
Stolen from
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8cc3493a-7ae5-4759-ab2a-e7683165320b
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With