From e9506234be916a9a719b844c7005c9cfa8251edf Mon Sep 17 00:00:00 2001 From: Jason Etheridge Date: Mon, 11 Sep 2017 03:34:18 -0400 Subject: [PATCH] migration_tools.attempt_sierra_timestamp Signed-off-by: Jason Etheridge --- sql/base/base.sql | 18 ++++++++++++++++++ 1 files changed, 18 insertions(+), 0 deletions(-) diff --git a/sql/base/base.sql b/sql/base/base.sql index 1acae1d..de80f0f 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -840,6 +840,24 @@ CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS D END; $$ LANGUAGE PLPGSQL STRICT STABLE; +CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$ + DECLARE + attempt_value ALIAS FOR $1; + fail_value ALIAS FOR $2; + output TIMESTAMP; + BEGIN + output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP; + RETURN output; + EXCEPTION + WHEN OTHERS THEN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;' + LOOP + RETURN output; + END LOOP; + END; +$$ LANGUAGE PLPGSQL STRICT STABLE; + CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$ DECLARE attempt_value ALIAS FOR $1; -- 1.7.2.5