Date and Time Conversion from Numeric Fields

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi chaau,

Thanks again,

When I run a query below as suggested

SELECT
VARCHAR_FORMAT(TIMESTAMP_FORMAT(CHAR(ATAD01)||' '||CHAR(ATAT01),
'YYYYMMDDHH24MISS'), 'DD-MON-YYYYY HH24:MI') FROM CTVSIT

I am getting output as attached, your further advice would be highly appreciated.

ASKER

Any body there to resolve my issue.

Member_2_2484401

You say "I am getting output as attached", but I don't see anything attached. Do you?

ASKER

Sorry, I didn't clicked upload file option.

Please find attached
strsql-query-output-date.JPG

SOLUTION

Member_2_2484401

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi DaveSlash,

Thanks for the reply, please find attached of output of your query.
strsql-query-output-date-1.JPG

ASKER

Hi Chaau,

Thanks again for your reply, please find attached of output of screen shot for your queries respectively,
strsql-query-output-date-2.JPG
strsql-query-output-date-3.JPG

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thanks again, please find attached screen shot of error received for the query.
strsql-query-output-date-4.JPG

Use COALESCE in place of NVL

ASKER

Dear Chaau,

Thanks again, I am giving you so much trouble as I am new to db2. As suggested I used COALESCE instead of NVL and also I have to use RIGHT & REPEAT, see below the query,

SELECT CASE WHEN COALESCE(ATAD01,0) = 0 OR COALESCE(ATAT01,0) = 0
THEN '0'
ELSE VARCHAR_FORMAT(TIMESTAMP_FORMAT(CHAR(ATAD01)||
' '||RIGHT(REPEAT('0',6)||CHAR(ATAT01), 6),
'YYYYMMDD HH24MISS'), 'DD-MON-YYYYY HH24:MI') END FROM CTVSIT

I get following error, please help me out experts.

Type SQL statement, press Enter.
> SELECT CASE WHEN COALESCE(ATAD01,0) = 0 OR COALESCE(ATAT01,0) = 0
THEN '0'
ELSE VARCHAR_FORMAT(TIMESTAMP_FORMAT(CHAR(ATAD01)||
' '||RIGHT(REPEAT('0',6)||CHAR(ATAT01), 6),
'YYYYMMDD HH24MISS'), 'DD-MON-YYYYY HH24:MI') END FROM CTVSIT
Query cannot be run. See lower level messages.
===>

ASKER

My current session attributes in STRSQL as attached.
strsql-query-output-date-5.JPG

ASKER

Anybody can help me to resolve the issue.