SQL query

Anon

Hello All.

I am trying to select rows from the table that along with other fields, have date and time fileds.
if I want to select a certain date and time for my starting point and till my ending point in same format, how would I go about doing it?

Shortly I want to select something like this

from 5/1/2001 12:07:33 PM till 5/7/2001 7:43:23 AM ( considering date time in 2 different fileds of database)

Help will be greatly appriciated
Thanks a lot

neuromancer

Is this what your looking for?

SELECT * FROM x WHERE DateTimeColumnInX BETWEEN '1/1/2000' AND '2/1/2000'

Anon

The only problem with this query is that it will pull up the dates in the wrong order.

Your date format needs to be yyyy-mm-dd for this to work correctly.

cheers,
scottD~

Anon

No but thanks for the replay...
there is more to it... also need to check the time.

Such as..
May 1 2001 from 12.30 AM till May 30 2001 2.00 PM

A query that will do on above..

Thank you.
Karaman

fleymingmasc

Looks like you have 2 different fields; let's say they are named from_date and to_date. Here's the MySQL syntax (DATETIME format may vary from DBMS to DBMS):

SELECT * FROM table_name
WHERE from_date >= '2001-05-01 12:07:33'
AND to_date <= '2001-05-07 07:43:23'

If you are using one datetime field for this comparison, it's even easier:

SELECT * FROM table_name
WHERE date_field BETWEEN '2001-05-01 12:07:33' AND '2001-05-07 07:43:23'

If you are using MSSQL, I believe the syntax for DATETIME is dependent upon your locale settings. In the USA it is:
'05/01/2001 12:07:33 PM'
but since MS is pretty forgiving you can put in any recognized date format (i.e. '1 May 2001 12:07:33'). Also, MSSQL and many DBMS's allow you to specify milliseconds, in which case the format is:
'05/01/2001 12:07:33.045 PM'

For other DBMS's check your manual under DATETIME to determine correct format.

Hope this helps.