Excel Source file with Date fields
Posted: Fri Dec 17, 2004 3:27 pm
I have an excel source file with a Date field of the format *3/14/2001. the explanation on * is that
"Date Formats display date and time serial numbers as date values. Except for items that have an (*), applied formats do not switch date orders with the operating system."
The date value as visible in the excel source file is 12/18/2002 but when I view data in Datastage ODBC source its 2000-12-18 00:00:00. So as the explanation says I thought it was something to do with the date format set in the OS of the DS server. But my DS server also has Date set as m/dd/yyyy. So how or from where does DS pick the 'yyyy-mm-dd hh:mm:ss' format?
In another of my source excel files I have a similar date field but the only difference being the first couple of records have blank date values (the format was still *3/14/2001).
The date values as visible in the excel file 11/1/2004 but when I view data in Datastage ODBC source its blank. But when I edited the source file and put a valid date in the first record then I saw all other valid dates available in the source and in yyyy-mm-dd hh:mm:ss format.
Has anybody faced this kind of a situation
and is there a work around to it.
Or does it have to do with the fact that excel is not installed in the DS server?
Thanks
Sonia.
Note : The first file did not show any funny characteristics as in the second, when the first record (s) had balnk dates.![Confused :?](./images/smilies/icon_confused.gif)
"Date Formats display date and time serial numbers as date values. Except for items that have an (*), applied formats do not switch date orders with the operating system."
The date value as visible in the excel source file is 12/18/2002 but when I view data in Datastage ODBC source its 2000-12-18 00:00:00. So as the explanation says I thought it was something to do with the date format set in the OS of the DS server. But my DS server also has Date set as m/dd/yyyy. So how or from where does DS pick the 'yyyy-mm-dd hh:mm:ss' format?
In another of my source excel files I have a similar date field but the only difference being the first couple of records have blank date values (the format was still *3/14/2001).
The date values as visible in the excel file 11/1/2004 but when I view data in Datastage ODBC source its blank. But when I edited the source file and put a valid date in the first record then I saw all other valid dates available in the source and in yyyy-mm-dd hh:mm:ss format.
Has anybody faced this kind of a situation
![Confused :?](./images/smilies/icon_confused.gif)
Or does it have to do with the fact that excel is not installed in the DS server?
Thanks
Sonia.
Note : The first file did not show any funny characteristics as in the second, when the first record (s) had balnk dates.
![Confused :?](./images/smilies/icon_confused.gif)