SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding the Correct Weekday Regardless of DateFirst


Finding the Correct Weekday Regardless of DateFirst

Author
Message
john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3630 Visits: 3059
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
l.ronnoco
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 41
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
Divya Agrawal
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 604
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
Divya Agrawal
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 604
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
danajao
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 28
Thanks John ... I'll try out as suggested and see how it goes ...
danajao
danajao
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 28
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
danajao
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 28
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
Andy Tynan
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 8
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
Hardy21
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2750 Visits: 1399
Nice article Divya

Thanks
rramsey
rramsey
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search