July 29, 2011 at 10:09 am
Today I spent an hour puttign together an expression that will return the last day of the previous quarter. It came out to be quite lengthy and I was wondering if there is a betetr way of doing it... It easy to do using T-SQL, but did anyone tried to do this before in SSIS and came up with a prettier result?
(DT_DATE)(DT_DBDATE)DATEADD("mm",((DATEPART("qq",GETDATE())-1)*3)-(MONTH(GETDATE())-1),DATEADD("dd",-DAY(GETDATE()),GETDATE()))
July 29, 2011 at 12:19 pm
Why would you put yourself through that pain? Do you have access to a calendar-table, or can you create one?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 29, 2011 at 12:48 pm
That's a good point. I suppose I could put one together and that thought crossed my mind. In this case I wanted a solution that is independent of any external sources. That said, I will probably go down the calendar table route in the future.
July 29, 2011 at 2:56 pm
I agree a calendar/date table is the way to go.
I haven't tested the efficiency of this, but I suspect it is a bit more efficient than what you have listed above:
DATEADD("dd",-1,DATEADD("qq",DATEDIFF( "qq", (DT_DBTIMESTAMP)"1/1/1900" , GetDate() ),(DT_DBTIMESTAMP)"1/1/1900"))
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply