Skip to content

Instantly share code, notes, and snippets.

@phpdave
Created December 9, 2016 15:23
Show Gist options
  • Save phpdave/7382b40dc4838a96a7e24ecd4b250ea9 to your computer and use it in GitHub Desktop.
Save phpdave/7382b40dc4838a96a7e24ecd4b250ea9 to your computer and use it in GitHub Desktop.
Figuring out if SQL is slow because of date manipulation into YYYYMMDD format and whats the best way to approach this.
--TRANSDATE is Decimal(6,0) with no leading zero in MDDYY format in a 4 million record file
--i.e. 10199, 10116, 120816
WITH TABLE_TRANSACTION_WITH_YYYYMMMD_DATE as
(
SELECT a.*,
CASE
WHEN SUBSTR(DIGITS(TRANSDATE),5,2)>70 THEN '19' || SUBSTR(DIGITS(TRANSDATE),5,2) || SUBSTR(DIGITS(TRANSDATE),1,4)
ELSE '20' || SUBSTR(DIGITS(TRANSDATE),5,2) || SUBSTR(DIGITS(TRANSDATE),1,4)
END AS TRANSDATEYYYYMMDD
FROM MYLIB.TRANSACTIONS a
)
SELECT *
FROM TABLE_TRANSACTION_WITH_YYYYMMMD_DATE
WHERE TRANSDATEYYYYMMDD >= 20140101 AND
TRANSDATEYYYYMMDD <= 20151231
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment