SQL Server and getting records by date

Anon

I'm trying to extract all records for a given date from an SQL Server 7 database. Records are stamped with a timestamp like this: "1/18/01 10:53:31 AM"

Queries using "WHERE timestamp LIKE '1/18/01'" are not working.

What's the best way to select all records for a given date using the timestamp I have?

lawnmowerman

I'm not familiar with SQL Server 7, but I have a quick ref which has the following example:

SELECT order_dum FROM Orders WHERE order_date BETWEEN to_date( '01-JAN-1999' ) AND to_date( '31-DEC-1999' );

This is an Oracle statement, but in order to use it under SQL Server, simply replace to_date() with DATEPART()...per my quick ref, at least 🙂

-Rich

jblallement

Hi,

What is the datatype of your timestamp, date or char ?

Anon

Many thanks to the advice below which led me down the DATEPART direction. If anyone has a more elegant solution, I'd love to learn about it, but this is working for me now.

My task: given $date, a user-entered date (in the form 01/18/2001), do a simple count of all records entered that day. Records have a timestamp stored in an SQL Server date/time field. Using PHP4 on a Linux box and SQL Server 7.

First, I exploded the user-entered date using the "/" separator.

$data = explode("/", $date);

Now I use the resulting array to search my table for records matching the month, day of month, and year I want.

$query = "SELECT id FROM tablename WHERE datepart(month, [timestamp]) = '$data[0]' AND datepart(day, [timestamp]) = '$data[1]' AND datepart(year, [timestamp]) = '$data[2]'";

jblallement

If timestamp is stored in a DATETIME field then you could simply write:

---- BEGIN SQL ----

SELECT id
FROM tablename
WHERE timestamp >= CAST('$date' AS DATETIME)
AND timestamp < CAST('$date' AS DATETIME) +1

---- END SQL ----

This way, you don't ahve to alter the timestamp field. It's much more efficient in term of data access.

Hope this helps