I have a date column which usually constists of data + hour + minutes. Sometimes it's just a date. I want to make a condition in where clause, to fetch records only if they have data, hour and minute. How can I do that?
1 Answer
In Oracle, a DATE data type is a binary data type consisting of 7 bytes that represent: century, year-of-century, month, day, hour, minute and second. It ALWAYS has those 7 bytes and it is NEVER stored in any particular (human-readable) format.
Given this, your question does not make sense as you cannot have a DATE data type that does not have hour, minute or second components. If the time components are not provided then default values will be used and set the hours, minutes and/or seconds to 0.
If we rephrase the question to:
I want to make a condition in where clause, to fetch records only if the time component of the date is not midnight. How can I do that?
SELECT *
FROM table_name
WHERE date_column > TRUNC(date_column);
3 Comments
Alex
Thank you for your answer. The point of my query is to find workers who work after work hours. So, currentlly it's hard for me to tell if midnight appears because it is a deafault, or is it because of the employee. If I understood you correctly, the default version will always be 00:00:00 and not for example 00:00:03? Cause I seem to get a lot of 00:00:XX and 00:15:XX.
MT0
@Alex If you use
TO_DATE('1970-01-01', 'YYYY-MM-DD') or TO_DATE('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') then they will both generate a date 1970-01-01 00:00:00 and there is no way to tell that one had default values for the time and one was explicitly specified. If you have the time HH:MM:SS and the values are non-zero then they cannot have been the default values.MT0
@ThorstenKettner Thanks, that is what I'd intended to write. I'd not spotted the the extra
= that my fingers had snuck in.
to_char()orextract()to get the DateTime parts Hour or Minute in Oracle and then check whether those are> 0see also stackoverflow.com/questions/26644466/…