May 11, 2012 at 1:21 am
Hi all,
I am relatively new to SSIS. I have constructed a package that loads data from an oracle database to an SQL Server 2008.
One of the columns i need to transfer is a date column in oracle having the following format DD/MM/YYYY
(for example 10/07/2014). When the package runs transfers the column but on the SQL server it comes with the format YYYY-MM-DD HH:MM:SS (for example 2014-07-10 00:00:00.000).
I need to retain the format DD/MM/YYYY on the SQL Server.
I tried to use the derived column component but i didn't make it work.
Any suggestions please
May 11, 2012 at 1:41 am
The datetime datatype only stores values in yyyy-mm-ddThh:mm:ss.ms format. I would leave it as this and do the formatting into dd/mm/yyyy in the application or in the calling stored procedure using the convert function and specifiying a date style code like the this
SELECT
CONVERT(DATE,columnname,103)
FROM
table
If this is not an option then you will need to change the datatype to a string based type like varchar(10) to get your results in the format you require.
More date styles can be found on this link http://msdn.microsoft.com/en-us/library/ms187928.aspx
May 11, 2012 at 1:50 am
Unfortunately neither is an option. Could this be made by using the script component?
If so can please provide example code?
Thanks a lot
May 11, 2012 at 1:52 am
no the only way to store a date in any other format than yyyy-mm-ddThh:mm:ss.ms is to store it as a string based data type, otherwise it is convert it at the front end or in the procedures retreving the data for the applications
May 11, 2012 at 2:00 am
OK thanks a lot for your time
May 11, 2012 at 4:39 am
The column could be stored as a datetime and another computed column added which does the suggested CONVERT on the date to store it in the format you require. Seems ugly, because I agree 100% with Anthony, but maybe you have good reasons which we have not foreseen...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 11, 2012 at 4:43 am
Thanks a lot Phil.
I shall try to work it out.
May 11, 2012 at 4:45 am
Shouldn't be hard - post back if you can't make it work.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply