Select date from between two timestamps

Question

I am facing the following problem. I have a database with a table which saves Dates (with its time).

Now I would like to know all the tables information where the date is in between two timestamps, but I am getting the following error: 01830. 00000 - "date format picture ends before converting entire input string".

What I did so far is this query:

SELECT * FROM ARBEITSBLOCK WHERE STARTZEIT BETWEEN '30.11.2015 19:00:00' 
                                              and  '01.12.2015 19:05:00'; 

And this which doesn't give me any result but there should be:

SELECT * FROM ARBEITSBLOCK 
WHERE TO_CHAR(STARTZEIT,'DD.MM.YYYY H24:MM:SS') BETWEEN '30.11.2015 13:00:00' 
                                                    and '01.12.2015 19:05:00'; 

Answer 1

Try this statement (using Oracle syntax)

SELECT *
FROM   ARBEITSBLOCK 
WHERE  STARTZEIT BETWEEN TO_DATE ('12/04/2015 09:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM')
                     AND TO_DATE ('12/04/2015 10:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM');