BirgittaHauser

-- 1. CvtNumYYYYMMDD2Date User Defined Function
--    Convert a numeric date in the format YYYYMMDD into a real date
Create or Replace Function YourSchema.CvtNumYYYYMMDD2Date (                        
       ParDateNum Dec(8, 0) )                     
       Returns DATE                              
       Language SQL                              
       Specific YourSchema.CvtNumYYYYMMDD2Date               
       Not Deterministic                         
       Reads SQL Data                            
       Called on NULL Input                      

forstie

-- This fails to return data....why?
SELECT cusip, issueDate, bidToCoverRatio
    FROM JSON_TABLE(
      SYSTOOLS.HTTPGETCLOB('https://www.treasurydirect.gov/TA_WS/securities/announced?format=json&type=FRN&pagesize=5', null),
      '$.root[*]'   
      COLUMNS(cusip VARCHAR(10)      PATH '$.cusip',
              issueDate Timestamp    PATH '$.issueDate',
              bidToCoverRatio double PATH '$.bidToCoverRatio') 
    ) AS X;
stop;

forstie

-- Author: Scott Forstie
-- Email:  forstie@us.ibm.com

create or replace variable coolstuff.decdate dec(6,0);
set coolstuff.decdate = '190718';
-- July 18, 2019 (yes, really!)
values timestamp_format(varchar(coolstuff.decdate), 'YYMMDD'); 

-- Wow
-- Yowza