CODEWITHSUNDEEP
sqlt-sqldatetimesql-like
forRJ
forRJ

Reputation: 70

SQL Server : filter on time element in datetime format

I am currently struggling with the following:

I have a SQL query which selects a datetime column. Next to that I do a convert in order to get the time only.

bc.StartTime as startdatetime,
CONVERT(TIME,bc.starttime) as starttime,

Output looks like this:

startdatetime              starttime
-------------------------------------------
2019-04-14 19:13:53.000    19:13:53.0000000
2019-04-15 00:00:00.000    00:00:00.0000000

So far so good, but now, I want to filter out the "00:00:00.0000000" time.

I've tried several things like:

AND CONVERT (time, bc.starttime) != '00:00:00.0000000' 
AND bc.starttime NOT LIKE '%00:00:00.000'

but none of them is working.

The starttime also contains "NULL"'s - which I would like to keep in my results.

Is there anyone who can help me?

Edit:

Thanks for your contribution all!

I am having 4 columns; actdatetime, bcstarttime, date and time. I will try to explain what I want:

The date and time column are converted columns from the bcstarttime column.

But I want to filter out the "00:00:00" times in my results. Because this column is an alias, it doesn't want to be declared in my "WHERE" clause.

If I do the following:

WHERE bc.StartTime != '00:00:00'

Then it also 'destroys' my NULL values (which I want to keep in the result).

It is probably a newbie thing which I am overlooking....

Upvotes: 0

Views: 472

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

How about a slightly different approach?

where (convert(date, bc.starttime) <> bc.starttime or
       startdatetime is null
      )

That is, there is something besides the date component.

Or you can do this using times:

where (convert(time, bc.starttime) <> '00:00:00.0000000' or
       bc.starttime is null
      )

Upvotes: 1

Related Questions