Description: "Conversion failed when converting date and/or time from character string."

Question

The following is the metadata of my source table:

Hours_StageTbl

type        varchar(100)
Op          varchar(100)
SN          varchar(100)
Hours       float
R_Date      varchar(100)
Source      int
BeginDate   date
Age         float

The following is the metadata of my destination table:

Hours

HoursId    PK, uniqueidentifier
SN         varchar(100)
Type       varchar(100)
Source     int
Date       datetime
Op         varchar(100)
Age        float
Hours      float
BornOnDate datetime

The following is my metadata for the table that I JOIN to:

A_Age

Type varchar(50)
SN varchar(50)
Op varchar(50)
Reg varchar(50)
RDateAge decimal
Date date

The following is my SQL Command Text in the OLE DB Source Editor in SSIS:

SELECT 
    F.Type
   ,F.Op
   ,F.SN
   ,F.Hours
   ,convert(date, F.R_Date +'28', 111) AS 'R_Date'
   ,F.Source
   ,F.BeginDate
   ,F.Age
   ,A.Type
   ,A.SN
   ,A.Op AS 'A_Op'
   ,A.Reg
   ,A.RDateAge
   ,A.Date
   ,(DATEDIFF(dd, convert(date, F.R_Date +'28', 111), GETDATE())/365.00) + A.RDateAge    as 'AdjustedAge'  
FROM 
    Hours_StageTbl F
INNER JOIN 
    A_Age A
    ON 
    F.SN = A.SN
ORDER BY 
    F.SN
   ,F.Date

My OLE DB Source Editor flows to Data Conversion and again converts Date from Source table to DT_Date which then flows to OLE DB Command.

It must be noted that Date varchar(100) From Hours_StageTbl (source table) is to be inserted into Date datetime in Hours (destination table) and is what is giving me problem I believe.

No matter what I do to try to fix the conversion I get the following error:

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E07 Description: "Conversion failed when converting date and/or time from character string.".

I have spent hours trying to rectify this. An extra set of eyes would be great.

Answer 1

Issue on Convert(date, F.R_Date +'28', 111).

Don't add days directly with string. Use funcion DateAdd( Eg. DateAdd(dd,4,R_Date ) )

EG.

Convert(date, DateAdd(dd,28, F.R_Date ), 111)