Monday, February 22. 2010

Some useful Date expressions for SSIS

I engaged in creating some date expressions yesterday, these expressions are very common needed. So I bring them here to share.

Suppose your Date Field name is FillDt ,

1- number of days in the quarter of date field.

For example
If the fillDt is "2002-03-04" the count will be 63
If the fillDt is "2002-06-20" the count will be 81

expression:

DATEDIFF("DAY",(DT_DATE)(((DT_WSTR,4)YEAR(fillDt)) + "-" + ((DT_WSTR,2)(((DATEPART("QUARTER",fillDt) - 1) * 3) + 1)) + "-1"),fillDt)

number of days in the year of date field.

For example
If the fillDt is "2002-03-04" the count will be 63
If the fillDt is "2002-06-20" the count will be 171
expression:
DATEDIFF("DAY",(DT_DATE)(((DT_WSTR,4)YEAR(fillDt)) + "-1-1"),fillDt)

3- Number of months of the quarter of date

For example:
If the fillDt is "2002-03-31" the count will be 3
If the fillDt is "2002-06-30" the count will be 3
If the fillDt is "2002-07-13" the count will be 1
expression:
DATEDIFF("MONTH",(DT_DATE)(((DT_WSTR,4)YEAR(fillDt)) + "-" + ((DT_WSTR,2)(((DATEPART("QUARTER",fillDt) - 1) * 3) + 1)) + "-1"),fillDt)

4- quarter number of date

For example
If the fillDt is "2002-03-04" the quarter count will be 1
If the fillDt is "2002-06-20" the quarter count will be 2
If the fillDt is "2002-10-06" the quarter count will be 4
expression:
(DT_WSTR,1)(DATEPART("QUARTER",fillDt))

5- quarter start date

For example
If the fillDt is "2002-03-04" the "quarterStartDt" will be "2002-01-01"
If the fillDt is "2002-06-20" the "quarterStartDt" will be "2002-04-01"
expression:
(DT_DATE)(((DT_WSTR,4)YEAR(fillDt)) + "-" + ((DT_WSTR,2)(((DATEPART("QUARTER",fillDt) - 1) * 3) + 1)) + "-1")

6- quarter end date
For example
If the fillDt is "2002-03-04" the "quarterStopDt" will be "2002-03-31"
If the fillDt is "2002-06-20" the "quarterStopDt" will be "2002-06-30"

expression:
DATEADD("DAY",-1,DATEADD("MONTH",3,(DT_DATE)(((DT_WSTR,4)YEAR(fillDt)) + "-" + ((DT_WSTR,2)(((DATEPART("QUARTER",fillDt) - 1) * 3) + 1)) + "-1")))

Posted by

Reza Rad

in SSIS at 10:57 | Comments (2) | Trackbacks (0)

Vote for this article:

-2
-1
0
+1
+2

Current Vote: 2 of 5, 4 vote(s) 476 hits

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

I have read several articles on your blog now. You have helped me with some issues in the SSIS forum. Any way, I am really impressed by these articles! I am curious though. Do you work for Microsoft? Do you get paid to write this blog? Great job! Could you email me back these answers at the above email address please? Thanks.

#1 Ryan Daulton (Homepage) on 2010-08-18 12:34 (Reply)

Hi Ryan, Thanks for your visit

No I don't working for Microsoft, and I don't get paid for these posts. I like SSIS , and want to be sharp in this area everytime, this is why I'm active in forums like Experts-Exchange and TechNET . I also like to help others in my expertise field, I can do this with forums and my Blog writings. Regards, Reza