How to search by to_date function?

Question

I have table as below:

Table Temp:

ID  MAX MIN DATE_C  
1   34  24  21-APR-17 02.41.38.520000 PM    
2   32  26  20-APR-17 02.42.44.569000 PM

I execute the below SQL query to get temperature details on respective date:

SELECT *
FROM Temp t
WHERE t.date_c = TO_DATE( '2017-04-21', 'YYYY-MM-DD')
order by t.id

But it's returning empty records. Whats wrong with my query?

score 2 · Accepted Answer · answered Apr 21 '17 at 12:17

2

You need to remove the time component on the column. Here is one way:

SELECT *
FROM Temp t
WHERE TRUNC(t.date_c) = DATE '2017-04-21'
ORDER BY t.id;

However, I usually recommend using inequalities, rather than a function on the column:

SELECT *
FROM Temp t
WHERE t.date_c >= DATE '2017-04-21' AND
      t.date_c < DATE '2017-04-22'
ORDER BY t.id;

This allows the query to use an index on date_c. I should add that the original version can use an index on (trunc(date_c, id).

answered Apr 21 '17 at 12:17

Gordon Linoff

1,242,037
58
646
786

Does DATE keyword automatically detect date format..? – SAP DEV Apr 21 '17 at 12:25
@SAPDEV . . . The `DATE` keyword allows ANSI standard formats (it is actually an ANSI standard keyword). I only use YYYY-MM-DD formats, so I've never tried it with a different format. – Gordon Linoff Apr 21 '17 at 12:26
2

`DATE` literal accepts **only** ANSI format: *The ANSI date literal contains no time portion, and must be specified in exactly this format ('YYYY-MM-DD').* from [Datetime Literals](http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ) – Wernfried Domscheit Apr 21 '17 at 12:37

MT0 · Answer 2 · 2017-04-21T12:40:46.240

21-APR-17 02.41.38.520000 PM is not a DATE; it has a fractional seconds component so it is a TIMESTAMP.

So, if you want to find items that are on a particular day (inputting the TIMESTAMP using an ISO/ANSI timestamp literal):

SELECT *
FROM   Temp
WHERE  date_c >= TIMESTAMP '2017-04-21 00:00:00' AND
       date_c <  TIMESTAMP '2017-04-21 00:00:00' + INTERVAL '1' DAY;

or

SELECT *
FROM   Temp
WHERE  date_c >= TO_TIMESTAMP( :your_date_string, 'YYYY-MM-DD' ) AND
       date_c <  TO_TIMESTAMP( :your_date_string, 'YYYY-MM-DD' ) + INTERVAL '1' DAY;

it's returning empty records. Whats wrong with my query?

date_c = TO_DATE( '2017-04-21', 'YYYY-MM-DD') matches all rows where the date_c value is exactly 2017-04-21 00:00:00.000000 (including the time component); if you do not have any rows with exactly that date and time then, as you noticed, it will return nothing. If you want to get records matching that day then you need to get values within a range of times between the start and end of the day.

score -3 · Answer 3 · edited Apr 21 '17 at 15:38

-3

You need to pass date on the column. Here is a way...

SELECT *
FROM Temp t
WHERE CAST(t.CREATED_ON as date)= N'2017-04-22'
ORDER BY t.id

edited Apr 21 '17 at 15:38

C B

1,677
6
18
20

answered Apr 21 '17 at 12:37

rahul sahu

1
1

Using `CAST( date_c AS DATE )` will not remove the time component as, in Oracle, there is no `DATETIME` data type and a `DATE` is stored internally as [7-bytes](http://stackoverflow.com/questions/13568193/how-are-dates-stored-in-oracle) containing year (2-bytes) and month, day, hour, minute and second (1-byte each). All this will do is remove the fractional seconds from the `TIMESTAMP`. – MT0 Apr 21 '17 at 12:44
Also, using the comparison of a `DATE` to a `NVARCHAR2` string will result in an implicit call to `TO_DATE` on the string using the `NLS_DATE_FORMAT` session parameter. If this does not match the format of the string then the query will throw an exception. Given the OP's `TIMESTAMP` format appears to be `21-APR-17 02.41.38.520000 PM` then it is likely that the `DATE` format is similar and this will fail. – MT0 Apr 21 '17 at 12:49

How to search by to_date function?

3 Answers3