CodeSOD: A Convoluted Time Machine

The web team and the JDE development team rarely see eye-to-eye in Cho's company. Cho, the JDE developer, worked in a world full of Oracle databases and important financial records. Andrew, a web developer, worked in a world full of MS-SQL and sales appointments. So when Andrew asked Cho to put together a job that would remove debt records older than six years so they'd stop showing up in his sales reports, he figured she had things well in hand.

"Six years?" mused Cho. "I'll have to build a custom function to figure out the start and end dates... I'll get back to you."

Two weeks after launch, several production incidents had been traced back to this new functionality. Of course, Cho had gone on vacation, so it was up to Andrew to dive into the seedy world of Oracle databases and debug the function...

 /* Cast today's date to a character value */ v_end_date_char := TO_CHAR ( TRUNC ( SYSDATE ) , 'MM/DD/YYYY' ); /* WI requires removal of debt 6 years back, so obtain the year for the purpose of building a 6 year old date */ v_year := TO_CHAR ( TO_NUMBER ( SUBSTR ( v_end_date_char, 7, 4 ) ) - 6 ); /* Check for leap year */ IF ( ( TO_NUMBER ( SUBSTR ( v_end_date_char, 1, 2 ) ) = 2 ) AND ( TO_NUMBER ( SUBSTR ( v_end_date_char, 4, 2 ) ) = 29 ) ) THEN /* Adjust for a leap year and build date six years ago */ v_end_date_char := '02/28/' || v_year; ELSE /* Build the two digit day of the year */ v_day := TO_CHAR ( TO_NUMBER ( SUBSTR ( v_end_date_char, 4, 2 ) ) ); /* Build the two digit month of the year */ v_month := TO_CHAR ( TO_NUMBER ( SUBSTR ( v_end_date_char, 1, 2 ) ) ); /* Build the character representation of the date six years ago */ v_end_date_char := v_month || '/' || v_day || '/' || v_year; END IF; /* Build date for query to compare with duedt field */ v_end_date := TO_DATE ( v_end_date_char, 'MM/DD/YYYY' ); 

Andrew stared at the function for a solid thirty minutes before reaching for the delete key. A quick Google search revealed a much cleaner way of getting the date:

 add_months(sysdate, -72) 

[Advertisement] Scout is the best way to monitor your critical server infrastructure. With over 90 open source plugins, robust alerting, beautiful dashboards and a 5 minute install - Scout saves youvaluable engineering time. Try the server monitoring you'll today.Your first 30 days are free on us. Learn more at Scout.