0

how to compare joining date with current date and if it is less than or equal to 45 days then select those details and display in oracle sql, what to do if joining date and current date in different format like (joining date 12/03/2015 and current date 01-MAR-17)?

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
Kishore Anand Venkatarajulu
  • 1
  • 1
  • 1
    Date doesn't have any format. It is only displayed in a format you could understand based on your locale specific NLS settings. See https://stackoverflow.com/a/36059638/3989608 – Lalit Kumar B Mar 22 '18 at 05:31
  • 2
    share your table schema/structure and what have you tried yet – AmanS Mar 22 '18 at 05:31

2 Answers2

0

That would be something like this (if you need rows for those who joined 45 days or more ago):

select *
from your_table
where joining_date <= trunc(sysdate) - 45;
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
-1

I luckily got output, query is

select COL_NAME from TABLE_NAME where (to_date(SYSDATE)- to_date(COL_NAME,'dd/mm/yyyy'))<=60

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Kishore Anand Venkatarajulu
  • 1
  • 1
  • **Never** apply `to_date()` on a value that is already a date. `to_date()` expects a `varchar` so the date value will first be converted to a `varchar` then passed to the `to_date()` function which will convert it back to a `date` which it was to begin with. –  Mar 22 '18 at 07:00
  • See https://stackoverflow.com/a/29559609/3989608 As @a_horse_with_no_name said, **Never use TO_DATE on a DATE, It will implicitly convert it into string and then back to date using locale-specific NLS format.** – Lalit Kumar B Mar 22 '18 at 07:43
  • A second issue with this query is that it won't use an index (if there is one) on `COL_NAME` - for that you want `WHERE col_name >= TRUNC(SYSDATE-60)` – David Faber Mar 24 '18 at 22:45