2

How to data of current date from timestamp field in oracle?

select
 jobstatus0_.JOBNAME as JOBNAME1_21_,
 jobstatus0_.STARTDATE as STARTDATE2_21_,
 jobstatus0_.ENDDATE as ENDDATE3_21_,
 jobstatus0_.REMARKS as REMARKS4_21_,
  jobstatus0_.STATUS as STATUS5_21_
 from
   PD_OWNER.CIM_SNOW_JOB_STATUS_TAB jobstatus0_
  where
 jobstatus0_.JOBNAME='AGREEMENTS'
 AND jobstatus0_.STARTDATE=sysdate <-- Not working with this

We need not to consider Time but date only

I tried following but it is also not working.

and cast(jobstatus0_.STARTDATE as date)=sysdate

Also used which worked

AND  to_date(to_char(jobstatus0_.STARTDATE,'DD-MON-YYYY'))=to_date(to_char(sysdate,'DD-MON-YYYY'));

but want more elegant way.

fatherazrael
  • 5,511
  • 16
  • 71
  • 155

3 Answers3

3

You may use the TRUNC(date) function

Purpose

The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is truncated to the nearest day. Please refer to "ROUND and TRUNC Date Functions" for the permitted format models to use in fmt.

Examples

The following example truncates a date:

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')
  "New Year" FROM DUAL;

New Year
---------
01-JAN-92
Dan
  • 1,238
  • 2
  • 17
  • 32
vishnudattan
  • 476
  • 3
  • 10
  • I updated your answer to include information from the page you linked. People find it much more helpful when you do this instead of only linking. – Dan Feb 10 '20 at 18:06
1

If column STARTDATE is DATE (or TIMESTAMP), you could truncate it (as well as SYSDATE) and get

and trunc(jobstatus0_startdate) = trunc(sysdate)

as trunc will remove time component:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select trunc(sysdate) trd,
  2         trunc(systimestamp) trt
  3  from dual;

TRD                 TRT
------------------- -------------------
06.02.2020 00:00:00 06.02.2020 00:00:00

SQL>

Though, if there was an index on that column, truncating it will make the index unusable, but that's another story and there is a workaround (function based index or making the column value "between" sysdate at this and next midnight).

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • "*will remove time component*" - strictly speaking it only sets it to `00:00:00`, the resulting value still has a time component –  Feb 10 '20 at 16:11
0

In postgresql/ mysql use as below

date(jobstatus0_.STARTDATE)=date(now())

In sql-server 

cast(jobstatus0_.STARTDATE as date)=cast(CURRENT_TIMESTAMP as date)
Tonmoy pramanik
  • 21