SQL Server:在日期时间格式中筛选时间元素。

huangapple go评论68阅读模式
英文:

SQL Server : filter on time element in datetime format

问题

I can provide translations for the non-code parts of your text:

"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...."

英文:

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....

答案1

得分: 1

如何尝试稍微不同的方法?

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

也就是说,除了日期组件之外还有其他内容。

或者您可以使用时间来执行此操作:

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

How about a slightly different approach?

where (convert(date, bc.starttime) &lt;&gt; 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) &lt;&gt; &#39;00:00:00.0000000&#39; or
       bc.starttime is null
      )

huangapple
  • 本文由 发表于 2020年1月6日 23:42:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/59614975.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定