date + 1 ???

[Edward]

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

[David Portas]

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/m...S,SQL.90).aspx
--

[Edward]

thanks
"David Portas" <RE****************************@acm.org> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

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/m...S,SQL.90).aspx
--