我试图在SQL查询的datetime字段中比较时间,但我不知道它是对的。 我不想比较日期部分,只是时间部分。
我在做这个:
SELECT timeEvent FROM tbEvents WHERE convert(datetime, startHour, 8) >= convert(datetime, @startHour, 8)这是对的吗?
我问这个是因为我需要知道'08:00:00'是否小于或大于'07:30:00',我不想比较日期,只是时间部分。
谢谢!
I'm trying to compare time in a datetime field in a SQL query, but I don't know if it's right. I don't want to compare the date part, just the time part.
I'm doing this:
SELECT timeEvent FROM tbEvents WHERE convert(datetime, startHour, 8) >= convert(datetime, @startHour, 8)Is it correct?
I'm asking this because I need to know if 08:00:00 is less or greater than 07:30:00 and I don't want to compare the date, just the time part.
Thanks!
最满意答案
您的比较将会起作用,但是它会很慢,因为日期会转换为每行的字符串。 要有效地比较两个时间部分,请尝试:
declare @first datetime set @first = '2009-04-30 19:47:16.123' declare @second datetime set @second = '2009-04-10 19:47:16.123' select (cast(@first as float) - floor(cast(@first as float))) - (cast(@second as float) - floor(cast(@second as float))) as Difference长说明:SQL Server中的日期存储为浮点数。 小数点前的数字代表日期。 小数点后的数字代表时间。
所以这里是一个例子:
declare @mydate datetime set @mydate = '2009-04-30 19:47:16.123'我们把它转换成一个float:
declare @myfloat float set @myfloat = cast(@mydate as float) select @myfloat -- Shows 39931,8244921682现在把数字后面的部分,即时间:
set @myfloat = @myfloat - floor(@myfloat) select @myfloat -- Shows 0,824492168212601将其转换回datetime:
declare @mytime datetime set @mytime = convert(datetime,@myfloat) select @mytime -- Shows 1900-01-01 19:47:16.1231900-01-01只是“零”的日期; 您可以使用转换显示时间部分,例如格式为108,这只是时间:
select convert(varchar(32),@mytime,108) -- Shows 19:47:16datetime和float之间的转换相当快,因为它们基本上以相同的方式存储。
Your compare will work, but it will be slow because the dates are converted to a string for each row. To efficiently compare two time parts, try:
declare @first datetime set @first = '2009-04-30 19:47:16.123' declare @second datetime set @second = '2009-04-10 19:47:16.123' select (cast(@first as float) - floor(cast(@first as float))) - (cast(@second as float) - floor(cast(@second as float))) as DifferenceLong explanation: a date in SQL server is stored as a floating point number. The digits before the decimal point represent the date. The digits after the decimal point represent the time.
So here's an example date:
declare @mydate datetime set @mydate = '2009-04-30 19:47:16.123'Let's convert it to a float:
declare @myfloat float set @myfloat = cast(@mydate as float) select @myfloat -- Shows 39931,8244921682Now take the part after the digit, i.e. the time:
set @myfloat = @myfloat - floor(@myfloat) select @myfloat -- Shows 0,824492168212601Convert it back to a datetime:
declare @mytime datetime set @mytime = convert(datetime,@myfloat) select @mytime -- Shows 1900-01-01 19:47:16.123The 1900-01-01 is just the "zero" date; you can display the time part with convert, specifying for example format 108, which is just the time:
select convert(varchar(32),@mytime,108) -- Shows 19:47:16Conversions between datetime and float are pretty fast, because they're basically stored in the same way.
更多推荐
发布评论