This content has been marked as final. Show 6 replies
Peter,1 person found this helpful
From the SQL Reference (Format Models):
The datetime format element D returns the number of the day of the week (1-7). The day of the week that is numbered 1 is specified implicitly by the initialization parameter NLS_TERRITORY.From the SQL Reference (TO_CHAR(datetime)):
The 'nlsparam' argument specifies the language in which month and day names and abbreviations are returned. This argument can have this form:'NLS_DATE_LANGUAGE = language' >
So 'D' is controlled by NLS_TERRITORY and the TO_CHAR function can only handle NLS_DATE_LANGUAGE.
So it looks like ALTER SESSION is your only way out here.
1 person found this helpful
Great! - But I don't like to alter session like thisWell, you could always
SQL> select t.value 2 , case 3 when t.value != 'DENMARK' 4 then to_char(sysdate-1, 'd') 5 else to_char(sysdate, 'd') 6 end 7 from nls_session_parameters t 8 where t.parameter = 'NLS_TERRITORY'; VALUE C ---------------------------------------- - AMERICA 1
Did not read far enough. It is even clearer in the 11.2 reference where it for D element says
Day of week (1-7). This element depends on the NLS territory of the session.In 10.2 reference it is just
Day of week (1-7).@Hoek, wouldn't that be great, but unfortunately it can be all sorts of things "Around here" not all are Danish ;)
I have decided to use to_char( , 'day', 'nls_language='), instead and then sniff for saturday/sunday.
Maybe1 person found this helpful
SQL> select case to_char (sysdate, 'FmDay', 'nls_date_language=english') when 'Monday' then 1 when 'Tuesday' then 2 when 'Wednesday' then 3 when 'Thursday' then 4 when 'Friday' then 5 when 'Saturday' then 6 when 'sunday' then 7 end d from dual / D ---------- 1 1 row selected.
What is the problem?
Do you just want an expression that, given a DATE, will return an integer (1 for Monday, ..., 7 for Sunday), independent of NLS settings?
1 + TRUNC (dt) - TRUNC (dt, 'IW')
return an integer (1 for Monday, ..., 7 for Sunday), independent of NLS settings?That's right Frank,
and cool, thank you!
@Michaels, thanks for reminding me about fill mode, which I of course forgot about it