Hi, I have a .xlsx file having two date columns A and B A B 11/28/2011 11/28/2011 the format of Col A is date and format of Col B is Text when I load the same in database I get the following output Also I am reading both the columns as String A B Mon Nov 28 00:00:00 IST 2011 11/28/2011 so in col A the timestamp is attached and Col B is as it is Can anyone help me figure out why this is happening Regards, Ojas
Hi, I don't think there is DATE datatype in Oracle Sql because of that it is storing in Timestamp format. I have worked with MS SQL Server database. When i use to load data with date, it will convert date format to datetime. With Regards, Viswanath.Ch.
My first question is, where do you see the different output formats? You shows us 2 different string formats of the same date and this can only happened in a tLogRow component or similar. In the Oracle database the internal format has nothing to do how you see the format in any tool as text presentation. I think the database gets the correct value and you see the database content with a kind of viewer/sql tool which has its own settings how to represent the date as text.
Hi, I am getting the issue in the Oracle Sql Developer database when I load the file Also as I showed you that the format of Col A is date and format of Col B is Text A B 11/28/2011 11/28/2011 when I load the same in database I get the following output Also I am reading both the columns as String A B Mon Nov 28 00:00:00 IST 2011 11/28/2011 so in col A the timestamp is attached and Col B is as it is So what i am trying to say that if i am reading the date which is column A having date format in the input file and when read as string the output we get there is a time stamp attached and where as if i am reading the date which is column B having Text format,when it is read as string the output is retained as it is
Yes I agree with you but it is a fact: A Date typed value will always displayed (nothing else) in the way the displaying tol is configured. If you see your long format, than because the function converting the Date into a text format use this pattern. The way to display the Date value has nothing to do with the storage in the database. I am pretty sure the SQLDeveloper from Oracle can change its way to display the date values. I understood in the database Col_A is of the type DATE and Col_B is of the type VARCHAR2 is this correct? A DATE typed value will mostly shows differently as a VARCHAR2 value. You could setup the SQLDeveloper to show DATE value in the format MM/DD/YYYY but at the moment your SQLDeveloper has probably a different pattern for displaying DATE typed values. Once again: DATE is a DATE and in Oracle it is actually a kind of a numeric value.
hi ojasam_12, The dates in excel cells that are of type 'date' are read in the format 'Mon Nov 28 00:00:00 IST 2011'. If your excel data type is general (for the date columns), this error doesn't come up. But if you change the dates in date format directly to Generat format, some formula is applied and it returns a number. If the date columns are general, like if you add a space after the date, it becomes general, but this is not feasible for large datasets. If I get some workaround for this I will keep posted.
Hi ojasam_12, We can make talend read excel dates in our required formats by changing some settings while reading the Excel in tfileinputexcel. In the advanced settings of the component, check the option 'Convert Date column to String'. Then select the columns which are in date format in excel and specify the format in which you want them. This will make talend read the dates in string format and not like 'Mon Nov 28 00:00:00 IST 2011'.