CODEWITHSUNDEEP
sqloracle-database
Naveen
Naveen

Reputation: 473

TO_CHAR only considering dates while comparing two time stamps?

In the below query condition is failing but logically it should pass. Is something wrong with to_char??

SELECT data
FROM table1
WHERE
TO_CHAR(TO_DATE(value1, 'DD/MM/RRRR HH24:MI:SS'), 'DD/MM/RRRR HH24:MI:SS') <= 
   TO_CHAR( SYSDATE, 'DD/MM/RRRR HH24:MI:SS')
 AND TO_CHAR(TO_DATE(value2, 'DD/MM/RRRR HH24:MI:SS'), 'DD/MM/RRRR HH24:MI:SS') >=
    TO_CHAR( SYSDATE, 'DD/MM/RRRR HH24:MI:SS');

value1='02/07/2014 12:30:10'
value2='06/08/2015 09:57:33'

in both the conditions it is only checking the dates i.e.,02<=07 (7th is todays date).First condition is getting satisfied regardless of month and year.if i change value1 to '15/08/2014 12:30:10' it is failing. Same with second condition.

Upvotes: 1

Views: 2299

Answers (3)

Naveen
Naveen

Reputation: 473

Finally after lot of googling i got the solution. Below query is working for me :) :)

SELECT MESSAGE FROM TABLE1 WHERE TO_TIMESTAMP(value1, 'DD/MM/RRRR HH24:MI:SS') <= CAST(SYSDATE AS TIMESTAMP) AND TO_TIMESTAMP(value2, 'DD/MM/RRRR HH24:MI:SS') >= CAST(SYSDATE AS TIMESTAMP);

value1='02/07/2014 12:30:10' value2='06/08/2015 09:57:33'

Upvotes: 1

Conffusion
Conffusion

Reputation: 4465

You have to use TO_TIMESTAMP instead of TO_DATE:

SELECT data
FROM table1
WHERE  
TO_CHAR(TO_TIMESTAMP(value1, 'DD/MM/RRRR HH24:MI:SS'), 'DD/MM/RRRR HH24:MI:SS') <= 
   TO_CHAR( SYSDATE, 'DD/MM/RRRR HH24:MI:SS')
  AND TO_CHAR(TO_TIMESTAMP(value2, 'DD/MM/RRRR HH24:MI:SS'), 'DD/MM/RRRR HH24:MI:SS') >=
    TO_CHAR( SYSDATE, 'DD/MM/RRRR HH24:MI:SS');

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Why are you comparing dates as strings? This also begs the question of why you would store dates as strings in the first place. You should store date/times using the built-in types.

Try this instead:

SELECT data
FROM table1
WHERE TO_DATE(value1, 'DD/MM/RRRR HH24:MI:SS') <= sysdate AND
      TO_DATE(value2, 'DD/MM/RRRR HH24:MI:SS') >= sysdate;

Your problem is presumably that you are comparing strings, rather than dates. And the format you are using DD/MM/YYYY doesn't do comparisons the same way. This is, in fact, why you should just use the ISO format of YYYY-MM-DD whenever you are storing date/time values in strings (which I don't recommend in most cases anyway).

If your values are already stored in proper types, then you can just do:

SELECT data
FROM table1
WHERE value1 <= sysdate AND
      value2 >= sysdate;

If these are timestamps with time zone, then you can use SYSTIMESTAMP instead of SYSDATE.

Upvotes: 4

Related Questions