DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: URGENT- date format

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    I needed urgent help on this so I am posting my question in Administration section which seems to be used more.

    Here are the queries:
    SQLWKS> select to_char(sysdate,'dd-mon-yyyy') from dual;
    TO_CHAR(SYS
    -----------
    22-feb-2001
    1 row selected.
    SQLWKS> select to_date(sysdate,'dd-mon-yyyy') from dual;
    TO_DATE(SYSDATE,'DD-
    --------------------
    22-FEB-01
    1 row selected.

    When I do to_date the year is always represented in 2 YY and not in 4 YYYY which I am trying to format to.
    When I do to_char the output is a string but with the format i want 4 YYYY. But I don't want output as a string I want it as date. How can I do this ????

    In other words I want to get the date formatted so that year is represented in 4 digits and not in 2 digits.

    I cannot use to_char as it converts date to string.
    I cannot change NLS_date format which will make it complex( in handling customer and not query wise)

    Is their any way to get this ???
    SYSDATE is just a ex. We will be selecting actual date from a table.

    Sonali







    Sonali

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    Could you display the value after selecting it as a string, then process it using it as a date? For example, select to_char(sysdate, 'dd-mon-yyyy') into X from dual. Then in a subsequent line of code, use X as a date, such as Y := to_date(X);


    I'll try to be more specific if you post how you are using it. For example, if it's in forms or a stored procedure . . .

  3. #3
    Join Date
    Jun 2000
    Location
    Toronto, ON, Canada
    Posts
    50
    It looks like the parameter NLS_DATE_FORMAT is set to DD-MON-YY. You could verify this by selecting
    select sysdate from dual ;
    If you are under NT, go to registry (start regedit.exe utility). Then go to HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->HOMEi where HOMEi is the home in which Oracle server or client rezides. Go to EDIT->String Value and name it NLS_DATE_FORMAT. Set the value for this variable to DD-MON-YYYY.
    Close SQL Worksheet and start it again to test the new format. It should be 22-FEB-2001.

  4. #4
    Join Date
    Jan 2001
    Posts
    318
    Nup, you do to_date and it converts it back to 2 YY, 2 digit year.

    SQLWKS> select to_char(sysdate, 'DD-MON-YYYY') from dual;
    TO_CHAR(SYS
    -----------
    22-FEB-2001
    1 row selected.

    SQLWKS> select to_date(to_char(sysdate, 'DD-MON-YYYY')) from dual;
    TO_DATE(TO_CHAR(SYSD
    --------------------
    22-FEB-01
    1 row selected.

    I have no problem in using a variable to hold the value.
    I think setting NLS_date format is the only option left.
    Sonali

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    If you do not want to change the NLS_DATE_FORMAT for the database you can change it only for your session.

    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

    SELECT SYSDATE FROM DUAL;

    SYSDATE
    --------------
    23-FEB-2001

    Sanjay

  6. #6
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356
    I think using NLS_DATE_FORMAT parameter in initializing files will be a safer option.

  7. #7
    Join Date
    Feb 2001
    Posts
    125
    Yes,
    set SERVEROUT ON
    and run following procedure

    declare
    x Varchar2(11);
    y date;
    begin
    select to_char(sysdate,'dd-mon-yyyy') into x
    from dual;
    y :=to_date(x);
    dbms_output.put_line(y);
    end;

    Pitamber Soni

    -----------------------------


    [QUOTE][i]Originally posted by kmesser [/i]
    [B]Could you display the value after selecting it as a string, then process it using it as a date? For example, select to_char(sysdate, 'dd-mon-yyyy') into X from dual. Then in a subsequent line of code, use X as a date, such as Y := to_date(X);


    I'll try to be more specific if you post how you are using it. For example, if it's in forms or a stored procedure . . . [/B][/QUOTE]

  8. #8
    Join Date
    Oct 2000
    Posts
    3
    hi,
    if u want to display the year always in 4y means.
    just the the NLS_DATE_FORMAT..

    Oracle have 3 parameters for that.

    for session.
    for instance.
    for Database.

    ASk the DBA to change this..

    Regds,
    Jgun..

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