CODEWITHSUNDEEP
sql-serverssis
Ali
Ali

Reputation: 3

SSIS - Date conversion Date formatting MM/DD/YYYY or DD/MM/YYYY

I am getting confusion when loading data from staging to target for the date column by using ssis as ETL tool. Staging:

varchar (datatype)-'09/05/2017'

Target: conversion transformation is used to convert to date datatype.Now records like 09/05/2017. But while loading to final table Record is like 2017-09-05 00:00:00 which is datetime datatype.

Actual expected result:2017-05-09 00:00:00

Note:OTHER records are inserted with correct format.

Upvotes: 0

Views: 4036

Answers (1)

Troy Witthoeft
Troy Witthoeft

Reputation: 2676

In SSIS - The LocaleID property of your DataFlow components handle whether dates are parsed into MM/DD/YYYY or DD/MM/YYYY.

enter image description here

In SQL - A similar thing is handled by the SET LANGUAGE command.

SET LANGUAGE us_english
DECLARE @d DATETIME = '1929/12/18'

SET LANGUAGE british
DECLARE @d DATETIME = '1929/12/18' -- fails

Check your settings in both SQL and SSIS. Adjust if necessary.

Upvotes: 2

Related Questions