As a short follow-up to a previous post regarding implicit date conversion and the NLS_DATE_FORMAT, which governs the default date format Oracle will use with the TO_DATE and TO_CHAR functions, I forgot to mention a couple of things:

  • how to determine the current NLS_DATE_FORMAT setting
  • changing the NLS_DATE_FORMAT via DBMS_SESSION

As noted in the previous post, the NLS_DATE_FORMAT setting is session modifiable via the ALTER SESSION command, e.g.:

ALTER SESSION SET nls_date_format = 'dd/mm/yyyy'

In order to work out what NLS_DATE_FORMAT your session has been configured with then the NLS_SESSION_PARAMETERS view will provide this information:

SELECT value
FROM   nls_session_parameters
WHERE  parameter = 'NLS_DATE_FORMAT'

If you wanted to work out what the format was for the database, as opposed to your session, then you can query the NLS_DATABASE_PARAMETERS view instead:

SELECT value
FROM   nls_database_parameters
WHERE  parameter = 'NLS_DATE_FORMAT'

NLS_DATE_FORMAT may also be set via the DBMS_SESSION.SET_NLS routine. One advantage to setting it via DBMS_SESSION is that it can done from within PL/SQL without having to go through the messy business of dynamic SQL as ALTER SESSION is not directly callable from PL/SQL. The call is simply:

dbms_session.set_nls ('nls_date_format','''dd/mm/yyyy''')

Note, for string NLS parameter values the embedded quotes need to be contained in the string argument to the procedure call, hence the 3 single quotes on either side of the format parameter.

The following is a basic illustration of the above points, setting the NLS_DATE_FORMAT via DBMS_SESSION and querying the session and database properties:

SQL> EXEC dbms_session.set_nls ('nls_date_format','''dd/mm/yyyy''')

PL/SQL procedure successfully completed.

  2  FROM   nls_session_parameters
  3  WHERE  parameter = 'NLS_DATE_FORMAT'
  4  /


  2  FROM   nls_database_parameters
  3  WHERE  parameter = 'NLS_DATE_FORMAT'
  4  /


5 thoughts on “Determining NLS_DATE_FORMAT

  1. I am trying to find all occurrences (including those that span midnight) where shift_brk_start & shift_brk_end fall on or within leave_start_time and leave_end_time

    1. the following feilds are assigned a date time format by default.

    2. the date associated with following is the actual work date of an employee

    3. the date associated with sb.SHFTBRK_DEF_START is the shift creation date, hence sb.SHFTBRK_DEF_START date will never match date for wd.WRKD_START_TIME, wd.WRKD_END_TIME

    4. to get shift break end time i need to add

    example of output:

    3100476801 Tania, Ball 01.08.2013 21:30 00:51 21:30 21:45 15
    3100476801 Tania, Ball 05.08.2013 21:30 00:51 21:30 21:45 15
    3100476801 Tania, Ball 06.08.2013 21:30 00:51 21:30 21:45 15

    the issue is that my script is still returning values I dont need to see, for example below the BREAK time (11:00-11:30) is outside of the leave time ( 11:30-13:30) therefore i
    do not need to see this record

    2504358201 Samantha, John 01.08.2013 11:30 13:30 11:00 11:30 30


    e.EMP_NAME as APS,
    e.EMP_FULLNAME as EMP_Name,
    TO_CHAR(ws.WRKS_WORK_DATE,’’) as Work_Date,
    to_char((wd.WRKD_START_TIME), ‘hh24:mi’) as Leave_Start_Time,
    to_char((wd.WRKD_end_TIME), ‘hh24:mi’) as Leave_End_Time,
    to_char(sb.SHFTBRK_DEF_START,’hh24:mi’) as shift_brk_Start,
    to_char(sb.SHFTBRK_DEF_START + (sb.SHFTBRK_MINUTES+1)*.000694, ‘hh24:mi’) as shift_brk_end,
    sb.SHFTBRK_MINUTES as shift_break_minutes

    workbrain.WORK_SUMMARY ws,
    workbrain.employee e,
    workbrain.work_detail wd,
    workbrain.TIME_CODE tc,
    workbrain.shift s,
    workbrain.shift_break sb,
    workbrain.EMPLOYEE_SCHEDULE es


    and wd.WRKD_MINUTES = current_date

    and ws.WRKS_WORK_DATE = es.WORK_DATE
    and ws.emp_id = es.emp_id

    and ws.shft_id = es.EMPSKD_ACT_SHIFT_ID
    and s.SHFT_ID = sb.SHFT_ID

    and (
    (to_date(to_char(wd.WRKD_START_TIME, ‘dd-mm-yyyy’) || ‘ ‘ || to_char(sb.SHFTBRK_DEF_START,’hh24:mi’), ‘dd-mm-yyyy hh24:mi’) wd.WRKD_START_TIME) OR
    (to_date(to_char(wd.WRKD_START_TIME, ‘dd-mm-yyyy’) || ‘ ‘ || to_char(sb.SHFTBRK_DEF_START,’hh24:mi’), ‘dd-mm-yyyy hh24:mi’) wd.WRKD_end_TIME) OR
    (to_date(to_char(wd.WRKD_START_TIME, ‘dd-mm-yyyy’) || ‘ ‘ || to_char(sb.SHFTBRK_DEF_START,’hh24:mi’), ‘dd-mm-yyyy hh24:mi’) > wd.WRKD_START_TIME and to_date(to_char(wd.WRKD_START_TIME, ‘dd-mm-yyyy’) || ‘ ‘ || to_char(sb.SHFTBRK_DEF_START,’hh24:mi’), ‘dd-mm-yyyy hh24:mi’) + (sb.SHFTBRK_MINUTES+1)*.000694 < wd.WRKD_end_TIME) OR
    (to_date(to_char(wd.WRKD_START_TIME, 'dd-mm-yyyy') || ' ' || to_char(sb.SHFTBRK_DEF_START,'hh24:mi'), 'dd-mm-yyyy hh24:mi') = wd.WRKD_END_TIME and to_date(to_char(wd.WRKD_START_TIME, 'dd-mm-yyyy') || ' ' || to_char(sb.SHFTBRK_DEF_START,'hh24:mi'), 'dd-mm-yyyy hh24:mi') + (sb.SHFTBRK_MINUTES+1)*.000694 = wd.WRKD_end_TIME)

    group by
    to_char(sb.SHFTBRK_DEF_START + (sb.SHFTBRK_MINUTES+1)*.000694, 'hh24:mi'),

    • Hi Despina,

      It seems that you have 2 sets of start and end date/time fields: leave and break. If you want to find those occurrences where there is an intersection of the two time periods then you can use the following:

      shift_brk_start BETWEEN leave_start_time AND leave_end_time
      leave_start_time BETWEEN shift_brk_start AND shift_brk_end

      I’ve described the above in terms of the data concepts and not the underlying formulae. If the break details you have are only provided in terms of time with no date component then can you work out the break start using a formula of:

      — get the start of the break for the leave date
      to_date(to_char(wd.WRKD_START_TIME, ‘dd-mm-yyyy’) || ‘ ‘ || to_char(sb.SHFTBRK_DEF_START,’hh24:mi’), ‘dd-mm-yyyy hh24:mi’)
      — if the break begins before the start of the leave date then move it to the next day
      CASE WHEN (TO_NUMBER(shift_brk_start,’hh24mi’) < TO_NUMBER(leave_start_time,'hh24mi'))
      THEN 1

      Does this help you?


      • thankyou so much, i fainally got it working as follows:


        (to_date(to_char(wd.WRKD_START_TIME, ‘’) || ” || to_char(sb.SHFTBRK_DEF_START, ‘hh24:mi’), ‘ hh24:mi’) (wd.WRKD_start_TIME))—1

        OR(to_date(to_char(wd.WRKD_START_TIME, ‘’) || ” || to_char(sb.SHFTBRK_DEF_START, ‘hh24:mi’), ‘ hh24:mi’) =(wd.WRKD_START_TIME) and
        to_date(to_char(wd.WRKD_START_TIME, ‘’) || ” || to_char(sb.SHFTBRK_DEF_START + (sb.SHFTBRK_MINUTES+1)*.000694, ‘hh24:mi’), ‘ hh24:mi’) = (wd.WRKD_end_TIME))–4

        OR(to_date(to_char(wd.WRKD_START_TIME, ‘’) || ” || to_char(sb.SHFTBRK_DEF_START, ‘hh24:mi’), ‘ hh24:mi’) =(wd.WRKD_START_TIME) and
        to_date(to_char(wd.WRKD_START_TIME, ‘’) || ” || to_char(sb.SHFTBRK_DEF_START + (sb.SHFTBRK_MINUTES+1)*.000694, ‘hh24:mi’), ‘ hh24:mi’) > (wd.WRKD_end_TIME))—5

        OR(to_date(to_char(wd.WRKD_START_TIME, ‘’) || ” || to_char(sb.SHFTBRK_DEF_START, ‘hh24:mi’), ‘ hh24:mi’) =(wd.WRKD_START_TIME) and
        to_date(to_char(wd.WRKD_START_TIME, ‘’) || ” || to_char(sb.SHFTBRK_DEF_START + (sb.SHFTBRK_MINUTES+1)*.000694, ‘hh24:mi’), ‘ hh24:mi’) (wd.WRKD_START_TIME) and
        to_date(to_char(wd.WRKD_START_TIME, ‘’) || ” || to_char(sb.SHFTBRK_DEF_START + (sb.SHFTBRK_MINUTES+1)*.000694, ‘hh24:mi’), ‘ hh24:mi’) (wd.WRKD_START_TIME) and
        to_date(to_char(wd.WRKD_START_TIME, ‘’) || ” || to_char(sb.SHFTBRK_DEF_START, ‘hh24:mi’), ‘ hh24:mi’) (wd.WRKD_end_TIME))—2


  2. Hii Everyone, i just read this discussion forum and it’s great, but can anyone tell me how to find out the date format of operating system. The date format of operating system is different from date format of database.
    The date format of operating system can be found out by going to control_panel>Region and language>formats( which is actually the name of tab)>Long Date.
    Now in the long date, you may see the date format which the operating system is showing.
    So please give me any query via which the date format of operating system is shown??????

    • Hi Ankit,

      You’re out of luck. Oracle doesn’t provide a facility to read the information you’re after. You can probably do this in Java and expose it via a Java stored procedure. The documentation has the details of how to do this kind of thing, see:

      However, I’m not sure why you need to do this. The OS date format is not something that would be changed on a production system so it’s static and your application shouldn’t need to continually read it. An external application could write to format to an application configuration table when the application is installed and that would be it. Even if it could be read via Java you will probably find that it does not perform very well and, depending on how often it is referenced, you may need to cache it locally in any case.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s