extract date from datetime stamp

Question

Anyone know how to extract the date from a datetime stamp as part of the where clause?

eg.

 select *
 from tableA
 where date between '01/08/2009' and '31/08/2009'

(Date is a timestamp!)

Many thanks, Fiona

Answer 1

If this is sql server, it's not possible. The timestamp data type's name is misleading, as it does not store any date information of any kind. All it holds is a sequential value that allows you to establish record order (eg, item A was created before item B), and therefore you don't have enough information in that column alone to know on what day the row was created.

Since the link I provided is Sql Server 2000 specific, also check this link for information on SQL Server 2008:
http://msdn.microsoft.com/en-us/library/ms182776.aspx

timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. In DDL statements, use rowversion instead of timestamp wherever possible.

To build a real timestamp column in Sql Server, use a DateTime (or DateTime2) data type and set it's default value to getdate() or current_timestamp.

Answer 2

If a real datetime value, not TIMESTAMP/ROWVERSION which is binary(8)...

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @MyValue), 0)