When being asked to convert a day of the week, the to_date() function returns the same day ('0001-01-01 BC’) no matter which day is converted:
# select to_date(‘Monday’, ‘Day’)
'0001-01-01 BC’
# select to_date(‘Tuesday’, ‘Day’)
'0001-01-01 BC’
However, if it were to return a date that was that day of the week, it could be inverted:
# select extract(dow from '0001-01-01 BC'::date); — this date should be the result of to_date(‘Sunday’, ‘Day’)
6
# select extract(dow from '0001-01-02 BC'::date); — this date should be the result of to_date(‘Monday’, ‘Day’)
0
….
David tells this is not a bug, but it still seems like a reasonable requirement on to_date() to me. Is there some reason why this isn’t possible?