Finding the Correct Weekday Regardless of DateFirst

• john.arnott

  SSChampion

  Points: 11882

  More actions

  February 22, 2010 at 10:02 am

  #1123101

  l.ronnoco (2/20/2010)

  ---

  SELECT 1 + (DATEDIFF(DAY, 0, @target_date)+700000) % 7

  The 700000 can be any number divisible by 7. This will work all the way back to before the year 0 so should be fine for any non-biblical applications 🙂

  This probably does not work with SQL 2008 (I don't have that version available here), but it certainly does not on SQL 2005. Those still on earlier versions will have to remember that datetime only supports values since 1753, so you can't even set target_date to an earlier value. Even if @target_date can be set to something earlier in SQL 2008, the eleven day adjustment in 1752 would mess up the modulo 7 algorithm.

  (Why was there no September 3-13 in 1752? here's an explanation: http://www.ancestry.com/learn/library/article.aspx?article=3358 )

• jamesburkill

  SSC Rookie

  Points: 39

  More actions

  February 23, 2010 at 2:23 am

  #1123488

  Ok. You've won me over.

  I just did A quick test on the efficiency of each method.

  Using the script below, my method took 2:20 to 2:30 to execute. Yours consistently took 1:20, though I did have to run it 100 million times to get those numbers.

  Declare @i int

  Declare @day int

  set @i=0

  While @i<=100000000

  Begin

  Set @Day=((DatePart(dw,'2009-12-17') + @@DateFirst -2)%7)+1

  set @i=@i+1

  End

  Declare @i int

  Declare @day int

  set @i=0

  While @i<=100000000

  Begin

  Set @Day=1 + (DATEDIFF(DAY, 0, '2009-12-17')+700000) % 7

  set @i=@i+1

  End

• Divya Agrawal

  Hall of Fame

  Points: 3144

  More actions

  February 23, 2010 at 4:13 am

  #1123523

  jamesburkill (2/23/2010)

  ---

  Ok. You've won me over.

  I just did A quick test on the efficiency of each method.

  Using the script below, my method took 2:20 to 2:30 to execute. Yours consistently took 1:20, though I did have to run it 100 million times to get those numbers.

  Declare @i int

  Declare @day int

  set @i=0

  While @i<=100000000

  Begin

  Set @Day=((DatePart(dw,'2009-12-17') + @@DateFirst -2)%7)+1

  set @i=@i+1

  End

  Declare @i int

  Declare @day int

  set @i=0

  While @i<=100000000

  Begin

  Set @Day=1 + (DATEDIFF(DAY, 0, '2009-12-17')+700000) % 7

  set @i=@i+1

  End

  Well, thanks @jamesburkill

  Datediff will take time as compared to getting datepart..

  --Divya

• bbothma

  Newbie

  Points: 9

  More actions

  March 5, 2010 at 2:22 am

  #1129040

  Hi - thanks

  This is a great solution. I made a function that can handle this

  😎

• danajao

  Valued Member

  Points: 58

  More actions

  September 7, 2010 at 5:39 pm

  #1217683

  Thanks for a very interesting tip ... problem I have though is while my expression provides me with a previous date ... it fails when moving into another month for example on September 1, 2010 I would expect the expression to evaluate to August 31, 2010, instead it evaluates to September 30, 2010 ... it handles the date difference within a given month correctly but not when the current month goes into the 2nd month ... just cant figure what the problem is... I'd appreciate your showing me where I'm going wrong ... Thanks.

  (DT_DATE) MONTH( DATEADD("mm", 0, GETDATE()) )> (DT_DATE) MONTH( DATEADD("mm", -1, GETDATE()) ) ?

  "X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", 0, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv" :

  "X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", -1, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv"

• john.arnott

  SSChampion

  Points: 11882

  More actions

  September 7, 2010 at 6:22 pm

  #1217685

  danajao (9/7/2010)

  ---

  Thanks for a very interesting tip ... problem I have though is while my expression provides me with a previous date ... it fails when moving into another month for example on September 1, 2010 I would expect the expression to evaluate to August 31, 2010, instead it evaluates to September 30, 2010 ... it handles the date difference within a given month correctly but not when the current month goes into the 2nd month ... just cant figure what the problem is... I'd appreciate your showing me where I'm going wrong ... Thanks.

  (DT_DATE) MONTH( DATEADD("mm", 0, GETDATE()) )> (DT_DATE) MONTH( DATEADD("mm", -1, GETDATE()) ) ?

  "X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", 0, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv" :

  "X:\\WNTS\\Reporting\\List28old "+ RIGHT( "1"+ (DT_WSTR, 2) MONTH( DATEADD("mm", -1, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv"

  It looks from the code that you may be trying to code an expression for SSIS. If true, you may get more help in the SSIS forum. But meanwhile, at the core of it, finding a previous date can be trivial with the DATEADD function:

  select DATEADD(dd,-1,'20100901')

  returns 8/31/2010

  You want to build the filename from pieces of that common result. Your problem may be that DATEADD("mm",0,GETDATE()) which I highlighted in my quote of your post above. It will alway give you the same month that you start with. Try making it DATEADD("mm",-1,GETDATE()) and let us know if that solves things for you.

• l.ronnoco

  SSC Rookie

  Points: 26

  More actions

  September 8, 2010 at 2:02 am

  #1217767

  Not sure which versions of SQL Server this works in (but it works in 2005).

  declare @dt_date datetime;

  declare @dt_date_prev datetime;

  set @dt_date = convert(datetime,'2009-09-01');

  set @dt_date_prev = @dt_date-1;

• Divya Agrawal

  Hall of Fame

  Points: 3144

  More actions

  September 8, 2010 at 2:40 am

  #1217781

  danajao (9/7/2010)

  ---

  Thanks for a very interesting tip ... problem I have though is while my expression provides me with a previous date ... it fails when moving into another month for example on September 1, 2010 I would expect the expression to evaluate to August 31, 2010, instead it evaluates to September 30, 2010 ... it handles the date difference within a given month correctly but not when the current month goes into the 2nd month ... just cant figure what the problem is... I'd appreciate your showing me where I'm going wrong ... Thanks.

  (DT_DATE) MONTH( DATEADD("mm", 0, GETDATE()) )> (DT_DATE) MONTH( DATEADD("mm", -1, GETDATE()) ) ?

  "X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", 0, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv" :

  "X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", -1, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv"

  Sorry for the empty post..you can find the answer in the post below this..

  --Divya

• Divya Agrawal

  Hall of Fame

  Points: 3144

  More actions

  September 8, 2010 at 2:48 am

  #1217785

  danajao (9/7/2010)

  ---

  Thanks for a very interesting tip ... problem I have though is while my expression provides me with a previous date ... it fails when moving into another month for example on September 1, 2010 I would expect the expression to evaluate to August 31, 2010, instead it evaluates to September 30, 2010 ... it handles the date difference within a given month correctly but not when the current month goes into the 2nd month ... just cant figure what the problem is... I'd appreciate your showing me where I'm going wrong ... Thanks.

  (DT_DATE) MONTH( DATEADD("mm", 0, GETDATE()) )> (DT_DATE) MONTH( DATEADD("mm", -1, GETDATE()) ) ?

  "X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", 0, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv" :

  "X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", -1, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv"

  Thanks for the appreciation of the article. Well, i have made a blog post about the date calculations. Please have a look at it, as i think it can be helpful to you for the problem you have..

  http://beyondrelational.com/blogs/divya/archive/2010/03/15/date-calculations-made-easy.aspx

  Let me know if it has still not resolved your problem.

  --Divya

• danajao

  Valued Member

  Points: 58

  More actions

  September 23, 2010 at 6:46 pm

  #1226655

  Thanks John ... I'll try out as suggested and see how it goes ...

• danajao

  Valued Member

  Points: 58

  More actions

  September 23, 2010 at 6:54 pm

  #1226656

  Thanks everyone for suggestions ... they have given me a couple of ideas as to how to proceed within BIDS SSIS on this issue ... will provide an update once it is resolved.

• danajao

  Valued Member

  Points: 58

  More actions

  September 23, 2010 at 9:48 pm

  #1226684

  Thanks John, I.Ronnoco & last but not least, Divya's website on date calculations in sql for your help in pointing me in the right direction ... for anyone with the same issue, the ssis expression did not require a condition, also the dateparts had to be all the same to return the previous days date no matter if the day had transitioned into another month.

  Corrected SSIS expression is shown below:

  "X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR(DATEADD("dd",-1, GETDATE()) )+".csv"

  Finally thanks to Darren Green's article on SSIS date expressions at sqlis.com for his comparison of T-SQL Date functions and their SSIS equivalents.

• Andy Tynan

  Newbie

  Points: 9

  More actions

  April 28, 2011 at 6:50 am

  #1317213

  Thanks for saving me from writing it from scratch, Divya!

  @davidgr144

  David, I spotted your question too, and modified the query to always give me Mon=1 to Sun=7:

  select 1+(datepart(dw, @date)+@@datefirst+5)%7

  Adding 5 before the MOD effectively cycles the days backwards, then adding 1 afterwards fixes the zero.

  Andy T

• Hardy21

  SSCrazy Eights

  Points: 9708

  More actions

  June 29, 2011 at 12:18 am

  #1345651

  Nice article Divya

  Thanks

• rramsey

  Newbie

  Points: 9

  More actions

  July 29, 2012 at 8:37 am

  #1518934

  One thing I noticed is this: I expected saturday to be returned as day 7 when sunday was day 1 however; the formula, Declare @ThisDay int = (DATEPART(weekday,@ThisDate) + @@DATEFIRST) % 7, always returns 0 for saturday.

  this query will show the results for each day of the week from 7/22/2012 to 7/28/2012.

  Select @@DATEFIRST as [DateFirst], ((DATEPART(weekday,'7/22/2012') + @@DATEFIRST) % 7) as SUN, ((DATEPART(weekday,'7/23/2012') + @@DATEFIRST) % 7) as MON , ((DATEPART(weekday,'7/24/2012') + @@DATEFIRST) % 7) as TUE, ((DATEPART(weekday,'7/25/2012') + @@DATEFIRST) % 7) as WED, ((DATEPART(weekday,'7/26/2012') + @@DATEFIRST) % 7) as THR, ((DATEPART(weekday,'7/27/2012') + @@DATEFIRST) % 7) as FRI, ((DATEPART(weekday,'7/28/2012') + @@DATEFIRST) % 7) as SAT

  Everything works well as long as you expect saturday to be zero.