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'; 

Answers


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.

E.g.:

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.


Need Your Help

R - summarize one matrix by another

r matrix summary

I have two matrices. The first contains values, and the second contains names corresponding to those values. I would like to sum the values in the first matrix by the corresponding name in the second