MOR_SNOW MOR_SNOW - 3 months ago 8
SQL Question

Selecting only time from date

If I do:

SELECT PRESERV_STARTED
FROM HARVESTED_L;


I will get values like:

23-12-1999 00:00:00
21-03-2000 22:01:37
...


And so on. (
PRESERV_STARTED
has type
DATE
)

What I want is only to select the date with time part, where the time is not
00:00:00
, so that I can omit those.
There is a lot of info about a solution to this, saying I can do something like:

select cast(AttDate as time) [time]
from yourtable


And for older versions of sql server:

select convert(char(5), AttDate, 108) [time]
from yourtable


And yet other proposals are:

SELECT CONVERT(VARCHAR(8),GETDATE(),108)


I tried all of these, among a few others, but no luck.
So my question is, having a date like:
23-12-1999 00:00:00
, how do I select the time part?

What comes most intuitive to me (mixing with the proposals I found) is something like:

SELECT CONVERT(VARCHAR(8), GETDATE(PRESERV_STARTED), 108) AS timePortion
FROM HARVESTED_L;


I get an error from this code, saying
"Missing expression"
. In fact, this is the error I get from most of the proposals I tried.

I am using Oracle SQL Developer
version 4.1.1.19

Answer

If I understand correctly you want to select only the rows for which the time part of the date column is not 00:00:00. You don't have to get the time part in order to do this. You can use TRUNC function which (by default) returns date with the time part truncated. Here's an example:

SQL> select * from t;

        ID D
---------- -------------------
         1 2016-01-01 00:00:00
         2 2016-01-01 00:01:00
         3 2016-01-01 00:01:23

3 rows selected.

SQL> select * from t where d <> trunc(d);

        ID D
---------- -------------------
         2 2016-01-01 00:01:00
         3 2016-01-01 00:01:23

2 rows selected.