Select date from between two timestamps
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';
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');
If STARTZEIT is a DATE column, then why are you trying to compare it to a string?
By doing that, you are relying on Oracle being able to say "aha! This string is really a date, so I will attempt to convert it for you!". That's all well and good, but how will Oracle know how the date-in-the-string is formatted?
Well, there's the nls_date_format parameter which is defaulted to 'DD-MON-RR', and I think you can now see why you're getting the "date format picture ends before converting entire input string" error, since 'DD-MON-RR' is a lot shorter than '30.11.2015 19:00:00'.
Instead of relying on this implicit conversion and the bugs that go right along with that (as you've discovered!), you should explicitly convert the string into a date, which you can easily do with the to_date() function.
select * FROM ARBEITSBLOCK WHERE STARTZEIT BETWEEN to_date('30.11.2015 19:00:00', 'dd.mm.yyyy hh24:mi:ss') and to_date('01.12.2015 19:05:00', 'dd.mm.yyyy hh24:mi:ss');
Oracle does not store dates in the format you see. It stores it internally in 7 bytes with each byte storing different components of the datetime value.
You must use TO_DATE with proper FORMAT MODEL to explicitly convert the literal to DATE.
SELECT * FROM ARBEITSBLOCK WHERE STARTZEIT BETWEEN TO_DATE('30.11.2015 19:00:00', 'DD.MM.YYYY HH24:MI:SS') AND TO_DATE('01.12.2015 19:05:00', 'DD.MM.YYYY HH24:MI:SS');
Remember, the DATE data type has both date and time elements, TIMESTAMP is an extension to DATE data type.