format the type date

[stressman]

how can i format the type date or timestamp to be in the format as follow
'20020415 06:00'
under Oracle we need to alter session but under DB2 what is the solution

thanks

 

[MarcLodge]

Stressman,
You cannot format a timestamp. Under DB2 it has one format only YYYY-MM-DD-hh.mm.ss.zzzzzz

For dates fields, you have more control, you can for instance SELECT CHAR(DATE_COL,format)

where the first argument is the column name and the second argument is the format. The following formats are supported:

Name Layout Example
ISO yyyy-mm-dd 1987-10-12
USA mm/dd/yyyy 10/12/1987
EUR dd.mm.yyyy 12.10.1987
JIS yyyy-mm-dd 1987-10-12

You can also have a LOCAL format which is a user written exit to define the date exactly how you wish.
HTH Marc

 

[stressman]

Thanks MarcLodge
would you like more developp your last sentence what do you want to say with have a local format to have the date exactly like i whish 'yyyymmdd hh:mm'

 

[MarcLodge]

I've not made a user exit ie LOCAL, but know it can be done, but only on a date field, NOT a timestamp.
I'd have a word with your DBA for further info as I think it's an installation thing. If you get no joy, come back to us.
Marc

 

[gregsimpson]

Stressman,

an alternative way for you to do this is to define the field as Timestamp as would be done traditionally. However on this Timestamp you put an exit routine known as a FIELDPROC.

A FIELDPROC transforms data on insertion and converts the data to its original format on subsequent retrieval.

Therefore you could give it '20020415 06:00' in your code

Let the FIELDPROC convert it to

'2002-04-15-06.00.00.000000',

storing it on the database as such.

Then when you query it it will once again be returned as

'20020415 06:00'