How do we convert a date in the 'DD-MM-YY' format WITHOUT using to_char ? If I use the following query i get it in DD-Mon-YY format ?
select TO_DATE(SYSDATE,'DD-MM-YY') from dual ;
Output : 29-Mar-18
I want it in 29-03-18 format , without using to_char. Is it possible ?
How do we convert a date in the 'DD-MM-YY' format WITHOUT using to_char ?
This is a common misconception that dates in the database have a format.
A date does not have a format - it is stored internally to the database as 7-bytes (representing year, month, day, hour, minute and second) and it is not until whatever user interface you are using (i.e. SQL/Plus, SQL Developer, Java, etc) tries to display it to you, the user, and converts it into something you would find meaningful (usually a string) that the date is given a format so that you, the user, find it meaningful on the client software.
So the question you should be asking is:
How do we get <insert name of SQL client software here> to change the default format it uses for a
DATEdata type?
If you are using SQL/Plus or SQL Developer then it will use the NLS_DATE_FORMAT session parameter to format the date. You can change this using:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YY';
Note: This is a session parameter and will only change the format for the current session (not for any other users or any subsequent sessions).
If you want to set this as the session default then you could set a logon trigger (if users are relying on the previous default format then applying this may not be well received).
You can also change the preferences in the SQL Developer GUI as described here.
we dont have alter permission , its client db
Then use TO_CHAR( date, format_model ) - that is what it is there for.
Note: Please do not use 2-digit years as the expected format. It is a source of errors when dates are given an unexpected and wrong century.
If I use the following query i get it in DD-Mon-YY format ?
select TO_DATE(SYSDATE,'DD-MM-YY') from dual ;
TO_DATE( date_string, format_model ) takes two string arguments and Oracle will implicitly call TO_CHAR to convert your date to a string so it will match the expected data type and then when the client program formats it it will implicitly perform a similar transformation again. So your query is effectively:
SELECT TO_CHAR(
TO_DATE(
TO_CHAR(
SYSDATE,
( SELECT VALUE
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT'
)
),
'DD-MM-YY'
),
( SELECT VALUE
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT'
)
)
FROM DUAL;
If the NLS_DATE_FORMAT is MM-DD-YY then SYSDATE will be implicitly converted to the default MM-DD-YY format then explicitly converted to a date using your format DD-MM-YY, and the day/month values will be swapped, before being converted back to the default MM-DD-YY format for display. Relying on implicit conversions is prone to many errors - try to avoid it.
If you want a date for your SQL client to format using their default format then just use:
SELECT SYSDATE FROM DUAL;
If you want to get a formatted date then use TO_CHAR( date, format_model ):
SELECT TO_CHAR( SYSDATE, 'DD-MM-YY' ) FROM DUAL;
ALTER SESSION is one option:
SQL> alter session set nls_date_format = 'dd-mm-yy';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------
29-03-18
Note that what you did - applied TO_DATE function to SYSDATE - is wrong. SYSDATE already returns DATE, so you could have applied TO_CHAR to it (with appropriate format mask), but not TO_DATE.
I would suggest you to use TO_CHAR or nls_date_format , though EXTRACT is an alternative if you want to answer an interview question.
SELECT EXTRACT (DAY FROM SYSDATE)
||'-'
|| LPAD(EXTRACT (MONTH FROM SYSDATE), 2, 0)
|| '-'
|| SUBSTR( EXTRACT(YEAR FROM SYSDATE),-2) as dt
FROM DUAL;
