英文:
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) <> 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
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论