

Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 19, 2016 10:17 PM
Points: 1,398,
Visits: 3,056


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.




Forum 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,'20090901');
set @dt_date_prev = @dt_date1;




SSCEnthusiastic
Group: General Forum Members
Last Login: Friday, January 15, 2016 5:19 AM
Points: 156,
Visits: 603


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




SSCEnthusiastic
Group: General Forum Members
Last Login: Friday, January 15, 2016 5:19 AM
Points: 156,
Visits: 603


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/datecalculationsmadeeasy.aspx
Let me know if it has still not resolved your problem.
Divya




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




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




Forum 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 TSQL Date functions and their SSIS equivalents.




Forum 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




Ten Centuries
Group: General Forum Members
Last Login: Thursday, October 8, 2015 11:32 PM
Points: 1,131,
Visits: 1,395


Nice article Divya
Thanks




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



