Menu
I have oracle date , I want to translate it to my date , for instance 24.7.2011 is sunday so i want a function to return 1 , for 25.7.2011 I want it to return 2 and so on...
I have been searching the wwb for examples but with no successes please help me.
432
The PLSQL SYSDATE function will returns current system date and time on your database. There is no any parameter or argument for the SYSDATE function. The SYSDATE function returns a date value. Note that the SYSDATE function return date and time as “YYYY-MM-DD HH:MM:SS” (string) or as YYYYMMDDHHMMSS (numeric).
In Oracle, you use the NLS_TERRITORY parameter to set which day the week should start on. You can change it just for your session, e.g.: ALTER SESSION SET NLS_TERRITORY=German; Then Monday becomes the first day of the week.
The Oracle function for this is TO_DATE with the 'D' format model:
SQL> select to_char (date '2011-07-24', 'D') d from dual;
D
-
7
As you can see, this returns 7 for Sunday, not 1, when I run it. The value returned varies according to your NLS settings. If necessary you could do this to get what you want:
SQL> select to_char (date '2011-07-24'+1, 'D') d from dual;
D
-
1
More details about Oracle's date format models can be found here
123
just you have to write to_char(your_date_column_name,'D') it will give the same answer what you have asked
just click here for more details
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
