CODEWITHSUNDEEP
sql-serveroracle-databasedatessis
djohnjohn
djohnjohn

Reputation: 65

SSIS from oracle to sql server date format running in visual studio vs ssms

I'm getting different date formats in my destination date column 'creation_date' when running my ssis packages in visual studio vs running them in ssms.

  1. oracle source date format is MM-DD-YYYY HH24:MI:SS, however column type is set to VARCHAR2, unfortunetly i don't have authorisation to set a datetime format:

enter image description here

  1. the package runs great in visual studio, i use an ado.net source where i specify the sql command:
select record_id, to_char(to_date(creation_Date, 'MM-DD-YYYY HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS') creation_date
  1. date column gets imported the way i want:

enter image description here

  1. but when running the same package in ssms, the format gets switched:

enter image description here

Upvotes: 0

Views: 624

Answers (1)

MT0
MT0

Reputation: 168440

Don't use strings to store date values.

Currently, you are having to convert from string-to-date-to-string at the source and then string-to-datetime at the destination.

Since you cannot change your VARCHAR2 data type in the source, don't pass your date data as strings between databases; use a DATE data type:

SELECT record_id, 
       to_date(creation_Date, 'MM-DD-YYYY HH24:MI:SS') AS creation_date
FROM   your_table

Then you can handle the value as a DATETIME data type in the destination without having an intermediate string format to manage.

Upvotes: 1

Related Questions