data format
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: data format

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    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 ????
    Sonali

  2. #2
    Join Date
    Feb 2001
    Posts
    123
    If you select a date from the database with no formatting, it is formatted using the default date format for the database when it is displayed.

    The to_date function is used to convert character type data to the date datatype. The sysdate pseudocolumn is already a date, so to_date(sysdate) does not make any sense.

    If you want to display the date using a 4 digit year, either use the to_char function when you select it for display, or set the nls_default_date format to use a 4 digit year. Personally I consider it dangerous to rely on default date formats, and prefer to do implicit formatting.

    HTH

    David.

  3. #3
    Join Date
    Jan 2001
    Posts
    318
    Yes, you are right, I know what you are saying. Maybe I did not write my question properly.
    I cannot set NLS_date format, because of the obvious reason that this cannot be done for each customer with different applications.
    Secondly I cannot use to_char as it will convert date to string which will not work in my case.
    I want to keep the output as date and not string and still get the year in 4 digits.
    Do you or any one knows a way to do it ???

    I just want year in 4 digits with output as date NOT STRING.
    Sonali

  4. #4
    Join Date
    Feb 2001
    Posts
    123
    What are you using the date for? In PL/SQL, or SQL*Plus? Are you placing the date in a variable?

  5. #5
    Join Date
    Feb 2001
    Posts
    123
    Sorry, just noticed an error in my earlier post - should have said nls_date_format, not nls_default_date_format.

    Are you aware that you can set this on a per-session basis with the alter session set nls_date_format = 'DD-MM-YYYY' command? This might be what you are after.

    The main thing that confuses me about your question is that if you are displaying the date, then how is the data type significant? Oracle does a to_char conversion from its internal numeric representation of a date in order to display it.

  6. #6
    Join Date
    Jan 2001
    Posts
    318
    No , I cannot use NLS_date format. Yes, I am aware that it can be used per session basis.
    To_char converts the date to string which I don't want.
    I want to keep it as date and want to display year in 4 digits.
    We will be using this query in C++, MFC and HTML code.
    I was thinking if this is possible in simple single SQL statement ??
    I am not using any PL?SQL block.
    Also we support both SQL server and Oracle databases. SQL server seems to handle this fine. So I just did not wanted to write a function to do this in oracle and was trying to get it other way.
    SYSDATE is just an example we will need to do this for selects from actual table on date column.

    Thanks
    Sonali

  7. #7
    Join Date
    Jan 2001
    Posts
    318
    Thanks Dave, looks like per session nls_date_format would work for me.

    Thanks again
    Sonali

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