Very Computer

by **Jac** » Thu, 02 Aug 2001 04:16:44

I want all rows that have a date column set to a particular day (give
me all rows with date column of '7/25/01'). The dates come in from
the users in the format 'mm/dd/yy'.

I did somthing like this:

select * from order_tbl
where order_date between '7/25/01' and dateadd(day, 1,'7/25/01')

Is this the best way to do this?

Thanks,

Jack

by **Steve Kas** » Thu, 02 Aug 2001 04:30:23

No. That will return everything with order dates of 7/25 or 7/26. You
can get only a single date this way. There are other ways, as well,
using CONVERT(), and if efficiency is an issue, you might look up
CONVERT() in Books Online and see what you can do with it.

Steve Kass
Drew University

select * from order_tbl

> I want all rows that have a date column set to a particular day (give
> me all rows with date column of '7/25/01'). The dates come in from
> the users in the format 'mm/dd/yy'.

> I did somthing like this:

> select * from order_tbl
> where order_date between '7/25/01' and dateadd(day, 1,'7/25/01')

> Is this the best way to do this?

> Thanks,

> Jack

by **Rich Dillo** » Thu, 02 Aug 2001 04:47:14

Jack,

If you have an index on order_date, it would be best to keep it outside of a
function as you've done. To avoid getting '7/25/01' your best bet would be:

select * from order_tbl
where order_date >= '7/25/01' and order_date < dateadd(day,1,'7/25/01')

If you want, you could also do this:

select * from order_tbl
where order_date between '7/25/01' and dateadd(ms,86399997,'7/25/01')

Hope that helps,

- Rich

Quote:> I want all rows that have a date column set to a particular day (give
> me all rows with date column of '7/25/01'). The dates come in from
> the users in the format 'mm/dd/yy'.

> I did somthing like this:

> select * from order_tbl
> where order_date between '7/25/01' and dateadd(day, 1,'7/25/01')

> Is this the best way to do this?

> Thanks,

> Jack

by **Babu** » Thu, 02 Aug 2001 04:57:01

You can use convert() to resolve this

select * from order_tbl where convert(varchar,order_date,101) = '07/25/2001'
hope this helps

Quote:> I want all rows that have a date column set to a particular day (give
> me all rows with date column of '7/25/01'). The dates come in from
> the users in the format 'mm/dd/yy'.

> I did somthing like this:

> select * from order_tbl
> where order_date between '7/25/01' and dateadd(day, 1,'7/25/01')

> Is this the best way to do this?

> Thanks,

> Jack

by **Tony Rogerso** » Thu, 02 Aug 2001 05:07:49

Using a function on a column will mean SQL Server can only do an index scan
rather than a seek which is less efficient.

Try and do stuff like this up front....

SELECT *
FROM order_tbl
WHERE order_date BETWEEN CAST( CONVERT( varchar(20), GETDATE(), 106 ) + '
00:00:00.000' AS datetime )
AND CAST( CONVERT( varchar(20),
GETDATE(), 106 ) + ' 23:59:59.999' AS datetime )

--
Tony Rogerson SQL Server MVP
Independant Consultant
Torver Computer Consultants Ltd
www.sql-server.co.uk [UK Independent SQL Server User Group; FAQ; KBase
etc..]

> No. That will return everything with order dates of 7/25 or 7/26. You
> can get only a single date this way. There are other ways, as well,
> using CONVERT(), and if efficiency is an issue, you might look up
> CONVERT() in Books Online and see what you can do with it.

> Steve Kass
> Drew University

> select * from order_tbl

> > I want all rows that have a date column set to a particular day (give
> > me all rows with date column of '7/25/01'). The dates come in from
> > the users in the format 'mm/dd/yy'.

> > I did somthing like this:

> > select * from order_tbl
> > where order_date between '7/25/01' and dateadd(day, 1,'7/25/01')

> > Is this the best way to do this?

> > Thanks,

> > Jack

by **Umachandar Jayachandra** » Thu, 02 Aug 2001 05:42:52

Quote:>> GETDATE(), 106 ) + ' 23:59:59.999' AS datetime )

999 milli-seconds will roll over to the next day and you will end up
getting more rows than requested. Easiest way is to do:

Order_date >= CONVERT( varchar, GETDATE(), 112 ) And
Order_date < DATEADD( day, 1, CONVERT( varchar, GETDATE(), 112 ) )

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

Very Computer

select date range of one day

select date range of one day

select date range of one day

select date range of one day

select date range of one day

select date range of one day

select date range of one day