Hi, I need some help in trying to figure out how I can determine the day of year using the SSIS expressions and format it to 3 digits long i.e 000 - 365 It is very simple in C#. I just need to use DateTime.Now.DayOfYear.ToString("000"). Thanks, Always_Another_Way

this should do it for you RIGHT("000" + (DT_WSTR,100)DAY(GETDATE()),3)Abhinav

I have not tested this Number of days in the year of date field. DATEDIFF("DAY",(DT_DATE)(((DT_WSTR,4)YEAR(fillDt)) + "-1-1"),fillDt) 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 http://www.rad.pasfu.com/index.php?/archives/16-Some-useful-Date-expressions-for-SSIS.html Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA Blog: Mr. Wharty's Ramblings MC ID: Microsoft Transcript Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

I'll try it out and let you know how it goes ! Thanks !

I'll try it out and let you know how it goes ! Thanks !

Jeff, I got the below error message when I tried your expression: ------------------------------ Expression cannot be evaluated ------------------------------ ADDITIONAL INFORMATION: The function "YEAR" does not support the data type "DT_WSTR" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator. Evaluating function "YEAR" failed with error code 0xC0047089. The function "YEAR" does not support the data type "DT_WSTR" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator. Evaluating function "DATEDIFF" failed with error code 0xC00470C5. ------------------------------ BUTTONS: OK ------------------------------

Thanks Abhinav. This works perfectly !

Hi Abhinav, How can i do the same with a date in this format : 20120104 ? Can you also recommend some reading material on SSIS expressions. Is there a book? Thanks, Always_Another_Way

I would say just create the expression in SQL and then look for the replacements in SSIS (DT_WSTR,100)YEAR(GETDATE()) + RIGHT("00" + (DT_WSTR,100)MONTH(GETDATE()),2) + RIGHT("00" + (DT_WSTR,100)DAY(GETDATE()),2) Abhinav

Thanks Abhinav. This works perfectly !

I would say just create the expression in SQL and then look for the replacements in SSIS (DT_WSTR,100)YEAR(GETDATE()) + RIGHT("00" + (DT_WSTR,100)MONTH(GETDATE()),2) + RIGHT("00" + (DT_WSTR,100)DAY(GETDATE()),2) Abhinav

RIGHT("000"+(DT_WSTR,3)DATEPART("Dayofyear",GETDATE()), 3) Burke

AB82, That's only going to give the DAY of the given MONTH Burke

RIGHT("000"+(DT_WSTR,3)DATEPART("Dayofyear",GETDATE()), 3) Burke