How to convert sybase timestamp to Oracl timestamp
Moderators: chulett, rschirm, roy
How to convert sybase timestamp to Oracl timestamp
I have input Seq File stage, pointing to a csv file which came from Sybase, with a Timestamp field whose data looks like:
Feb 10 2007 10:30:30:450AM
1) In the columns view (of the Seq File Stage) should the length of the timestamp be specified?
2) What type of conversion (function, macro, or whatever) should be used within the Transform Stage to generate an Oracle-compatible value as input to the ODBC Oracle Stage?
Thanks afrorehand,
-- john
Feb 10 2007 10:30:30:450AM
1) In the columns view (of the Seq File Stage) should the length of the timestamp be specified?
2) What type of conversion (function, macro, or whatever) should be used within the Transform Stage to generate an Oracle-compatible value as input to the ODBC Oracle Stage?
Thanks afrorehand,
-- john
There appears to be 3 different timestamp formats, viz:
a) Sybase,
b) Ascential, and
c) Oracle.
I guess my real problem is that I don't understand how we determine if the data from the input file is accepted as a valid timestamp format (in Sybase or Ascential format???), prior to exercising any derivation function.
Somehow I have to use AT LEAST 2 functions to go thru all 3 formats.
And then you suggest that I use a String as an intermediate step, which
now create 5 steps:
a) Sybase
b) string
c) Ascential
d) string, and
e) Oracle
Now I am really confused.
Are there any examples available is see how this is done?
-- john
a) Sybase,
b) Ascential, and
c) Oracle.
I guess my real problem is that I don't understand how we determine if the data from the input file is accepted as a valid timestamp format (in Sybase or Ascential format???), prior to exercising any derivation function.
Somehow I have to use AT LEAST 2 functions to go thru all 3 formats.
And then you suggest that I use a String as an intermediate step, which
now create 5 steps:
a) Sybase
b) string
c) Ascential
d) string, and
e) Oracle
Now I am really confused.
Are there any examples available is see how this is done?
-- john
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
Re: How to convert sybase timestamp to Oracl timestamp
Do you have a space before AM?jherr22 wrote:
Feb 10 2007 10:30:30:450AM
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Use the following C function to convert your input into YYYY-MM-DD 24HH:mm:ss.nnn format. THen use StringToTimestamp() to convert the string to timestamp.
Code: Select all
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
char* SybaseToOracleTp(char* InTp)
{
//Initialize variables
const int SIZE = 30;
char* month = (char *)malloc(SIZE);
char* day = (char *)malloc(SIZE);
char* year = (char *)malloc(SIZE);
char* hour = (char *)malloc(SIZE);
char* newHr = (char *)malloc(SIZE);
char* min = (char *)malloc(SIZE);
char* sec = (char *)malloc(SIZE);
char* msec = (char *)malloc(SIZE);
char* time = (char *)malloc(SIZE);
char* intMon = (char *)malloc(SIZE);
char* finOut = (char *)malloc(SIZE);
const char* calender[] = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
int hr = 0;
//AM or PM
char* p = strstr(InTp, "PM");
//Disect the Date
strcpy(month, strtok(InTp, " "));
strcpy(day, strtok(NULL, " "));
strcpy(year, strtok(NULL, " "));
strcpy(time, strtok(NULL," "));
//Disect Time
strcpy(hour, strtok(time, ":"));
strcpy(min, strtok(NULL, ":"));
strcpy(sec, strtok(NULL, ":"));
strcpy(msec, strtok(NULL, ":"));
//get numeric representation of Month
for(int i = 0; i < 12; i++)
{
if (strcmp(month, calender[i]) == 0)
sprintf(intMon, "%02d", i + 1);
}
if ((p) && strcmp(hour, "12") != 0)
{
hr = atoi(hour);
hr+=12;
sprintf(hour, "%02d", hr);
}
if ((!p) && strcmp(hour, "12") == 0)
{
strcpy(hour, "00");
}
//format string to YYYY-MM-DD HH:MM:SS.sss
sprintf(finOut, "%s-%2s-%s %s:%s:%s.%s", year, intMon, day, hour, min, sec, msec);
//free memory
free(month);
free(day);
free(year);
free(hour);
free(min);
free(sec);
free(msec);
free(time);
free(intMon);
return finOut;
}
Last edited by DSguru2B on Wed Feb 07, 2007 5:25 pm, edited 1 time in total.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
No Sam, this is just for the input shown by the OP. Because i am disecting the date and I know that the first part is going to be Month in three character string, the second part is Day and so on. It wont work for any input format.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Including all foreign language representations? If you claim to write anything "generic" in DataStage you will need to make use of NLS locales, for example 23 AVR 2007 is a valid date in Canada, France and lots of other places.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I am not getting anywhere with this Timestamp problem.
This post will simplify the problem, in hopes of advice.
Within the Seq. File Stage, I now point to a CSV file, each row with 6 fields, with each field in double quotes, and each separated by a coma
(and no trailing coma).
The 4th field in the row is a Timestamp.
I loaded the table definition with the 6 fields, the 4th of which is a
Timestamp, with length 26, and no scale value, and not nullable.
The data in the 4th field is: "Jan 10 2007 10:30:31:246AM" which is
26 chars long. (The data came from a Timestamp in Sybase.)
I then bring up Seq. File Properties, selecting the columns tab, and then I click on the "View Data", and I get:
"Bad trailing quote character at field "my_date"; looking for '34' but found '0', at offset: 89"
This makes no sense to me.
I have tried everything:
-- changing the length of the column definition,
-- dropping the double quotes between the comas,
and many other attempts.
Nothing will bring up the timestamp (using View Data)
If I switch the field type from Timestamp to Varchar, "my_date" shows up just fine (under View Data).
Within DataStage Manager | selected Project | Properties button | Parallel tab , and then under "Timestamp String" I see the default Timestamp string which is:
%yyyy-%mm-%dd %hh:%nn:%ss
I have no idea when this default format is used, but I decided to try it.
So, I tried changing my data in the input file to look like that, and I still
could not get View Data to bring up the data.
In a previous post by Brian, C-code is provided, which is great, which I am now trying to compile into a library.
But, if View Data cannot see the data, (which means to me that the data will be rejected upon Job Execution), then how could a C-function operate on it?
I'm baffled.
Any possibility of help with this?
Thanks
-- john
This post will simplify the problem, in hopes of advice.
Within the Seq. File Stage, I now point to a CSV file, each row with 6 fields, with each field in double quotes, and each separated by a coma
(and no trailing coma).
The 4th field in the row is a Timestamp.
I loaded the table definition with the 6 fields, the 4th of which is a
Timestamp, with length 26, and no scale value, and not nullable.
The data in the 4th field is: "Jan 10 2007 10:30:31:246AM" which is
26 chars long. (The data came from a Timestamp in Sybase.)
I then bring up Seq. File Properties, selecting the columns tab, and then I click on the "View Data", and I get:
"Bad trailing quote character at field "my_date"; looking for '34' but found '0', at offset: 89"
This makes no sense to me.
I have tried everything:
-- changing the length of the column definition,
-- dropping the double quotes between the comas,
and many other attempts.
Nothing will bring up the timestamp (using View Data)
If I switch the field type from Timestamp to Varchar, "my_date" shows up just fine (under View Data).
Within DataStage Manager | selected Project | Properties button | Parallel tab , and then under "Timestamp String" I see the default Timestamp string which is:
%yyyy-%mm-%dd %hh:%nn:%ss
I have no idea when this default format is used, but I decided to try it.
So, I tried changing my data in the input file to look like that, and I still
could not get View Data to bring up the data.
In a previous post by Brian, C-code is provided, which is great, which I am now trying to compile into a library.
But, if View Data cannot see the data, (which means to me that the data will be rejected upon Job Execution), then how could a C-function operate on it?
I'm baffled.
Any possibility of help with this?
Thanks
-- john