|
Home > Archive > MS SQL Server > December 2005 > Between Clause
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| 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
| |
| Amish Shah 2005-12-27, 7:23 am |
| 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 ***
| |
|
|
|
| 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
| |
| William Stacey [MVP] 2005-12-27, 1:23 pm |
| > 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]
>
>
>
>
| |
| Tibor Karaszi 2005-12-30, 3:23 am |
| 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 disastrous 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...[color=darkred]
> 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:
>
|
|
|
|
|