CODEWITHSUNDEEP
sql-serversql-server-2008
Pravin Kumar
Pravin Kumar

Reputation: 693

filter a sql statement using its column values

I have a table named 'Table1'. It has 3 columns namely 

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.

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

Upvotes: 0

Views: 205

Answers (4)

Curt
Curt

Reputation: 5722

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.

Upvotes: 0

Avitus
Avitus

Reputation: 15958

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.

It's always more correct to specify the columns you want to use in the select statement.

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.

You can see the definition of GetUTCDate()

Upvotes: 2

JanR
JanR

Reputation: 6132

Try the following:

select * 
from Table1 
where GetDate() between startdatetime and enddatetime

GetDate() gets the current datetime

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166396

If you are looking for the current date why not use 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.

Something like

select  * 
from    Table1 
where   GETDATE() BETWEEN startdatetime  AND enddatetime

SQL Fiddle DEMO

Upvotes: 2

Related Questions