date function with oracle db

Question

While creating a table i have mentioned varchar2 type to store date , Now I am not sure about oracle query to retrieve "from to - to date " .

Till now i have tried this query

SELECT DSC_REF,EMP_NO, EMP_NAME, ACTION_DATE from DSC_UPLOAD 
WHERE ACTION_DATE Between (extract(month from to_date(ACTION_DATE,'dd/mm/yyyy')) = '02' 
                          (extract(month from to_date(ACTION_DATE,'dd/mm/yyyy')) = '03' 
AND extract(year from to_date(ACTION_DATE,'dd/mm/yyyy'))='2018')

Never store dates in a varchar column. That is a terrible idea. — user330315
– user330315, Commented Apr 19, 2018 at 12:04
It's one thing if you have a legacy table but you definitely should not be creating a new table to store a date as VARCHAR2 — David Faber
– David Faber, Commented Apr 19, 2018 at 12:07
Also, just FYI, I believe the EXTRACT() function returns a NUMBER not a CHAR. — David Faber
– David Faber, Commented Apr 19, 2018 at 12:42

MT0 · Accepted Answer · 2018-04-19 13:14:33Z

1

Assuming ACTION_DATE is of the DATE data type then a simple method is to just use DATE literals:

SELECT DSC_REF,
       EMP_NO,
       EMP_NAME,
       ACTION_DATE
from   DSC_UPLOAD 
WHERE  ACTION_DATE >= DATE '2018-02-01'
AND    ACTION_DATE <  DATE '2018-04-01'

If it is a string (why?) then:

SELECT DSC_REF,
       EMP_NO,
       EMP_NAME,
       ACTION_DATE
from   DSC_UPLOAD 
WHERE  TO_DATE( ACTION_DATE, 'dd/mm/yyyy' ) >= DATE '2018-02-01'
AND    TO_DATE( ACTION_DATE, 'dd/mm/yyyy' ) <  DATE '2018-04-01'

is there any way to retrieve data only using month and year without mentioning date

You can do:

WHERE  EXTRACT( MONTH FROM TO_DATE( ACTION_DATE, 'dd/mm/yyyy' ) ) IN ( 2, 3 )
AND    EXTRACT( YEAR  FROM TO_DATE( ACTION_DATE, 'dd/mm/yyyy' ) ) = 2018

Regarding your query, if ACTION_DATE is a DATE data type then:

to_date(ACTION_DATE,'dd/mm/yyyy')

Is a bad idea. TO_DATE( date_string [, format_string [, nls_string] ] ) has a signature that takes between 1 and 3 string arguments. Oracle will try to be helpful and implicitly convert your date to a string using the NLS_DATE_FORMAT session parameter as the format model and you end up with a query that is effectively:

TO_DATE(
  TO_CHAR(
    ACTION_DATE,
    (
      SELECT VALUE
      FROM   NLS_SESSION_PARAMETERS
      WHERE  PARAMETER = 'NLS_DATE_FORMAT'
    )
  ),
  'dd/mm/yyyy'
)

If the NLS_DATE_FORMAT does not match dd/mm/yyyy then you will get an exception - and the NLS_DATE_FORMAT is a session parameter so each user can modify it and it may not be consistent between users or even user sessions.

edited Apr 19, 2018 at 13:14

answered Apr 19, 2018 at 12:42

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

TAK Over a year ago

Error code 1843, SQL state 22008: ORA-01843: not a valid month

TAK Over a year ago

I have tried this but am getting this error - Error code 1843, SQL state 22008: ORA-01843: not a valid month. Is there any other way to retrieve date from database and i have used varchar2 type for column.

MT0 Over a year ago

@sujithraelumalai Updated.

TAK Over a year ago

Sir is there any way to retrieve data only using month and year without mentioning date

MT0 Over a year ago

@sujithraelumalai Updated

Rene · Accepted Answer · 2018-04-19 12:32:17Z

0

When your date string is always formatted as dd/mm/yyyy then one way to get month and year is:

 month = to_char(to_date(action_date,'dd/mm/yyyy'),'mm')
 year  = to_char(to_date(action_date,'dd/mm/yyyy'),'yyyy')

The syntax for between is:

expression BETWEEN value1 AND value2;

Not sure what selection you are after given the example query.

answered Apr 19, 2018 at 12:32

Rene

10.6k5 gold badges37 silver badges48 bronze badges

Collectives™ on Stack Overflow

date function with oracle db

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related