Timestamp

[shilpa79]

this column is not commin from the source

I have to populate the crreationtimestamp column
like "2007-03-20T09:21:22"

for this column sql type is timestamp

Thanks,

[chulett]

Sure, go ahead.

Is there a question here you meant to ask?

[DSguru2B]

Yea, I didnt get the question either

[Madhusv]

I think you want pass this information to MS-SQL server.
you can use Convert(' ','T', CurrentTimestamp()) in varchar field,
function to replace space generated from the Datastage and while populating make the target field type as varchar(). This will not affect loading.

[chulett]

You use the 'T' for MS-SQL? I've only seen it for XML timestamps.

We can sit here and guess all day. I prefer to wait for the Shilpa to come back and clarify what exactly is needed. And what the target is.

[shilpa79]

You use the 'T' for MS-SQL? I've only seen it for XML timestamps.

We can sit here and guess all day. I prefer to wait for the Shilpa to come back and clarify what exactly is needed. And what the target is.

Sorry for not getting back soon

My question was I need to populate the timestamp with the above format
'T' should come inbetween date and time.

I am genertaing XML file and there is no source column mapping to the target .

Yes, ur right , this is for XML timestamps for header information.
If I would like to do the same conversion in PX its the same or different.

Thanks as lot for the quick response.

[DSguru2B]

You will have to treat it as a varchar else it will not accept it as a timestamp, as far as i know.

[chulett]

What do you need in this timestamp? One consistent value over the run of the job? Something that changes over the course of the run? Same basic answer but with a slightly different approach.

In any case, you'll need to build a routine to get the system date/time and put it in a timestamp format. Then for an 'XML timestamp' you'll need to ensure there's a 'T' rather than a space between the two components. You can either build it that way or build a 'normal' timestamp and then swap the space for a 'T' - Madhusv has shown one way to do that.

Then define a stage variable and call the routine in the Initial Value of the variable if you want it to be a constant. Or use it directly in the derivation of your output column for a changing value.

[shilpa79]

I tried and its working fine in server and now there requirements are changed they would like to test me in PX.

I am not able to fix 'T' inbetween date and time in PX

Thanks,

[DSguru2B]

As I suspected, it wont accept T in timestamp. Treat it as varchar.

[shilpa79]

As I suspected, it wont accept T in timestamp. Treat it as varchar.

I did I have changed to varchar and try to use the fuction convert but still its not working in PX

[DSguru2B]

What exactly is not working

[shilpa79]

What exactly is not working

I am not able to populate the same format of date in PX. Iwas able to do it in Server using Iconv and Oconv and convert functions

2007-03-20T09:21:22

I changed the sqltype to varchar but still its not populating T inbetween date and time
I have used simple way to do that CurrentDate():'T':CurrentTime()

Thanks,

[DSguru2B]

Thats what your doing wrong. CurrentDate() and CurrentTime() will return date and time and not a string. Try something like

Code: Select all

DateToString(CurrentDate()):'T':TimeToString(CurrentTime())

[shilpa79]

Thats what your doing wrong. CurrentDate() and CurrentTime() will return date and time and not a string. Try something like

Code: Select all

DateToString(CurrentDate()):'T':TimeToString(CurrentTime())

It worked Guru. :D

Thanks to all of them for helping me out to resolve the issue