July 22, 2008 at 4:21 pm
I am trying to calculate a date in a derived column. My package can run anytime after a file is created, but I always want to show the effective_date column as the previous Saturday. Is there a way to accomplish this in SSIS? I have seen the DATEADD and DATEPART but haven't found a way to put that together to get the previous Saturday (from the current date). Any ideas or suggestions?
Thanks,
Kim
July 22, 2008 at 6:32 pm
Try this
DATEPART( "dw", GETDATE() ) == 7 ? GETDATE() : DATEADD( "day", -( DATEPART( "dw", GETDATE() ) ), GETDATE() )
The above expression checks if today is Saturday and if true returs today's day else returns the last Saturday's date.
July 23, 2008 at 8:01 am
That worked great.....unfortunately I am being told it needs to show the week beginning date - it always has to show a Monday (even if run on Tuesday or Wednesday). Can this be changed to work for a Monday?
I also found the following:
DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
This works in SQL but is not a valid expression in SSIS. Any ideas?
July 23, 2008 at 10:43 am
Try
DATEPART( "dw", GETDATE() ) == 2 ? GETDATE() : DATEADD( "day", -( DATEPART( "dw", GETDATE() ) -2), GETDATE() )
Just added 2 days to return a monday.
HTH
~Mukti
July 23, 2008 at 10:57 am
Great - thanks for the help. I'll try this.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply