Author Topic: return date field as mm/dd/yy  (Read 2089 times)

jsquizz

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 573
    • View Profile
return date field as mm/dd/yy
« on: January 04, 2018, 01:08:19 PM »
Hi All, I have the most simple sql query ever I am writing to a file

select policy, rdate from ada1

as expected, rdate is being returned in the 5 digit date format. Is there any SIMPLE way to do this on the fly, that doesnt require me from writing a stored procedure

What I have:

db2 => select policy, rdate from ada1

POLICY                                             RDATE
-------------------------------------------------- ------
3741474                                            17479

What I want:

What I have:

db2 => select policy, rdate from ada1

POLICY                                             RDATE
-------------------------------------------------- ------
3741474                                            11/08/17

Thanks
#CMOD #DB2 #AFP2PDF #TSM #AIX #RHEL #AWS #AZURE #GCP #EVERYTHING

Ed_Arnold

  • Hero Member
  • *****
  • Posts: 1199
    • View Profile
Re: return date field as mm/dd/yy
« Reply #1 on: January 04, 2018, 02:10:49 PM »
I haven't tested this but...

In the latest CMOD Newsletter

http://www-01.ibm.com/support/docview.wss?uid=swg27050682

under z/OS Quick Hits is this:

Using SQL to view old time stamp values
Still running with the old TIME_STAMP and using ARSDATE to convert that to the format of the human-readable time stamp used in TIME_STAMP_DT?
Here's a quick way to view those old stamps in the new format by using SQL:
SELECT TIME_STAMP,
TIMESTAMP('1970-01-01', '00:00:00') + TIME_STAMP SECONDS
FROM hlq.SL2


I hope this helps.

Ed
« Last Edit: January 04, 2018, 02:13:08 PM by Ed_Arnold »
#zOS #ODF

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2228
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
Re: return date field as mm/dd/yy
« Reply #2 on: January 04, 2018, 09:33:36 PM »
I suspect 'SECONDS' should be 'DAYS' since 17479 is likely to be the number of days since January 1st, 1970.  :)

-JD.
IBM CMOD Professional Services: http://TenaciousConsulting.com
Call:  +1-866-533-7742  or  eMail:  jd@justinderrick.com
IBM CMOD Wiki:  https://CMOD.wiki/
FREE IBM CMOD Education & Webinars:  https://CMOD.Training/

Interests: #AIX #Linux #Multiplatforms #DB2 #TSM #SP #Performance #Security #Audits #Customizing #Availability #HA #DR

jsquizz

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 573
    • View Profile
Re: return date field as mm/dd/yy
« Reply #3 on: January 05, 2018, 07:04:20 AM »
yeah i played around with this for a few minutes last night and couldnt get it to work. Will have to mess with more today.
#CMOD #DB2 #AFP2PDF #TSM #AIX #RHEL #AWS #AZURE #GCP #EVERYTHING