[raghuvendra]

I have written a query:-

select to_date(FILE_START_TIME,'YYYY-MM-DD HH:MI:SSam'), to_date(FILE_END_TIME,'YYYY-MM-DD HH:MI:SSam'), SUBSCRIBER_ID from cms_video_info,video_view_history where cms_video_info.vcode=video_view_history.FILE_ID and vcode='V1111' and video_view_history.FILE_START_TIME >='03-04-2008' OR video_view_history.FILE_END_TIME<='03-04-2008';

TO_DATE(F TO_DATE(F SUBSCRIBER
--------- --------- ----------
26-FEB-08 26-FEB-08 9218181818
14-MAR-08 14-MAR-08 9818675098


now if u see the date entered above is 03-04-2008 (start date) and 03-04-2008 (end date)
now accordingly koi result display nahin hona chahiye tha...as the records do not exist in the given date range. but it is displaying the result.

Now if i write AND instead of OR in the query it gives

select to_date(FILE_START_TIME,'YYYY-MM-DD HH:MI:SSam'),to_date(FILE_END_TIME,'YYYY-MM-DD HH:MI:SSam'),SUBSCRIBER_ID from cms_video_info,video_view_history where cms_video_info.vcode=video_view_history.FILE_ID and vcode='V1111'and video_view_history.FILE_START_TIME >='03-04-2008' and video_view_history.FILE_END_TIME<='03-04-2008';


no rows selected

which is rite but if i change the input dates (date range)
lets say

select to_date(FILE_START_TIME,'YYYY-MM-DD HH:MI:SSam'),to_date(FILE_END_TIME,'YYYY-MM-DD HH:MI:SSam'),SUBSCRIBER_ID from cms_video_info,video_view_history where cms_video_info.vcode=video_view_history.FILE_ID and vcode='V1111'and video_view_history.FILE_START_TIME >='01-01-2008' and video_view_history.FILE_END_TIME<='03-04-2008';

no rows selected

It gives no rows selected. when there exists records in that date range.

Please help?
:)

[r035198x]

Are those columns dates or varchars?

[raghuvendra]

Those columns are varchar.

[r035198x]

Originally Posted by raghuvendra

Those columns are varchar.

They need to be dates if you want to do date comparisons on them.
Right now they are being compared as strings.

[raghuvendra]

Originally Posted by r035198x

They need to be dates if you want to do date comparisons on them.
Right now they are being compared as strings.

Thanks a lot. By the way if want use it as varchars ..then wat should i do?

[r035198x]

Originally Posted by raghuvendra

Thanks a lot. By the way if want use it as varchars ..then wat should i do?

That would not be the natural way of storing dates but you could convert the varchars to dates before the comparisons using the TO_DATE function.

[raghuvendra]

Thanks buddy. You were a great help.

[r035198x]

Originally Posted by raghuvendra

Thanks buddy. You were a great help.

Welcome.
P.S You may want to take part in this chat session. Ignore all the posts by Jos in that thread.