Queries with Dates

New to SQL.
I'm trying to include 2 dates in a query. They query works, but not
logically. I'm sure I'm missing something small.
When the query runs, it doesn't include dates that have times later than
12:00:00AM. How do I get rid of that?
SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.OrderDate,
dbo.Products.ProductName
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
Details].OrderID INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID =
dbo.Products.ProductID
WHERE (dbo.Orders.OrderDate >= @.ordDate) AND (dbo.Orders.OrderDate <=
@.ordDate2)This example should demonstrate.
DECLARE @.ordDate datetime
DECLARE @.ordDate2 datetime
SET @.ordDate '20060531' -- As no time is specified this will be interpreted
as '20060531 00:00'
SET @.ordDate2 '20060531 23:59:59'
To capture all data on the 31-May-2006 your WHERE clause would be :-
WHERE dbo.Orders.OrderDate BETWEEN @.ordDate AND @.ordDate2
HTH. Ryan
"Tony K" <king-tony2@.comcast.nospam.net> wrote in message
news:O%23IUoRFhGHA.4404@.TK2MSFTNGP05.phx.gbl...
> New to SQL.
> I'm trying to include 2 dates in a query. They query works, but not
> logically. I'm sure I'm missing something small.
> When the query runs, it doesn't include dates that have times later than
> 12:00:00AM. How do I get rid of that?
> SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID,
> dbo.Orders.OrderDate, dbo.Products.ProductName
> FROM dbo.Orders INNER JOIN
> dbo.[Order Details] ON dbo.Orders.OrderID =
> dbo.[Order Details].OrderID INNER JOIN
> dbo.Products ON dbo.[Order Details].ProductID =
> dbo.Products.ProductID
> WHERE (dbo.Orders.OrderDate >= @.ordDate) AND (dbo.Orders.OrderDate <=
> @.ordDate2)
>|||>SET @.ordDate2 '20060531 23:59:59'
To be strict about it there is a very small chance this will not work
because it ignores the milliseconds. But you can not simply add .999
to the end. Consider this query and results:
select 990, convert(datetime,'20060531 23:59:59.990') UNION ALL
select 991, convert(datetime,'20060531 23:59:59.991') UNION ALL
select 992, convert(datetime,'20060531 23:59:59.992') UNION ALL
select 993, convert(datetime,'20060531 23:59:59.993') UNION ALL
select 994, convert(datetime,'20060531 23:59:59.994') UNION ALL
select 995, convert(datetime,'20060531 23:59:59.995') UNION ALL
select 996, convert(datetime,'20060531 23:59:59.996') UNION ALL
select 997, convert(datetime,'20060531 23:59:59.997') UNION ALL
select 998, convert(datetime,'20060531 23:59:59.998') UNION ALL
select 999, convert(datetime,'20060531 23:59:59.999')
order by 1
-- --
990 2006-05-31 23:59:59.990
991 2006-05-31 23:59:59.990
992 2006-05-31 23:59:59.993
993 2006-05-31 23:59:59.993
994 2006-05-31 23:59:59.993
995 2006-05-31 23:59:59.997
996 2006-05-31 23:59:59.997
997 2006-05-31 23:59:59.997
998 2006-05-31 23:59:59.997
999 2006-06-01 00:00:00.000
As you can see, SQL Server doesn't quite work in milliseconds, and
23:59:59.999 resolves to the NEXT DAY. However, you have four choices
for the largest value for the same day, .995 through .998.
Roy Harvey
Beacon Falls, CT
On Wed, 31 May 2006 09:44:24 +0100, "Ryan"
<Ryan_Waight@.nospam.hotmail.com> wrote:

>This example should demonstrate.
>DECLARE @.ordDate datetime
>DECLARE @.ordDate2 datetime
>SET @.ordDate '20060531' -- As no time is specified this will be interpreted
>as '20060531 00:00'
>SET @.ordDate2 '20060531 23:59:59'
>To capture all data on the 31-May-2006 your WHERE clause would be :-
>WHERE dbo.Orders.OrderDate BETWEEN @.ordDate AND @.ordDate2|||Valid point, well made.
HTH. Ryan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:v7vq72haq4c3a3npo7krbl1qceb4emk7mc@.
4ax.com...
> To be strict about it there is a very small chance this will not work
> because it ignores the milliseconds. But you can not simply add .999
> to the end. Consider this query and results:
> select 990, convert(datetime,'20060531 23:59:59.990') UNION ALL
> select 991, convert(datetime,'20060531 23:59:59.991') UNION ALL
> select 992, convert(datetime,'20060531 23:59:59.992') UNION ALL
> select 993, convert(datetime,'20060531 23:59:59.993') UNION ALL
> select 994, convert(datetime,'20060531 23:59:59.994') UNION ALL
> select 995, convert(datetime,'20060531 23:59:59.995') UNION ALL
> select 996, convert(datetime,'20060531 23:59:59.996') UNION ALL
> select 997, convert(datetime,'20060531 23:59:59.997') UNION ALL
> select 998, convert(datetime,'20060531 23:59:59.998') UNION ALL
> select 999, convert(datetime,'20060531 23:59:59.999')
> order by 1
> -- --
> 990 2006-05-31 23:59:59.990
> 991 2006-05-31 23:59:59.990
> 992 2006-05-31 23:59:59.993
> 993 2006-05-31 23:59:59.993
> 994 2006-05-31 23:59:59.993
> 995 2006-05-31 23:59:59.997
> 996 2006-05-31 23:59:59.997
> 997 2006-05-31 23:59:59.997
> 998 2006-05-31 23:59:59.997
> 999 2006-06-01 00:00:00.000
> As you can see, SQL Server doesn't quite work in milliseconds, and
> 23:59:59.999 resolves to the NEXT DAY. However, you have four choices
> for the largest value for the same day, .995 through .998.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 31 May 2006 09:44:24 +0100, "Ryan"
> <Ryan_Waight@.nospam.hotmail.com> wrote:
>|||All these things is the reason I wrote http://www.karaszi.com/SQLServer/in...
ime.asp
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eiAcoeKhGHA.896@.TK2MSFTNGP02.phx.gbl...
> Valid point, well made.
> --
> HTH. Ryan
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:v7vq72haq4c3a3npo7krbl1qceb4emk7mc@.
4ax.com...
>|||On Wed, 31 May 2006 13:54:22 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>All these things is the reason I wrote http://www.karaszi.com/SQLServer/in...br />
time.asp
>
>:-)
Very nice. I've got to catch up on all the articles you guys wrote
since I was last around!
Roy|||Thank you everybody. I'll look at the website and write back if I have
other issues.
Thank you everybody!
Tony
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6t2s729hu1bdn521lbur5qea0ia4ct55a8@.
4ax.com...
> On Wed, 31 May 2006 13:54:22 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Very nice. I've got to catch up on all the articles you guys wrote
> since I was last around!
> Roy