Hello, I am importing data from 1 table to another table. The source table has a DOB field of type date and format YYYY-MM-DD. I have to import it into the destination table as VARCHAR(8) and the format should be YYYYMMDD. I am using the following SQL to do the import in the OLEDB Source: CASE WHEN DOB IS NULL OR DOB = '' OR LEFT(DOB,4) < 1800 THEN '18000101' ELSE CAST(REPLACE(DOB,'-','') AS VARCHAR(8)) END As dob But I am gettig the following error: The value could not be converted because of a potential loss of data. Can someone please suggest how to fix this error? Thanks, KK

Hi kesar_k, You need to filter records that are not of date type of datetime/smalldatetime or use ISDATE() function in CASE statement SELECT * FROM [dbo].[yourTable1] WHERE ISDATE(CAST([YourdateColumn] AS DATETIME)) = 0 Sergei

Hi Sergei, I already checked all the dates in the table and none of them returned ISDATE() = 0 thats why I skipped it in the code. Thanks!

change your destination data-type from VARCHAR(8) to VARCHAR(15), if it works than trying below thing too with your case statement:- replace(replace('\n','')'\t', '') Thanks kk

Hi kesar_k, You need to check for length of data itself as well, for example SELECT ISDATE('7777 - 01 - 11') returns True. However, LEN() returns 14 Sergei

Hi, Please try the below CASE WHEN DOB IS NULL OR DOB = '' OR LEFT(convert(varchar(8),DOB,112),4) < 1800 THEN '18000101' ELSE CONVERT(VARCHAR(8),DOB,112) END As dob Hope this helps. Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

Bilal: I tried the SQL you provided but it still fails with the same error: There was an error with output column "dob" (212) on output "OLE DB Source Output" (11). The column status returned was: "The value could not be converted because of a potential loss of data.". Kumar03: I also tried increasing the length of the destination to varchar(15) but that didn't help either. Anything else that I should try? Thanks, KK

I only had to delete and recreate the step to fix the problem.