convert datetime to timestamp

[hondaccord94]

Hi Brad,

I have written like this in my code, but my operator is not generated.
can you please tell me what else I need to add.
how should I specify the header file declarations.

here my input column is CloseDate(in the varchar datatype) and output column(which is in timestamp datatype ) is outdate

out.outdate= in.CloseDate;
out.myTmsp.set(in.CloseDate, "%yyyy-%mm-%dd %hh:%nn:%ss.000000");

---Thanks

Okay, here's an example.

Datafile:

0001|2005-07-04 10:15:01.123456
0002|2005-07-04 10:25:02.123456
0003|2005-07-04 10:35:03.123456
0004|2005-07-04 10:45:04.123456
0005|2005-07-04 10:55:05.123456

Sequential Stage input schema:

Code: Select all

-schema record
  {final_delim=end, record_delim='\\n', delim='|', null_field=''}
(
  recid:int32 {max_width=4};
  datetimeString:nullable string[26] {max_width=50};
)

Buildop uses the same schema as input, and the same for output, except a new field called myTmsp is added at the end (definition = myTmsp: nullable timestamp[microseconds].

Buildop logic:

Code: Select all

out.recid = in.recid;
out.datetimeString = in.datetimeString;

// Example input date string is "2005-07-04 10:15:01.123456"

// Populate myTmsp from datetimeString using set() function
out.myTmsp.set(in.datetimeString, "%yyyy-%mm-%dd %hh:%nn:%ss.6");

The whole job is seq -> buildop -> dataset

Contents of the dataset (generated with orchadmin dump -name mydataset.ds):

recid:1 datetimeString:2005-07-04 10:15:01.123456 myTmsp:2005-07-04 10:15:01.123456
recid:2 datetimeString:2005-07-04 10:25:02.123456 myTmsp:2005-07-04 10:25:02.123456
recid:3 datetimeString:2005-07-04 10:35:03.123456 myTmsp:2005-07-04 10:35:03.123456
recid:4 datetimeString:2005-07-04 10:45:04.123456 myTmsp:2005-07-04 10:45:04.123456
recid:5 datetimeString:2005-07-04 10:55:05.123456 myTmsp:2005-07-04 10:55:05.123456

As for finding these functions, I guess the documentation is really lacking - the PX doc has no mention of the buildop functions. You'll need to look in the header files themselves: <your Ascential root dir>/DataStage/PXEngine/include/apt_util/time.h

Hope this helps.

Brad.

[bcarlson]

here my input column is CloseDate(in the varchar datatype) and output column(which is in timestamp datatype ) is outdate

out.outdate= in.CloseDate;
out.myTmsp.set(in.CloseDate, "%yyyy-%mm-%dd %hh:%nn:%ss.000000");

I think you had some typos (see bolded and italicized code above) - what you need is this:

Code: Select all

out.outdate.set(in.CloseDate, "%yyyy-%mm-%dd %hh:%nn:%ss.000000");

You were referencing myTmsp in your code, and trying to assign the CloseDate string directly to outdate. My guess is there was an error because myTmsp is not defined, and the outdate assignment failed because the compiler did not know how to convert the varchar to a timestamp.

[hondaccord94]

Thanks a lot Brad, its working fine now.

here my input column is CloseDate(in the varchar datatype) and output column(which is in timestamp datatype ) is outdate

out.outdate= in.CloseDate;
out.myTmsp.set(in.CloseDate, "%yyyy-%mm-%dd %hh:%nn:%ss.000000");

I think you had some typos (see bolded and italicized code above) - what you need is this:

Code: Select all

out.outdate.set(in.CloseDate, "%yyyy-%mm-%dd %hh:%nn:%ss.000000");

You were referencing myTmsp in your code, and trying to assign the CloseDate string directly to outdate. My guess is there was an error because myTmsp is not defined, and the outdate assignment failed because the compiler did not know how to convert the varchar to a timestamp.

[hondaccord94]

Hi Brad,
Can you please confirm me that this function will convert the varchar (input column )datatype to timestamp(output column)
out.outdate.set(in.CloseDate, "%yyyy-%mm-%dd %hh:%nn:%ss.000000");

Thanks

[bcarlson]

Hi Brad,
Can you please confirm me that this function will convert the varchar (input column )datatype to timestamp(output column)
out.outdate.set(in.CloseDate, "%yyyy-%mm-%dd %hh:%nn:%ss.000000");

Thanks

What is the exact format of your input datetime? Can you send an example?

If it does not have microseconds and you want 0's at the end, then what you need is

out.outdate.set(in.CloseDate, "%yyyy-%mm-%dd %hh:%nn:%ss");

Note the absence of the "%ss.6". The "%ss.6" in my previous example is to indicate that the input string actually has all 6 microsecond digits.

If that doesn't help, please post some examples of the datetime string that will be your input.

Brad.

[hondaccord94]

hi brad,
my input is coming as a datetime datatype which is like this
2001-02-07-00.00.00.000000
and then in datastage job I am reading it as a varchar and according to the transformations I need to convert that varchar column to timestamp.

Thanks..


te="bcarlson"]

Hi Brad,
Can you please confirm me that this function will convert the varchar (input column )datatype to timestamp(output column)
out.outdate.set(in.CloseDate, "%yyyy-%mm-%dd %hh:%nn:%ss.000000");

Thanks

What is the exact format of your input datetime? Can you send an example?

If it does not have microseconds and you want 0's at the end, then what you need is

out.outdate.set(in.CloseDate, "%yyyy-%mm-%dd %hh:%nn:%ss");

Note the absence of the "%ss.6". The "%ss.6" in my previous example is to indicate that the input string actually has all 6 microsecond digits.

If that doesn't help, please post some examples of the datetime string that will be your input.

Brad.[/quote]

[hondaccord94]

Thanks a lot Brad !!!!!!!!