CODEWITHSUNDEEP
sqldb2
Ashok.N
Ashok.N

Reputation: 1391

SQL Query to convert TIMESTAMP to String while selecting the columns

I am using DB2 database. I have a table and it has two columns as below:

Table name is sample: Below are the columns:

CRDATTIM TIMESTAMP NOT NULL , 

RECORDCD CHAR(1) CCSID 37 NOT NULL;

The above table has the below values:

   CRDATTIM                                   RECORDCD 

2014-08-08 12:12:39.621060                  O   
2014-08-27 07:01:46.814080                  T   
2014-08-27 06:41:47.404080                  T   
2014-08-27 07:14:47.871080                  T   
2014-08-28 02:23:50.390080                  T   
2014-08-28 06:27:58.298080                  T   
2014-09-02 08:57:37.607080                  T

Now I want to select all the columns of the table sample(select * from sample)by converting the values under CRDATTIM column to string, not as TIMESTAMP.

For example the value under the column CRDATTIM 2014-08-08 12:12:39.621060 should be returned as 2014-08-08-12.12.39.621060.

Similarly

2014-08-27 07:01:46.814080 should be returned as 2014-08-27-07.01.46.814080

and so on

Any thoughts on how to achieve this?

Upvotes: 2

Views: 62254

Answers (3)

Jesse
Jesse

Reputation: 3393

According to DB2 doc, you can use varchar_format() or to_char():

select varchar_format (CURRENT_TIMESTAMP, 'YYYY-MM-DD') from SYSIBM.SYSDUMMY1;

Upvotes: 0

Ashok.N
Ashok.N

Reputation: 1391

Aniket V's suggestion helped me in figuring out the solution.

Try nested functions in the SQL for CRDATTIM field. Using TO_CHAR function to get the timestamp as a string and then apply REPLACE function to replace the space in the string with a hyphen

The below query worked for me to convert Timestamp to specified string format.

select  TO_CHAR(CRDATTIM,'YYYY-MM-DD-HH24.MI.SS.FF6') as CRDATTIM ,recordcd from sample  ;

Upvotes: 7

mao
mao

Reputation: 12267

Answer may depend on your DB2-version and operating-system platform for the DB2-server, but try this: 

select timestamp_format(crdattim,'YYYY-MM-DD-HH24.MI.SS.NNNNNN'),recordcd from sample

You can also try to_char() instead of timestamp_format().

Upvotes: 0

Related Questions