data format

[sonaliak]

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 ????

[Dave_A]

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.

[sonaliak]

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.

[Dave_A]

What are you using the date for? In PL/SQL, or SQL*Plus? Are you placing the date in a variable?

[Dave_A]

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.

[sonaliak]

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

[sonaliak]

Thanks Dave, looks like per session nls_date_format would work for me.

Thanks again