Between Clause

Hai,

I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.

I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.

But the above query returns upto 10/30/2005.

Please advise me.

Rgds,
Soura

This works fine for me.

create table test(id int, testdate datetime)

insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')

select * from test where testdate between '10/01/2005' and '10/31/2005'

Please post you data and ddl.

Thanks
Amish

*** Sent via Developersdex http://www.droptable.com ***

This should contain all necessary information: http://www.karaszi.com/SQLServer/in...fo_datetime.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"SouRa" <SouRa@discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94- 2D16F715E493@microso
ft.com...
> Hai,
>
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
>
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
>
> But the above query returns upto 10/30/2005.
>
> Please advise me.
>
> Rgds,
> Soura

Soura,

'10/31/2005' assumes 10/31/2005 at midnight.  Any records with a time of
after midnight will not be included in the results.

Try:

select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'

Rob

SouRa  wrote:
> Hai,
>
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
>
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
>
> But the above query returns upto 10/30/2005.
>
> Please advise me.
>
> Rgds,
> Soura

> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'

or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.

--
William Stacey [MVP]

Hi Rob,

Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.

I have one method,

"select * from table_name where
 convert(datetime,con
 vert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'

it is working fine.
Can  you tell me it is efficient one. Please advise me.

rgds,
Soura

"Rob" wrote:

> Soura,
>
> '10/31/2005' assumes 10/31/2005 at midnight.  Any records with a time of
> after midnight will not be included in the results.
>
> Try:
>
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
>
> Rob
>
> SouRa wrote: 
>

Hi William ,

Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.

I have one method,

"select * from table_name where
 convert(datetime,con
 vert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'

it is working fine.
Can  you tell me it is efficient one. Please advise me.

rgds,
Soura


"William Stacey [MVP]" wrote:
 
>
> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
>
> --
> William Stacey [MVP]
>
>
>
>

Did you read my article?

> "select * from table_name where
>  convert(datetime,con
 vert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'

Above will negate the usage of indexes on the column. Can potentially be dis
astrous for performance.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"SouRa" <SouRa@discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673- A47EC0A60E7E@microso
ft.com...
> Hi Rob,
>
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each tim
e.
>
> I have one method,
>
> "select * from table_name where
>  convert(datetime,con
 vert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
>
> it is working fine.
> Can  you tell me it is efficient one. Please advise me.
>
> rgds,
> Soura
>
> "Rob" wrote:
>