OK my challenge, I have a file that SSIS (SQL 2005) reads and I need to convert the collum to a date but it does not include year. I am given "2-Dec" now this report will be passed to SSIS every billing period so the report may include end of a year and beginning of the following year. Dec 2010-Jan 2011 as example. So I conclude if month > current month then has to be previous year. Now that I know the year I want to use this information to convert "2-Dec" to mm/dd/yyyy. Now my proposed solution does not work and will not pass the expression syntax. I have spent a few hours rebuilding this step by step in the stupid expression builder to no avail. Can someone assist me? Either figureout what I am doing wrong or provide a alternative? SQL Representation: SELECT CASE WHEN CONVERT(datetime,'2-Dec'+'-'+CONVERT(varchar(4),DATEPART(yy,GETDATE()))) > GETDATE() THEN CONVERT(nvarchar(10),CONVERT(datetime,'2-Dec'+'-'+CONVERT(varchar(4),DATEPART(yy,GETDATE())-1)),101) ELSE CONVERT(nvarchar(10),CONVERT(datetime,'2-Dec'+'-'+CONVERT(varchar(4),DATEPART(yy,GETDATE()))),101) END AS DATE Expression Attempt: ((DT_DATE)([Date]+"-"+((DT_STR,4,1252)DATEPART("year",GETDATE())))) > GETDATE() ? ((DT_STR,10,1252)((DT_DATE)([Date]+"-"+((DT_STR,4,1252)DATEPART("year",DATEADD( "year", -1, GETDATE() )))))) : ((DT_DATE)([Date]+"-"+((DT_STR,4,1252)DATEPART("year",GETDATE())))) Thank you, Thanks, Matt

I would use a Script Transform. In it you would create one additional output column (called DateOut, for example) and take your one Date column as an input. Now do all your logic with If/Then/Else blocks in the language of your choice VB.Net or C#. These languages are better suited to this type of logic than the Expression Builder, whcih can get kind of klunky in this situation. Makes it harder to debug as well. Set the throughput as Synchronous and you should have no impact on performance.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hello, Here you go (UPPER(SUBSTRING(DT,FINDSTRING(DT,"-",1) + 1,LEN(DT))) == "JAN" ? 1 : UPPER(SUBSTRING(DT,FINDSTRING(DT,"-",1) + 1,LEN(DT))) == "FEB" ? 2 : UPPER(SUBSTRING(DT,FINDSTRING(DT,"-",1) + 1,LEN(DT))) == "MAR" ? 3 : UPPER(SUBSTRING(DT,FINDSTRING(DT,"-",1) + 1,LEN(DT))) == "APR" ? 4 : UPPER(SUBSTRING(DT,FINDSTRING(DT,"-",1) + 1,LEN(DT))) == "MAY" ? 5 : UPPER(SUBSTRING(DT,FINDSTRING(DT,"-",1) + 1,LEN(DT))) == "JUN" ? 6 : UPPER(SUBSTRING(DT,FINDSTRING(DT,"-",1) + 1,LEN(DT))) == "JUL" ? 7 : UPPER(SUBSTRING(DT,FINDSTRING(DT,"-",1) + 1,LEN(DT))) == "AUG" ? 8 : UPPER(SUBSTRING(DT,FINDSTRING(DT,"-",1) + 1,LEN(DT))) == "SEP" ? 9 : UPPER(SUBSTRING(DT,FINDSTRING(DT,"-",1) + 1,LEN(DT))) == "OCT" ? 10 : UPPER(SUBSTRING(DT,FINDSTRING(DT,"-",1) + 1,LEN(DT))) == "NOV" ? 11 : 12) > MONTH(GETDATE()) ? (DT_STR,2,1252)(DAY(GETDATE())) + "/" + (DT_STR,2,1252)(MONTH(GETDATE())) + "/" + (DT_STR,4,1252)(YEAR(GETDATE()) - 1) : (DT_STR,2,1252)(DAY(GETDATE())) + "/" + (DT_STR,2,1252)(MONTH(GETDATE())) + "/" + (DT_STR,4,1252)(YEAR(GETDATE())) Thanks http://sqlage.blogspot.com/

Thank you Aamir. I have decided to try the Script Task since I am really hating the expression builder with a passion at the moment :). Todd, when I try to do anything with the string date input I get a conversion warning message. If(Row.Date + "-" + Year(Now.Date)) > Now.Date Then End If Row.Date +"-" has a blue swiggly saying "Option Strict On disallows implicit conversions from 'String' to 'Double'."The date field is a string input, I don't understand the message. I tried CString() but it says CString not defined. Am I forgetting something? Thanks, Matt

Hi, Extract the Month( like DEC, Jan, Feb) from the data and then convert this to number, then you need to compare to Current Month number, IF TRUE THEN YOU WILL set to DD/MM/YYYY-1 ELSE DD/MM/YYYY I am not sure about any function in C# or VB that can take Short Month Names (DEC,JAN,FEB) and compare them to Current Month, That's why I convert your short month names into Month number and compared with Month Number in Derived column and then produced output date. Thankshttp://sqlage.blogspot.com/

use script task. declare two variables dt and st as string. i gave dt value as 02-Jan i just tested with the following script. it worked. try this and let me know if it works. Dim di As String Dim fi As String Dim ci As String Dim yi As String Dim curmonth As String Dim curyear As String Dim CurrentDate As Date CurrentDate = Now() di = Dts.Variables("dt").Value.ToString() 'MsgBox(di) fi = Mid(di, 4, 3) 'MsgBox(fi) If fi = "Jan" Then ci = Right("00" + CStr(1), 2) ElseIf fi = "Feb" Then ci = Right("00" + CStr(2), 2) ElseIf fi = "Mar" Then ci = Right("00" + CStr(3), 2) ElseIf fi = "Apr" Then ci = Right("00" + CStr(4), 2) ElseIf fi = "May" Then ci = Right("00" + CStr(5), 2) ElseIf fi = "Jun" Then ci = Right("00" + CStr(6), 2) ElseIf fi = "Jul" Then ci = Right("00" + CStr(7), 2) ElseIf fi = "Aug" Then ci = Right("00" + CStr(8), 2) ElseIf fi = "Sep" Then ci = Right("00" + CStr(9), 2) ElseIf fi = "Oct" Then ci = Right("00" + CStr(10), 2) ElseIf fi = "Nov" Then ci = Right("00" + CStr(11), 2) ElseIf fi = "Dec" Then ci = Right("00" + CStr(12), 2) End If 'MsgBox(ci) curmonth = Right("00" & CStr(DatePart(DateInterval.Month, CurrentDate)), 2) curyear = CStr(DatePart(DateInterval.Year, Now())) 'MsgBox(curmonth) 'MsgBox(curyear) If ci > curmonth Then yi = CStr(CDbl(curyear) - 1) Else : yi = curyear End If 'MsgBox(yi) Dts.Variables("st").Value = ci + "/" + Right("00" + Mid(di, 1, 2), 2) + "/" + yi MsgBox("Date extracted is: " & Dts.Variables("st").Value.ToString()) Dts.TaskResult = Dts.Results.Success

in the above step declare dt as readonlyvariable and st as readwritevariable

I see how you guys are stripping out the month words and then doing some kind of conversion but the design I have works just as well just couldn't figure out exacly how to get the syntax correct, untill now. This is my final code. ' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Public Class ScriptMain Inherits UserComponent Public Overrides Sub DDMM_ProcessInputRow(ByVal Row As DDMMBuffer) ' If Convert.ToDateTime(Row.Date + "-" + CStr(Year(Now.Date))) > Now.Date Then Row.FullDate = CStr(Convert.ToDateTime(Row.Date + "-" + CStr(Year(DateAdd(DateInterval.Year, -1, Now.Date))))) Else Row.FullDate = CStr(Convert.ToDateTime(Row.Date + "-" + CStr(Year(Now.Date)))) End If ' End Sub End Class Thanks, Matt