Bill Behr FLYERFIGHTER66

IT Manager / Programmer / IBM i developer / Web Developer / etc.

BirgittaHauser / CvtNum2Date.SQL

Created October 24, 2019 10:43

SQL Function to convert a numeric date into a real date

	-- 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 / JSON_TABLE and survival tips for shredding JSON with SQL

Last active August 12, 2024 08:06

This example shows how to overcome what seems to be commonplace: JSON Web Services that return an invalid JSON document.

	-- 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 / Dates and TIMESTAMP_FORMAT

Last active May 2, 2024 07:55

Formatting date data into true date and time date types

	-- Author: Scott Forstie
	-- Email: [email protected]

	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