date + 1 ???

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

Edward wrote:

> Hi group,
>
> Using Oracle, and given that $date=30/01/2006, you can construct a query a
s
> 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
--

thanks
"David Portas" < REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote in message
news:1138665436.223913.75090@f14g2000cwb.googlegroups.com...
> Edward wrote:
> 
>
> 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
> --
>