Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to set start of week for T-SQL DATEDIFF function?

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.

like image 461
Alexander Prokofyev Avatar asked Jul 09 '09 04:07

Alexander Prokofyev


People also ask

How do I change the start of the week in SQL?

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.

How do I get the start of the week in SQL?

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.

Does datediff include start and end?

DATEDIFF returns the total number of the specified unit between startDate and endDate.

What are the three parameters of datediff function?

This function accepts three parameters namely interval, first value of date, and second value of date.


1 Answers

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

like image 128
Tetraneutron Avatar answered Oct 13 '22 19:10

Tetraneutron