This discussion is archived
9 Replies Latest reply: Dec 4, 2012 3:24 AM by 976640 RSS

How to get Date format for a given date?

976640 Newbie
Currently Being Moderated
May be it's inane to ask, but what i am aware of the Date,
what get displayed on client - its just the way client treats the date.
Right?
So what if one need to check the format set at server level for the date.
Or there is need to write a common code which will tell the date with their format
on server it is executed.

This requirement came in my mind while using extract method.
Actually, i wanted to do date manipulation on back end side.
Lets say the time stamp is 04:12:2012 03:05:16.
Now if i add 100 minutes to it, the result will be-
04:12:2012 04:45:16.

This is the requirement. May be there can be better way to do this,
but this is the way i can think of now.

Thanks.
  • 1. Re: How to get Date format for a given date?
    Purvesh K Guru
    Currently Being Moderated
    This way:
    alter session set nls_date_format = 'DD-Mon-YYYY HH24:MI:SS';
    
    with data as
    (
      select to_date('04-12-2012 03:05:16', 'DD-MM-YYYY HH24:MI:SS') col from dual
    )
    select col, col + 100/(24*60) mod_time
      from data;
    
    COL                       MOD_TIME                  
    ------------------------- ------------------------- 
    04-Dec-2012 03:05:16      04-Dec-2012 04:45:16    
    PS:- the Alter statement is merely for display purpose.
  • 2. Re: How to get Date format for a given date?
    KeithJamieson Expert
    Currently Being Moderated
    its quite staightforward once you know that when you add 1 to a date you add 1 day

    so if there are 24 hours in a day , you need to add 1/24

    so now if you want to add minutes , there are 60 minutes in an hour so thats 1 /(24*60)

    and for seconds there are 60 secs in a minute (3600 in an hour)
    so thats 1/(24*60*60)

    So now we have that, we can add 100 minutes as follows

    date + 100*(1/24*60)

      1  declare
      2  v_date date := to_date('04:12:2012 04:45:16','DD:MM:YYYY HH24:MI:SS');
      3  begin
      4  v_date := v_date + 100*(1/(24*60));
      5  dbms_output.put_Line(to_char(v_date,'DD-MM-YYYY HH24:MI:SS'));
      6* end;
    SQL> /
    04-12-2012 06:25:16
    
    PL/SQL procedure successfully completed.
    
    SQL>
  • 3. Re: How to get Date format for a given date?
    ascheffer Expert
    Currently Being Moderated
    And or the people who don't know that "1 is one day" Oracle has invented the interval:
    declare
      v_date date := to_date('04:12:2012 04:45:16','DD:MM:YYYY HH24:MI:SS');
    begin
      v_date := v_date + numtodsinterval( 100, 'MINUTE' );
      dbms_output.put_Line(to_char(v_date,'DD-MM-YYYY HH24:MI:SS'));
    end;
  • 4. Re: How to get Date format for a given date?
    976640 Newbie
    Currently Being Moderated
    Thank you very much for responding early.
    Well, i got to know how to add minutes and other similar stuff.
    But original question is still unanswered - how to get format of date which is being used by server.

    select systimestamp from DUAL;

    in this query, result returns what format, this is i need to know.

    Thanks.
  • 5. Re: How to get Date format for a given date?
    theoa Pro
    Currently Being Moderated
    To get the NLS parameters for the current session (which are what you probably want) and instance:
    SELECT * FROM nls_session_parameters;
    SELECT * FROM nls_instance_parameters;
    I just don't see why you would need the database NLS settings.
    Good code is not dependent on the NLS settings (except for display of course).
  • 6. Re: How to get Date format for a given date?
    ascheffer Expert
    Currently Being Moderated
    That is something you don't need to know:
    you have a string containing a date or timestamp, and then you need to know the format of that string and not the date fornat used by the server,
    or you have a date or timestamp, and then you can add minutes to it without knowing the format as shown before.
  • 7. Re: How to get Date format for a given date?
    976640 Newbie
    Currently Being Moderated
    Thank you for the response.
    I will keep my code independent of server format.
  • 8. Re: How to get Date format for a given date?
    BluShadow Guru Moderator
    Currently Being Moderated
    973637 wrote:
    Thank you very much for responding early.
    Well, i got to know how to add minutes and other similar stuff.
    But original question is still unanswered - how to get format of date which is being used by server.

    select systimestamp from DUAL;

    in this query, result returns what format, this is i need to know.

    Thanks.
    That format depends on the default NLS_DATE_FORMAT parameter for the database, and that is determined by whoever installs the database and what language/nationality settings they chose during installation, or they may have changed the setting after installation so the default is different. That parameter can also be set at a session level...
    SQL> select sysdate from dual;
    
    SYSDATE
    --------------------
    04-DEC-2012 09:55:37
    
    SQL> alter session set nls_date_format='DD/MM/YYYY';
    
    Session altered.
    
    SQL> select sysdate from dual;
    
    SYSDATE
    ----------
    04/12/2012
    ... but that is essentially changing a local client setting.

    Internally dates are represented as a series of bytes, and that depends on whether you're referring to a date that is literally queried or a date stored on a table.
    SQL> create table x (x date);
    
    Table created.
    
    SQL> insert into x (x) values (sysdate);
    
    1 row created.
    
    SQL> select dump(x), dump(sysdate) from x;
    
    DUMP(X)
    ------------------------------------------------------
    DUMP(SYSDATE)
    ------------------------------------------------------
    Typ=12 Len=7: 120,112,12,4,10,57,19
    Typ=13 Len=8: 220,7,12,4,9,56,53,0
    
    
    SQL>
    The stored format is of type 12 and uses 7 bytes to represent the date, whereas a queried literal date is type 13 and uses 8 bytes to represent it. It's a minor difference, and relates to the underlying C code and how it represents dates for the queried literal ones as opposed to how Oracle is storing them on tables.

    What the bytes actually represent you can look up on the internet... I'm not going to waste space repeating it here.
  • 9. Re: How to get Date format for a given date?
    976640 Newbie
    Currently Being Moderated
    Thank you very much BluShadow,
    It was really very helpful.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points