-1

Sory, I have Question ? Why not show up when I execute some of its field content. Please help me to fix it. Thank U

SELECT * FROM T_TRANS WHERE TIME_START = to_date('01-09-2014', 'DD-MM-YY');
PSR
  • 39,804
  • 41
  • 111
  • 151
Aditya Galang Mahafi
  • 21
  • 1
  • 4
  • 1
    What is the question ? – PSR Sep 04 '14 at 10:59
  • 2
    Does your `time_start` column include a time, as the name suggests, or just a date (all times are midnight)? Your query - even using YYYY instead of YY - will only match records at exactly midnight. – Alex Poole Sep 04 '14 at 11:05
  • Agree with Alex, to check, try `trunc(time_start) = to_date('01-09-2014', 'DD-MM-YY')`. If records come, definitely `time_start` has timestamp. – San Sep 04 '14 at 11:09

3 Answers3

2

Oracle is being a bit lenient with you with the date format mask, but you should use YYYY to make it clearer. But you're still providing a single point in time as midnight on that date:

select to_char(to_date('01-09-2014', 'DD-MM-YY'), 'YYYY-MM-DD HH24:MI:SS') as two_digit,
  to_char(to_date('01-09-2014', 'DD-MM-YYYY'), 'YYYY-MM-DD HH24:MI:SS') as four_digit
from dual;

TWO_DIGIT           FOUR_DIGIT
------------------- -------------------
2014-09-01 00:00:00 2014-09-01 00:00:00

Given the name of the column it's reasonable to assume you have other times, and your query won't match anything except exactly midnight. To find all records on that day, you need to provide a range:

SELECT * FROM T_TRANS
WHERE TIME_START >= to_date('01-09-2014', 'DD-MM-YYYY');
AND TIME_START < to_date('02-09-2014', 'DD-MM-YYYY');

... though I prefer the ANSI date notation for this sort of this:

WHERE TIME_START >= DATE '2014-09-01'
AND TIME_START < DATE '2014-09-02'

You could specify 23:59:59 on the same date, but that will break subtly when you use a timestamp field rather than a date field.

You could also truncate the value from the table (as @San said in a comment), or convert to a string for comparison (as @yammy showed in an answer), but either of those will prevent any index on the time_start column being used, affecting performance.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
0

After reading Alex Poole suggestion, you should try:

SELECT * FROM T_TRANS WHERE to_char(TIME_START, 'DD-MM-YYYY') = '01-09-2014';
yamny
  • 660
  • 4
  • 13
-1

Shouldn't the format mask be "DD-MM-YYYY"?

TenG
  • 3,843
  • 2
  • 25
  • 42