Timestamp Conversion

[kumar66]

Hi All,

I need to convert a Timestamp value
"2009-12-13 08:46:45" to the format "13/12/2009 08:46:45."

The input datatype is timestamp and the output datatype is also timestamp.

I tried converting the input to string and converting the string to timestamp by using the function:
timestamp to string(input)
string to timestamp(input,"%dd/%mm/%yyyy %hh:%mm:%ss)

But it is not working for me.

Please Advise.

Thanks & Regards,
Kumar66

[priyadarshikunal]

where are you writing that value, a sequential file or a database?

Time you define it as time stamp the value will be in default format

[kumar66]

Hi priyadarshikunal,


Thanks for your reply. I am loading to DB2 table .

Thanks & Regards,
Kumar66

[sohasaid]

Try to use this function:

StringToTimeStamp(Convert ("-", "/", TimeStampToString<COLUMN_NAME>))

[ArndW]

Kumar66,

A timestamp (and date, time as well as all numeric datatypes) has no format; thus what you are doing when going from one timestamp to another timestamp is using up CPU but not changing any value at all. All of the 'binary' datatypes are without a format until they are assigned to a string or other output format. At that point in time a format is applied to them and they are converted from their internal representation according to that picture.

[kumar66]

[quote="sohasaid"]Try to use this function:

Hi Sohasaid,

Thanks for your input. i tried the follwoing:

StringToTimeStamp(Convert ("-", "/", TimeStampToString(DSLink2.BEGIN_DATE_TIME,"%dd/%mm/%yyyy %hh:%nn:%ss")),"%dd/%mm/%yyyy %hh:%nn:%ss")

But it throws the error:
APT_CombinedOperatorController,1: Data string '13/12/2009 08:47:32' does not match format '%yyyy/%mm/%dd %hh:%nn:%ss': the value for tag %yyyy has fewer characters than expected.

Please Advice.

Thanks,
Kumar66

[priyadarshikunal]

you haven't read the post from Arnd.

Incase you are loading it to DB2 table, you don't have to do any conversion as any database will not preserve the format.

If you are reading it from any client - you should be able to configure that from there, or change NLS timestamp format/default timestamp format of your database to see it in desired format.

You are just overkilling the system and forcing it to do extra processing, which is not needed.

[kumar66]

Hi All,

Thanks for your suggestions.

I loaded the the tiesatmp value directly to the DB2 table without changing the format .

But Db2 is storing the date formt as 13-Jan-2010 10:12:34 AM .
I need the format to be stored as 13-01-2010 10:12:34.

How can I chage the date format in DB2?

Please Advice.

Thanks & Regards,
Kumar66

[ray.wurlod]

DB2 is storing the format 011010101010100100101001010111010010110101010001000

You can change the display characteristics of your viewing tool if you want it to be displayed differently from default.

[chulett]

But DB2 is storing the date formt as 13-Jan-2010 10:12:34 AM.

No, it is not. A timestamp is a timestamp is a timestamp, it is your query tool that is enforcing the external format of that timestamp when you select it.