Monthly Archives: October 2011

Determine Day of Week from Date

A colleague had an issue today where she needed to exclude records from a query who had a date in a specific column that fell on the weekend. She knew Oracle had an easy way to get this, but wasn’t sure exactly how. She was very much correct, and I suggested adding the following to her where clause:

to_char(date_column, 'DY') not in ('SAT','SUN')

A quick way to make sure that it is calculating days correctly across years:

select dt, to_char(dt, 'DY') dow
from (
    select add_months(trunc(sysdate), 12*(1-rownum)) dt
    from dual
    connect by level <= 5
); 
DT          DOW
----------- ---
26-OCT-2011 WED
26-OCT-2010 TUE
26-OCT-2009 MON
26-OCT-2008 SUN
26-OCT-2007 FRI

I prefer using the 'DY' format mask instead of say 'D' or 'DAY'. This is because 'D' will return the number corresponding to the day of week, but I never remember which number corresponds to which day (even though it is logical, where 1 = SUN, 2 = MON ... 7 = SAT), so to me having the 3 letter representation is more self-documenting. I don't like 'DAY' because it returns the full name of the day (e.g. SATURDAY), but it is RPAD-ed to 9 characters, so all but WEDNESDAY has trailing spaces that you would have to include in your match (or trim off).

Another one of those tasks that is really easy once you know how to do it... 🙂

Second International NoCOUG SQL Challenge – Results Announced

The results for the 2nd NoCOUG SQL Challenge have been announced in the newest issue of the NoCOUG Journal. You can read more here and see my attempt here.

Here is the notice from Iggy Fernandez, President of the 2011 NoCOUG Board:

The Second International NoCOUG SQL Challenge was published on 2/13/11 in the February 2011 issue of the NoCOUG Journal (http://bit.ly/gVNZsW). SQL commands to create the data were provided at http://bit.ly/g58WVn. The challenge was to find the secret message hidden in a seemingly random collection of words. The winners are Andre Araujo (Australia), Rob van Wijk (Netherlands), and Ilya Chuhnakov (Russia.) Each winner will receive an Amazon Kindle from contest sponsor Pythian and the August Order of the Wooden Pretzel in keeping with the pronouncement of Steven Feuerstein that“some people can perform seeming miracles with straight Es-Cue-El, but the statements end up looking like pretzels created by somebody who is experimenting with hallucinogens.”

The full announcement can be read in the 100th issue of the NoCOUG Journal (http://bit.ly/rC2gRA).

Another Sad Day in IndyCar – RIP Dan Wheldon

If you are a fan of the sport, then you know what happened yesterday on the track in Las Vegas. Dan was a great driver, competitor and ambassador for the sport. He was a mainstay in the garages in Indy every year.. always with a smile on his face and making time for fans. Everyone knows this is a high-risk sport, but it doesn’t make accepting the loss of a life any easier. Too young and too talented, but more importantly leaving behind a wife and 2 young boys. My thoughts and prayers are with them as they attempt to cope with this tragedy.

Dan Wheldon at Indy - 2010

Dan Wheldon drinking the milk in Victory Circle after winning the 2011 Indy 500

You will be missed Dan.