There are many different ways to manipulate dates when working with them in SSIS. Many great examples have been posted across the web and I use many of them on a regular basis in my coding. It can be difficult however to get a single source for many of these since many of us use the ones we need and move on.
This blog entry (and follow ups) will highlight the most useful date strip/manipulation expressions on the web and link back to their creator/publisher.
Let's dive in !
This wiki over at SQLIS.com does a great job of collecting some of these date scripts. There is also an article published by Darren Green with most of the items in the WIKI. The Wiki is here: http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html
My goal in this posting is to highlight some cool expressions and encourage you to get engaged and post some of your own.
Some of the coolest and most useful expressions on the WIKI include:
Removing Time from Date:
This is very useful especially when data is coming from non Microsoft systems and has full datetime values everywhere. You may need to do lookups in your ETL against a date dimension and will need to perform this task routinely.
Two good approaches are shown below:
(DT_DATE)(DT_DBDATE)@[User::WorkingDate]
OR
DATEADD("day",DATEDIFF("day",(DT_DBTIMESTAMP)0,GETDATE()),(DT_DBTIMESTAMP)0)
Calculating the beginning and end of the previous month:
Beginning of the Month:
This expression moves three months back from today, subtracting the day count from the current day of month to get the first day of the month. Then it converts the expression to a DT_DBDATE type and then back to a DT_Date to reset the time to 00:00AM.
(DT_DATE)(DT_DBDATE)DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-3,GETDATE()))
End of the Month:
This employs similar logic in reverse, going back to the last day of the preceding month, and then does some data conversions to truncate the time to 00:00AM.
DATEADD("mi",-1,(DT_DATE)(DT_DBDATE) DATEADD("dd",0,DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-2,GETDATE()))))
If you have an expression (date or otherwise) that makes your life better or development faster, please post it here or email me and I will get it posted with your credits ! 🙂
Don't forget to post your thoughts or email me your questions to ajorgensen@pragmaticworks.com. As always, this Blog is to help you better understand the tools at your disposal ...
