Sysdate help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Sysdate help

  1. #1
    Join Date
    Nov 2001
    Posts
    2

    Question

    I am having trouble retreiving the sysdate in my queries. If I run

    SQLWKS> SELECT sysdate
    2> FROM dual;
    SYSDATE
    --------------------
    20-NOV-01
    1 row selected.

    I am trying to get the time stamp (HH:MM:SS) to show as well, as Oracle books show that it should, but am having no luck. Hopefully someone out there has a suggestion

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual

  3. #3
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') from dual;

  4. #4
    Join Date
    May 2001
    Location
    Jacksonville, FL
    Posts
    12
    Use the 'to_char' function to display the system date (or any date stored in a table):

    select to_char(sysdate, 'MM/DD/YYYY HH:MI:SS') from dual;

    Note that the minutes field is 'MI' (not to be confused with month which is 'MM'). Good luck.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Or, change your NLS_DATE_FORMAT variable:
    Code:
    SQL> select sysdate from dual;
    
    SYSDATE
    ---------
    20-NOV-01
    
    SQL> alter session set nls_date_format="mm/dd/yyyy hh24:mi:ss";
    
    Session altered.
    
    SQL> select sysdate from dual;
    
    SYSDATE
    -------------------
    11/20/2001 11:44:46
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184
    that all depends on the nls_date_format set in the init.ora
    else for that particular session the nls_date_format may be changed...
    sonofsita
    http://www.ordba.net

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by sonofsita
    that all depends on the nls_date_format set in the init.ora
    else for that particular session the nls_date_format may be changed...
    Not really, the hierachies goes like this

    NLS_LANG enviroment variable predominates all followed by
    NLS_DATE_FORMAT at session level
    NLS_DATE_FORMAT set in init.ora, instance level
    NLS_DATE_FORMAT at database level

    if none of above is not used then you have to check NLS_TERRITORY, if I remember right it was territory

  8. #8
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184
    the question is why the output is like that than the user wished. I totally agree with you as far as hierachies are concerned.

    it is said "NLS_TERRITORY specifies the name of the territory whose conventions are to be followed for day and week numbering."

    That also can be set at the database, instance, session

    So ultimately what is suggested..

    at the database level it is at the time of creation..
    that is alterable ?

    (1) permanent fix--instance level -- init.ora
    (2) temporary fix-- session level at sqlplus/svrmgr/sqlwks

    sonofsita
    http://www.ordba.net

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I have only seen NLS_DATE_FORMAT set in init.ora under 2 circumstances

    1. The application is three-tiered
    2. Requirement of application

    If you are using an ad-hoc tool I dont see a reason why you have to set this parameter at instance level :-?

    Plus setting it in init.ora you are forcing everyone use that date format

  10. #10
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184
    "ad-hoc tool " - this is a good reason to leave it for each session..

    "Plus setting it in init.ora you are forcing everyone use that date format" - this is also a good reason to desist from imposing nls_date_format..

    "Requirement of application" is essential...in my experience the general users (OS level users authenticated to access database using the schema owner id and pw)are viewers and application uses the schema owner id and does all the ddl/dml and we have set standards... so it suits us. that also now defines the tiers.

    Thanks Pando for a goos discussion.

    The question gets reverted to stevenb who is to know his requirement and decide..

    [Edited by sonofsita on 11-20-2001 at 05:36 PM]
    sonofsita
    http://www.ordba.net

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width