Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save BirgittaHauser/ea25c9c60e7208c0faa32f3677a61a86 to your computer and use it in GitHub Desktop.
Save BirgittaHauser/ea25c9c60e7208c0faa32f3677a61a86 to your computer and use it in GitHub Desktop.
SQL Function for converting a 7 digit numeric date (Format CYYMMDD) into a real date
CREATE OR REPLACE FUNCTION YourSchema.NUM72DATE (
DATENUM DECIMAL(7, 0) )
RETURNS DATE
LANGUAGE SQL
DETERMINISTIC
CALLED ON NULL INPUT
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
RETURN DATE ( '8888-12-31' ) ;
RETURN CAST ( DIGITS ( DEC ( CHAR ( DATENUM + 19000000 ) , 8 ) ) CONCAT '000000' AS DATE ) ;
END ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment