I have a table named 'Table1'. It has 3 columns namely
我有一个名为'Table1'的表。它有3列即
1. vehicle Number
2. startdatetime
3. enddatetime
the table is as below->
表格如下 - >
vehicleno | startdatetime | enddatetime |
1 2013/07/16 00:00:00 2013/07/17 00:00:00
2 2013/07/16 00:00:00 2013/07/18 14:00:00
3 2013/07/17 12:19:00 2013/07/20 17:35:00
4 2013/07/19 10:24:56 2013/07/19 20:14:00
5 2013/07/15 08:10:00 2013/07/18 09:10:00
Now i want a o/p such that ,At the time of executing the query if the present datetime is between the startdatetime and enddatetime then the record should be displayed in my o/p table.
现在我想要一个o / p，以便在执行查询时，如果当前日期时间在startdatetime和enddatetime之间，那么记录应该显示在我的o / p表中。
I have tried with the query..
我试过了查询..
select * from Table1 where startdatetime between '2013/07/17 00:00:00' and '2013/07/17 23:59:59' or enddatetime between '2013/07/17 00:00:00' and '2013/07/17 23:59:59'
but i didn't get the result i want.
但我没有得到我想要的结果。
Please help me out..
请帮帮我..
4 个解决方案
#1
2
If you are looking for the current date why not use GETDATE?
如果您正在寻找当前日期，为什么不使用GETDATE？
Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.
返回当前数据库系统时间戳作为没有数据库时区偏移的日期时间值。此值派生自运行SQL Server实例的计算机的操作系统。
Something like
就像是
select *
from Table1
where GETDATE() BETWEEN startdatetime AND enddatetime
SQL Fiddle DEMO
#2
2
I would think you'd want to do:
我想你会想做：
select vehicleno ,startdatetime, enddatetime
from table1
where getUTCDate() between startdatetime and enddatetime
Then if you aren't in GMT you can do a dateAdd(hour, 5, getUTCDate()) if you are say EST.
然后，如果你不是格林尼治标准时间，你可以做一个dateAdd（小时，5，getUTCDate（）），如果你说EST。
It's always more correct to specify the columns you want to use in the select statement.
在select语句中指定要使用的列总是更正确。
And at this point since we live in a global community I feel it's more appropriate to use getUTCDate() instead of just regular getDate() because getDate() just does the timezone that the server is located in and it will depend on where the server is if this works for your situation.
此时，由于我们生活在一个全球社区，我觉得使用getUTCDate（）而不仅仅是常规的getDate（）更合适，因为getDate（）只是执行服务器所在的时区，它将取决于服务器的位置如果这适用于您的情况。
You can see the definition of GetUTCDate()
你可以看到GetUTCDate（）的定义
#3
1
Try the following:
请尝试以下方法：
select *
from Table1
where GetDate() between startdatetime and enddatetime
GetDate() gets the current datetime
GetDate（）获取当前日期时间
#4
0
Just for the record, if you want to use literal dates in SQL, use ISO date format (YYYY-MM-DD hh:mm:ss). That is the only format guaranteed to be interpreted correctly regardless of your locale settings.
仅供记录，如果要在SQL中使用文字日期，请使用ISO日期格式（YYYY-MM-DD hh：mm：ss）。无论您的语言环境设置如何，这是唯一可以保证正确解释的格式。
#1
2
If you are looking for the current date why not use GETDATE?
如果您正在寻找当前日期，为什么不使用GETDATE？
Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.
返回当前数据库系统时间戳作为没有数据库时区偏移的日期时间值。此值派生自运行SQL Server实例的计算机的操作系统。
Something like
就像是
select *
from Table1
where GETDATE() BETWEEN startdatetime AND enddatetime
SQL Fiddle DEMO
#2
2
I would think you'd want to do:
我想你会想做：
select vehicleno ,startdatetime, enddatetime
from table1
where getUTCDate() between startdatetime and enddatetime
Then if you aren't in GMT you can do a dateAdd(hour, 5, getUTCDate()) if you are say EST.
然后，如果你不是格林尼治标准时间，你可以做一个dateAdd（小时，5，getUTCDate（）），如果你说EST。
It's always more correct to specify the columns you want to use in the select statement.
在select语句中指定要使用的列总是更正确。
And at this point since we live in a global community I feel it's more appropriate to use getUTCDate() instead of just regular getDate() because getDate() just does the timezone that the server is located in and it will depend on where the server is if this works for your situation.
此时，由于我们生活在一个全球社区，我觉得使用getUTCDate（）而不仅仅是常规的getDate（）更合适，因为getDate（）只是执行服务器所在的时区，它将取决于服务器的位置如果这适用于您的情况。
You can see the definition of GetUTCDate()
你可以看到GetUTCDate（）的定义
#3
1
Try the following:
请尝试以下方法：
select *
from Table1
where GetDate() between startdatetime and enddatetime
GetDate() gets the current datetime
GetDate（）获取当前日期时间
#4
0
Just for the record, if you want to use literal dates in SQL, use ISO date format (YYYY-MM-DD hh:mm:ss). That is the only format guaranteed to be interpreted correctly regardless of your locale settings.
仅供记录，如果要在SQL中使用文字日期，请使用ISO日期格式（YYYY-MM-DD hh：mm：ss）。无论您的语言环境设置如何，这是唯一可以保证正确解释的格式。