NLS settings and Timestamp issues

DESC TRIAL

HERE TRIAL HAS COLA IS DATE , Point to be noticed here is if you compare timestamp with date it will convert both to

timestamp for comparing

one more point is even if you store date it will always have timestamp stored in table.So we should be careful while comparing date and timestamp it will convert both to timestamps

SELECT * FROM TRIAL

WHERE COLA >= TO_DATE( SYSDATE,'DD-MON-YY')

SELECT * FROM TRIAL

WHERE COLA >=  SYSDATE

Bhagvant,

Since the session’s NLS date format is timestamp, sysdate gives you timestamp and in ORACLE to store date or time we have only one data type “DATE”.

Now in the below scenario, while comparing, I think you need to trunk the sysdate. This is just my instinct J

select * from client_program

where upper(cli_pgm_nm)  like '%US10207%'

and TRUNC(SYSDATE) BETWEEN to_Date(EFF_DT,'dd-mon-yy') AND to_date(trm_dt,'dd-mon-yy')

This is part of below mail that I had sent.This is a fun question on sql.Some thing I noticed J

And we always  thought date did not have a timestamp J ….. In NLS setting of sql developer I have set date format DD-MON-YY HH24.MI.SS.

So below is a question how to do this

select * from client_program

where upper(cli_pgm_nm)  like '%US10207%'

and SYSDATE BETWEEN to_Date(EFF_DT,'dd-mon-yy') AND to_date(trm_dt,'dd-mon-yy')

this does not return the record.Though the EFF_DT and TRM_DT are 29 Aug 13 .. Now question is Why .. Is it because we are comparing sysdate which Is a timestamp and EFF_DT which is a date so it will convert both to timestamp before comparing ????

In peferences if you keep date format to DD-Mon RR you will get below result for select to_timestamp(sysdate-1) from dual.That is it will always give 12 am .But if you change the setting of sql developer to DD-MON-RR HH24.MI.SS you give a different timing.I was wondering which one is correct

After changing the preferences