CODEWITHSUNDEEP
sqloracle-databaseselectbetween
Kairan
Kairan

Reputation: 5542

Query in SQL using between Timestamp pulling wrong data

I am using this query:

  SELECT ts as "TimeStamp", 
         stat as "Status"
    FROM myTable
   WHERE stat = 'O'
     AND source = 'Source1'
     AND ts BETWEEN TO_TIMESTAMP('2013-10-05','yyyy-mm-dd') AND
                    TO_TIMESTAMP('2013-10-06','yyyy-mm-dd')

And also tried:

  SELECT ts as "TimeStamp", 
         stat as "Status"
    FROM myTable
   WHERE stat = 'O'
     AND source = 'Source1'
     AND ts >= TO_TIMESTAMP('2013-10-05','yyyy-mm-dd') AND ts <
                    TO_TIMESTAMP('2013-10-06','yyyy-mm-dd')

It returns 0 records, but if I do 

  SELECT ts as "TimeStamp", 
         stat as "Status"
    FROM myTable
   WHERE stat = 'O'

I can clearly identify 5 records. Apparently the TO_TIMESTAMP is not working properly I am hoping someone might be able to help identify the proper fix

Edit: To clarify, I only want the timeframe for 10/5 not including 10/6 Sorry pasted wrong results Also the field is of type TIMESTAMP(6)

Upvotes: 0

Views: 14435

Answers (2)

asantaballa
asantaballa

Reputation: 4048

Because the TO time stamp has time even though you're not specifying. So it is selecting only up to the very beginning of the lat day. Either specify time 23:59:59 and subseconds as required, or do less than the next day.

...
AND ts >= TO_TIMESTAMP('2013-10-05','yyyy-mm-dd') 
AND ts <  TO_TIMESTAMP('2013-10-07','yyyy-mm-dd')

Upvotes: 4

dElo
dElo

Reputation: 124

Try: AND ts BETWEEN TO_DATE('2013-10-05','YYYY-MM-DD') AND TO_DATE('2013-10-06','YYYY-MM-DD')

And mind that it will get you results from the 10-05 at 00:00:00 to the 10-06 at 00:00:00 If you don't specify the HH MI SS mask.

Date masks are case sensitivve and TO_DATE will work better.

Upvotes: 0

Related Questions