Hey Everyone,

I have table called vdet_sa_archive and in that table there are so many fields and i want to access the data on datetime range.

sample data in table

starttime endtime

1 14-02-13 01:30:15.000000000 AM 14-02-13 02:01:57.000000000 AM
2 14-02-13 4:30:01.000000000 AM 14-02-13 5:30:01.000000000 AM
3 14-02-13 01:30:01.000000000 PM 14-02-13 01:45:01.000000000 PM
So i want to access the data between 14-02-13 1:00:00 AM to 14-02-13 5:00:00 AM

So how can i get this data

i wrote like

select count(*) from vdet_sa_archive where starttime >= TO_DATE('14/FEB/2013 01:00:00 AM', 'dd/mm/yyyy HH:MI:SS AM') and endtime<=TO_DATE('14/FEB/2013 04:00:00 AM', 'dd/mm/yyyy HH:MI:SS AM');

But its not working.

Thanks and regards,
snehal

what is your datatype of startime, endtime, i guess it of type date and not varchar

if its date type, then you need not to use to_date

simply starttime>'14-feb-2012 01:00:00 am' will work

SELECT COUNT(*)
FROM VDET_SA_ARCHIVE
WHERE STARTTIME >= TO_CHAR('14/FEB/2013 01:00:00 AM', 'dd/mm/yyyy HH:MI:SS AM')
and endtime<= to_char('14/FEB/2013 04:00:00 AM', 'dd/mm/yyyy HH:MI:SS AM');

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.