Time travel with fixed_date

My Dad is a bit of a clever-clogs at the moment.
When he confidently predicted that Chelsea would beat Barcelona in the Champions League back in April,
I put this down to an affinity with the Chelsea squad which, lets face it, is rather nearer to his age-group than those at most other big clubs.
Since then however, he’s been on a roll, culminating in his unerring prediction that Sweden would beat France in the Euros.
This almost cephalopodic accuracy ( remember Paul the Octopus) is about to be put to it’s sternest test.
According to Dad, England will beat Italy on Sunday.
Hopefully, this is just another example of his prescience rather than a case of being swept up in the mood of the moment. Roy Hodgson has gone from being “why on did they go for this old codger” to “I always knew he was the man for the job” in the space of six weeks.
English football fans, fickle? I don’t know where you get that idea.
All of which provides a fairly tenuous link to the subject at hand – testing date sensitive changes in Oracle. Continue reading

If you can’t do it in PL/SQL, do it in SQL

The tension was palpable on the bridge of The Enterprise. The hulk of the giant Teredation Cruiser filled the View Screen.

With baited breath they awaited the response of the Teredation Captain to the message they had just transmitted.

Suddenly the image on the screen changed from that of the menacing warship to Simon, Lord High Hatter of the Teredations. In a voice which hinted at an unaccustomed uncertainty, he exclaimed,
“What is this PL/SQL of which you speak ?”

OK, so maybe it wasn’t the bridge of the Enterprise so much as in the beer garden at the Nut and Squirrel. The question, however, is pretty much accurate.

In an attempt to distract himself from the sad news that Claude Gnapka had finally left Luton for Walsall, Simon posed the following programming problem :

He needed a SQL query to return the first working day on or after the 23rd of the month, together with the first working day of the following month. He wanted both dates to be returned in the same row.
The catch ? Simon works on Teradata which, owing to a glitch in the Universal Translator ( or something), doesn’t have anything like PL/SQL or T-SQL built in. Continue reading