Date and Time Conversion from Numeric Fields

I have two different column for date & time with numeric datatype as YYYYMMDD for date and HHMMSS for time, i want to convert it as  DD-MM-YYYY HH(24):MM  in db2 sql on as400 ver7.1.

I have to use same conversion in SQL in SSRS.

Any suggestion are welcome and appreciated.
Syed Shareef AhmedSenior System AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
In DB2 use the TIMESTAMP_FORMAT:
SELECT TIMESTAMP_FORMAT(CHAR(YourDateColumn) + CHAR(YourTimeColumn), 'YYYYMMDDHH24MISS') 
From YourTable

Open in new window

In SSRS use Format to create a YYYY-MM-DD HH:MM:SS string that will be converted to date using a CDate function:
CDate(Format(YourDateColumn, "0000-00-00") & " " & Format(YourTimeColumn, "00:00:00")) 
From YourTable

Open in new window

0
Syed Shareef AhmedSenior System AnalystAuthor Commented:
Thanks Chaau,

But I want the specific format as 'DD-MON-YYYYY HH24:MI', as I am new to DB2 struggling a lot to achieve it.

Appreciate a solution at the earliest for the experts.
0
chaauCommented:
In DB2 do this:
SELECT VARCHAR_FORMAT(TIMESTAMP_FORMAT(CHAR(YourDateColumn) + CHAR(YourTimeColumn), 'YYYYMMDDHH24MISS'), 'DD-MON-YYYYY HH24:MI')
From YourTable

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Syed Shareef AhmedSenior System AnalystAuthor Commented:
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.
0
Syed Shareef AhmedSenior System AnalystAuthor Commented:
Any body there to resolve my issue.
0
Dave FordSoftware Developer / Database AdministratorCommented:
You say "I am getting output as attached", but I don't see anything attached. Do you?
0
Syed Shareef AhmedSenior System AnalystAuthor Commented:
Sorry, I didn't clicked upload file option.

Please find attached
strsql-query-output-date.JPG
0
Dave FordSoftware Developer / Database AdministratorCommented:
In this type of situation, I like to build a complex expression "piece-by-piece", adding one level of complexity at a time. That way, if the results look incorrect, I know the step where it started failing.

What are the results for this?

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

Open in new window


-- DaveSlash
0
chaauCommented:
The TIMESTAMP_FORMAT needs a space:
SELECT                                                            
VARCHAR_FORMAT(TIMESTAMP_FORMAT(CHAR(ATAD01)||' '||CHAR(ATAT01),  
'YYYYMMDD HH24MISS'), 'DD-MON-YYYYY HH24:MI') FROM CTVSIT

Open in new window

BTW, can you run this query and attach the screenshot to double check the ATAD01 and ATAT01 values:
SELECT                                                            
CHAR(ATAD01), CHAR(ATAT01) 
FROM CTVSIT

Open in new window

0
Syed Shareef AhmedSenior System AnalystAuthor Commented:
Hi DaveSlash,

Thanks for the reply, please find attached of output of your query.
strsql-query-output-date-1.JPG
0
Syed Shareef AhmedSenior System AnalystAuthor Commented:
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
0
chaauCommented:
It looks like the zero values are causing the problem. Try this:
SELECT                                                            
CASE WHEN NVL(ATAD01,0) = 0 OR NVL(ATAT01,0) = 0 THEN '0' ELSE
VARCHAR_FORMAT(TIMESTAMP_FORMAT(CHAR(ATAD01)||' '||LPAD(CHAR(ATAT01), 6, '0'),  
'YYYYMMDD HH24MISS'), 'DD-MON-YYYYY HH24:MI') FROM CTVSIT

Open in new window

0
Syed Shareef AhmedSenior System AnalystAuthor Commented:
Thanks again, please find attached screen shot of error received for the query.
strsql-query-output-date-4.JPG
0
chaauCommented:
Use COALESCE in place of NVL
0
Syed Shareef AhmedSenior System AnalystAuthor Commented:
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.                      
===>
0
Syed Shareef AhmedSenior System AnalystAuthor Commented:
My current session attributes in STRSQL as attached.
strsql-query-output-date-5.JPG
0
Syed Shareef AhmedSenior System AnalystAuthor Commented:
Anybody can help me to resolve the issue.
0
Syed Shareef AhmedSenior System AnalystAuthor Commented:
Dear Experts,

Is there any solution for my issue :)
0
Dave FordSoftware Developer / Database AdministratorCommented:
I would highly recommend "building" your expression one step at a time before putting it all together.

From what I can see, the value you're passing in to TIMESTAMP_FORMAT doesn't match what it's expecting.  So, before you try to add on anything else, work out exactly what DB2 expects as an input value (and its format) to TIMESTAMP_FORMAT, and make your data match that.

HTH,
DaveSlash
0
Dave FordSoftware Developer / Database AdministratorCommented:
The following works for me, so see if you can massage your data into this format:

select TIMESTAMP_FORMAT('2015051808152000',
                        'YYYYMMDDHH24MISS')
  from sysibm.sysdummy1

TIMESTAMP_FORMAT          
2015-05-18-08.15.20.000000

Open in new window


HTH,
DaveSlash
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Syed Shareef AhmedSenior System AnalystAuthor Commented:
Thanks all for your valuable suggestions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.