round date

Hi All,
Maybe this is easy but I can't find the way to fix this.
I want to get total and number of orders for each date. I use this query...
SELECT cast(o.orderdate as smalldatetime) , sum(od.unitprice), count(*) as
counter
FROM Orders o
INNER JOIN orderdetails od on
o.orderid = od.orderid
group by o.orderdate
ORDER BY o.orderdate desc
Is not working because my orderdate column contains also the time. I try to
round the date but not luck.
how can I fix this?
Any ideas...
Tks in advance
JFBFirst, to fix yr problem, Strip the time off in the expressions in your que
ry
SELECT convert (varchar(8), o.orderdate , 112) OrderDate,
sum(od.unitprice) Total, count(*) counter
FROM Orders o
JOIN orderdetails od
on o.orderid = od.orderid
Group by convert (varchar(8), o.orderdate , 112)
ORDER BY convert (varchar(8), o.orderdate , 112) desc
Next, if you don't have any need for the time portion in this field, (And
never will) consider strippinmg it off and only storing the date in the firs
t
place... or, Storing the date and time portions in separate columns...
"JFB" wrote:

> Hi All,
> Maybe this is easy but I can't find the way to fix this.
> I want to get total and number of orders for each date. I use this query..
.
> SELECT cast(o.orderdate as smalldatetime) , sum(od.unitprice), count(*) as
> counter
> FROM Orders o
> INNER JOIN orderdetails od on
> o.orderid = od.orderid
> group by o.orderdate
> ORDER BY o.orderdate desc
> Is not working because my orderdate column contains also the time. I try t
o
> round the date but not luck.
> how can I fix this?
> Any ideas...
> Tks in advance
> JFB
>
>|||One easy way to drop the time portion from a datetime field is to do a
FLOOR function but only after casting to FLOAT first:
declare @.dtNow datetime
declare @.dtToday datetime
set @.dtNow = GetDate()
set @.dtToday = floor(cast(@.dtNow as float))
select @.dtNow
select @.dtToday
You see that the @.dtNow has the time included but the @.dtToday has been
truncated to midnight.
If you apply this "function" to your data you can compare all datetimes as
the equvilent same day/date
Message posted via http://www.webservertalk.com|||Great... i try almost the same but with 101 format and it didn't give me the
right order.
I appreciate this ... Tks for you help.
JFB
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:C862E5FA-B9DB-4F0C-830A-B79F4222BA07@.microsoft.com...
> First, to fix yr problem, Strip the time off in the expressions in your
query
> SELECT convert (varchar(8), o.orderdate , 112) OrderDate,
> sum(od.unitprice) Total, count(*) counter
> FROM Orders o
> JOIN orderdetails od
> on o.orderid = od.orderid
> Group by convert (varchar(8), o.orderdate , 112)
> ORDER BY convert (varchar(8), o.orderdate , 112) desc
> Next, if you don't have any need for the time portion in this field, (And
> never will) consider strippinmg it off and only storing the date in the
first
> place... or, Storing the date and time portions in separate columns...
>
> "JFB" wrote:
>
query...
as
to|||Much faster, and simpler is to cast to Integer, as can be seen by
Select Cast(Cast(getdate() as integer) as dateTime)
"Geoffrey Kahan via webservertalk.com" wrote:

> One easy way to drop the time portion from a datetime field is to do a
> FLOOR function but only after casting to FLOAT first:
> declare @.dtNow datetime
> declare @.dtToday datetime
> set @.dtNow = GetDate()
> set @.dtToday = floor(cast(@.dtNow as float))
> select @.dtNow
> select @.dtToday
> You see that the @.dtNow has the time included but the @.dtToday has been
> truncated to midnight.
> If you apply this "function" to your data you can compare all datetimes as
> the equvilent same day/date
> --
> Message posted via http://www.webservertalk.com
>