Finding the Correct Weekday Regardless of DateFirst

  • 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 )

  • 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

  • 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

  • Hi - thanks

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

    😎

  • 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"

  • 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.

  • 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;

  • 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

  • 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

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

  • 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.

  • 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.

  • 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

  • Nice article Divya

    Thanks

  • 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.

Viewing 15 posts - 46 through 60 (of 61 total)

You must be logged in to reply to this topic. Login to reply