In a Mental Status Examination a psychiatrist will assess the patient, often to decide whether they should be admitted to hospital. Quoting the article linked above, “Orientation is assessed by asking the patient … what time it is (time, day, date)”. So the patient is asked what day of the week it is.
What if the psychiatrist were to ask instead, “What is the number of today, based upon where it falls among the days of the week”? That might indicate that the doctor himself was under too much pressure and needed to take some time off, and it would also be quite unfair to the patient. We need to know what day it is, we often need to know when the week starts or finishes, but we don’t usually ask ourselves what number the day is.
There are database functions that provide the day number: in Sql Server it’s: SELECT DATEPART(weekday, SYSDATETIME()), and in Oracle it would be TO_CHAR('d', SYSDATE), which you can cast to a number of course. Do we ever need to know it? If today is day 6, what does that mean unless we also know which day is number 1? If the numbering follows the ISO standard, “day 1” will be Monday and today is Saturday. But I’ve just queried Sql Server like this:
SELECT DATEPART(weekday, SYSDATETIME()) As NumberOfToday, DATENAME(weekday, SYSDATETIME()) As NameOfToday
and I got this:
NumberOfToday NameOfToday
------------- ------------------------------
6 Friday
(1 row(s) affected)
Don’t worry we can sort that out:
SET DATEFIRST 1
Now it’s all good, we’re following the ISO standard:
NumberOfToday NameOfToday
------------- ------------------------------
5 Friday
(1 row(s) affected)
Oh, but there are a few questions: does the DATEFIRST setting persist across sessions? If not, how can we make it permanent? What happens when Sql Server is re-started, or when it’s rebuilt? Will the setting be affected by regional settings on the server or on the client? These are all questions that can be answered, but what’s the point? We already have the date, but instead of deriving the weekday directly -and deterministically- from that, we go to a non-deterministic function first and then work backwards.
Perhaps by now you’re beginning to wonder whether a “day number” function is any use at all. In fact I’m hoping you’re already convinced that it isn’t, and thinking that I should stop going on about it. Well I will, but as you can probably tell I’ve got scars (caused by one machine which had U.S. regional settings) so I think you should make allowances. After the redesign, my application figured out what day it was directly, and from that point we didn’t care about database or machine settings. You can do this in Sql Server with SELECT LEFT(DATENAME(weekday, SYSDATETIME()), 3) and in Oracle with TO_CHAR('DY', SYSDATE).
I don’t believe there’s any case where knowing the number of the day puts you in a better position than knowing its name. If you need to loop through the days in the week, please handle in the code where you’ll start and how you’ll get the ordering right. And try not to use =WEEKDAY(TODAY()) in MS Excel either.
I’ve used them, you’ve probably used them too – but next time there’s an opportunity to calculate a day number: Just Say No! NO!!! NOOOO!!!!!!!
I’m so sorry… I just had to get all that out… Yes, I feel better now…Thank you… Good-bye.
blog.philipsymons.com 