Getting Null in birthdatedate column instead of actual data

Question

Post reply

Getting Null in birthdatedate column instead of actual data

vipin_jha123

Hall of Fame

Points: 3085
More actions
May 21, 2015 at 12:19 am

#308329

Hi ,
In my SSIS package I am deriving birth date column by using below
CASE WHEN Col015 IS NULL THEN Col015 ELSE CAST(SUBSTRING(Col015,1,2) + '/' + SUBSTRING(Col015,3,2) + '/' + SUBSTRING(Col015,5,2) AS DATETIME) END AS BirthDate
and in my destination table birthdate datatype is datetime.
when I am inserting actual data is not getting inserting . only null gets inserted into birthdate column.
error
[OLE DB Destination [227]] Error: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input]. Columns[BirthDate] on OLE DB Destination.Inputs[OLE DB Destination Input].
The column status returned was: "The value could not be converted because of a potential loss of data.".
regards,
Vipin Jha

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQL.AP Hall of Fame Points: 3244 More actions · Answer 1

Please share some sample data you have in this column.

____________________________________________________________

AP

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 2

Hi ,

Sample data are below :-

06/06/63

09/17/61

06/03/69

11/23/64

07/10/90

02/02/69

09/30/70

12/05/49

01/04/67

Regards,

Vipin jha

SQL.AP Hall of Fame Points: 3244 More actions · Answer 3

vipin_jha123 (5/21/2015)
Hi ,
Sample data are below :-
06/06/63

If this is the source data, then for sure your conversion will fail. Did you even try to execute your CASE statement in SQL? It will throw you an error.

I am not getting why are you taking substring for these dates when they are already in the same format?

You've shared sample data for your source i.e. Col015. Correct me if I'm wrong.

____________________________________________________________

AP

SQL.AP Hall of Fame Points: 3244 More actions · Answer 4

vipin_jha123 (5/21/2015)
Hi ,
Sample data are below :-
06/06/63
09/17/61
06/03/69
11/23/64
07/10/90
02/02/69
09/30/70
12/05/49
01/04/67
Regards,
Vipin jha

If this the sample data after conversion, then while loading in SSIS it will consider these dates as invalid because the middle value is greater than 12.

Better convert your date format to yyyy-mm-dd rather than dd/mm/yy. SSIS and SQL understand this format pretty well.

____________________________________________________________

AP

Emil B SSCertifiable Points: 5568 More actions · Answer 5

Check yourself what your substrings are returning. anyway this can be converted to datetime without any string operations:

SET DATEFORMAT mdy

;WITH CTE_BD

AS(

SELECT *

FROM (

SELECT '06/06/63' Col015 UNION ALL

SELECT '09/17/61' Col015 UNION ALL

SELECT '06/03/69' Col015 UNION ALL

SELECT '11/23/64' Col015 UNION ALL

SELECT '07/10/90' Col015 UNION ALL

SELECT '02/02/69' Col015 UNION ALL

SELECT '09/30/70' Col015 UNION ALL

SELECT '12/05/49' Col015 UNION ALL

SELECT '01/04/67' Col015) AS T)

SELECT CAST(Col015 AS DATETIME), SUBSTRING(Col015,1,2) , SUBSTRING(Col015,3,2) , SUBSTRING(Col015,5,2)

FROM CTE_BD

but be careful because for example '12/05/49' might be converted to '2049-12-05 00:00:00.000'