Posted by David Portas on 01/31/06 01:57

Edward wrote:

> Hi group,
>
> Using Oracle, and given that $date=30/01/2006, you can construct a query as
> follows:
>
> select * from table
> where date_field between to_date('$date', 'dd/mm/yyyy') and to_date('$date',
> 'dd/mm/yyyy')+1
>
> What is the equivalent with MSSQL?
>
> Thanks,
>
> Edward

DECLARE @date DATETIME ;
SET @date = '20060130' ;

SELECT *
FROM table
WHERE date_col >= @date
AND date_col < DATEADD(DAY,1,@date) ;

In SQL Server DATETIME / SMALLDATETIME always includes time as well as
date. The above query restricts to a 24hr daterange so adjust the
DATEADD or < accordingly if that isn't what you intended.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]