Looks like a script task, and you'll need to compare against regular expressions. How are you going to determine if 02-06-11 means February 6th 2011, June 2nd 2011 or June 11th 2002?Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Hi All, I designed SSIS package that is load data from Sql Server(Source) to Destination table(Sql Server).In that source table I have some fields as Date and time. The format of Date and Time is not same. Please find below list of different date formats,the Date can come in any one of below formats. 01-05-12 18:19 07/12/10 14-Aug-12 08-21-2012 11-08-12 0:08 31-03-2010 27-08-12 30-Aug-12 27-AUG-12 08.48.19 AM 02-06-11 But I need to load data in Sql server table with YYYY-MM-DD format only.Can any one suggest how can I handle this one in SSIS. PS. When ever the date is smaller than 12 in dd-mm-yyyy format it is treating as Month. In below screen For Invoice ID 17 (Invoice date in DD-MM-YYYY format in Nvarchar datatype), in Source the data is available for Feb 01 when I load data in to My destination table(For Invoice date having datatype as Datetime) it shows for Month of Jan 02 that is it changes date to month and viceversa. Regards, Praveen

Looks like a script task, and you'll need to compare against regular expressions. How are you going to determine if 02-06-11 means February 6th 2011, June 2nd 2011 or June 11th 2002?Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Hi Praveen, Use the below query to convert the data to datetime format, If the destination datatype is date, you should use a data conversion transformation to convert it to date type. SELECT Replace(convert(varchar(10),CONVERT(DATETIME,Column),111),'/','-') From table Except 31-03-2010,27-08-12 all the different formats handled perfectly. Reason for those not being handled is sql expects first value to be a month instead of date. if its the other way around in your table. Try setting this, SET DATEFORMAT DMY SELECT Replace(convert(varchar(10),CONVERT(DATETIME,'27-08-12'),111),'/','-') With this query you will get an error for this record '08-21-2012', So you should change the data one way. Rajkumar

we are facing same issue.Regards, Praveen

Hi Rajkumar, I tried same thing but no luck.it is not support for all date formats.Regards, Praveen

HI Praveen, how about this? Declare @Date Table(Dt nvarchar(30)) Insert Into @Date Select '01-05-12 18:19' Union All Select '07/12/10' Union All Select '14-Aug-12' Union All Select '08-21-2012' Union All Select '11-08-12 0:08' Union All Select '31-03-2010' Union All Select '27-08-12' Union All Select '30-Aug-12' Union All Select '27-AUG-12 08.48.19 AM' Union All Select '02-06-11' Set DateFormat DMY SELECT Replace(convert(varchar(10),CONVERT(DATETIME,Dt),111),'/','-') From @Date Where ISDATE(Dt) = 1 Set DateFormat MDY SELECT Replace(convert(varchar(10),CONVERT(DATETIME,Dt),111),'/','-') From @Date Where ISDATE(Dt) = 1 You Can Use one Query in One OLE DB Source and Merge them. Rajkumar

Hi The below link shows to validate different date formats. All you have to do is apply the same in your case and Get a return value in desired format. Write a method in such a way ,just pass your date and the method should validate your date a return the vale. http://dotnetedu.com/KB2/Validate-Date-In-Different-Formats-Using-Sharp-net.aspx

Hi The below link shows to validate different date formats. All you have to do is apply the same in your case and Get a return value in desired format. Write a method in such a way ,just pass your date and the method should validate your date a return the vale. http://dotnetedu.com/KB2/Validate-Date-In-Different-Formats-Using-Sharp-net.aspx

Hi The below link shows to validate different date formats. All you have to do is apply the same in your case and Get a return value in desired format. Write a method in such a way ,just pass your date and the method should validate your date a return the vale. http://dotnetedu.com/KB2/Validate-Date-In-Different-Formats-Using-Sharp-net.aspx