Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»

Finding the Correct Weekday Regardless of DateFirst Expand / Collapse
Author
Message
Posted Tuesday, September 7, 2010 6:22 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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.
Post #981995
Posted Wednesday, September 8, 2010 2:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:30 AM
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;
Post #982101
Posted Wednesday, September 8, 2010 2:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 5, 2014 1:48 AM
Points: 143, Visits: 551
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
Post #982117
Posted Wednesday, September 8, 2010 2:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 5, 2014 1:48 AM
Points: 143, Visits: 551
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
Post #982122
Posted Thursday, September 23, 2010 6:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 4, 2011 4:02 PM
Points: 4, Visits: 28
Thanks John ... I'll try out as suggested and see how it goes ...
Post #992419
Posted Thursday, September 23, 2010 6:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 4, 2011 4:02 PM
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.
Post #992420
Posted Thursday, September 23, 2010 9:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 4, 2011 4:02 PM
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.



Post #992455
Posted Thursday, April 28, 2011 6:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 28, 2012 9:36 AM
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
Post #1100184
Posted Wednesday, June 29, 2011 12:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 4:43 AM
Points: 1,130, Visits: 1,391
Nice article Divya

Thanks
Post #1133421
Posted Sunday, July 29, 2012 8:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 20, 2012 3:27 AM
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.
Post #1336996
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse