Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Date Expression Compilation in SSIS

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

Comments

Posted by John.E.McGuire on 10 September 2009

Hi...

I know I'll regret this, but...

Why not just a straight forward double convert:

CONVERT(DATETIME, CONVERT(VARCHAR, @date, 101))

Jack McGuire

:=)

Senior Software Developer

Systems Engineering & Advanced Technology Division

Science Applications International Corporation

SAIC - "From Science to Solutions" ®

227 Franklin St., Suite 200

Johnstown, PA 15901-1916

Offic: (814) 535-3499 ext. 151

Fax: (814) 535-3496

John.E.McGuire@SAIC.com

Posted by Ryan VB on 10 September 2009

There's many different ways to work with dates in SQL and here's another example of finding the first day of the month for any # of months ago.

-- This finds the FIRST DAY OF THE MONTH FROM SIX MONTHS AGO

Select   DateAdd(Month, -6, GetDate()) - DatePart(Day, DateAdd(Month, -6, GetDate())) As SQL_Date

-- Example: 2009-03-01 08:31:41.873

-- You can use the code in the article above to remove the time if needed be.

-- If you want the last day of the month, remove the + 1 at the end of the code.

Posted by casinc815 on 13 September 2009

Have you expanded on this theme to include Last Day, First Day?

Jim O'Toole

Leave a Comment

Please register or log in to leave a comment.