First millisecond and Last millisecond in a day

You could use timestamp:

SELECT TO_CHAR(SYSTIMESTAMP,'DD-MON-YYYY HH24.MI.SS.FF') FROM DUAL;

Select allOpen in new window

Thanks for the response.

I eseentially need to retrieve records from the very first millisecond in a day (say today) until vers last millisecond of today.
Can you please tell me how that can be achieved?

Thanks.

My table stores the date time as Number which is in milliseconds.
So timestamp_column equivalent column in my schema is a NUMBER column.

Here is a sample Number value representing a Date Time: 1359651194334

select * from table where MilliSecs_Number_column >= trunc(systimestamp) and < trunc(systimestamp+1);

So,  how do I convert the MilliSecs_Number_column to DATE with Timestamp in the SELECT sql?

Not sure. It is being stamped by JAVA code.

On converting the Number in Milliseconds to Date, I get 1/31/2013 4:53:14 PM.

SELECT TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS') + (1359651194334  /1000/60/60/24) from dual;

Select allOpen in new window

Your approach works. Thanks.

In the mean time, I also found another approach which is essentially giving me the same result:

select * 
  from table 
 where TO_DATE('1970-01-01', 'YYYY-MM-DD') + (1359651194334  /1000/60/60/24) >= trunc(systimestamp) 
   and TO_DATE('1970-01-01', 'YYYY-MM-DD') + (1359651194334  /1000/60/60/24) < trunc(systimestamp+1);  

Select allOpen in new window

What do you think about this approach?
Thanks!

Good point. I'll have to discuss with the developers on that.

Thanks to both slighwv and MikeOM_DBA for the help.

Awesome. Thanks for the findings.