Convert SQL datetime to just date

kozu

Hi, I need to convert the DateTime to a date format. I know I have to use Convert, but I don't think I use it right because I'm not getting the expected result. Here's my scenarion: I want to do a sum for all products in the DB that were bought during a given period of time. My query looks like this select convert(datetime,convert(char(10),OrderDate,101)), SUM(Qty) from Orders where convert(datetime,convert(char(10),OrderDate,101)) between '05/29/05' and '06/10/05' group by convert(datetime,convert(char(10),OrderDate,101)) order by convert(datetime,convert(char(10),OrderDate,101)) the result looks like this: OrderDate Qty 2005-05-30 00:00:00.000 1 2005-05-31 00:00:00.000 4 How can I get rid of the 0s at the end of OrderDate? thanx

kubben

You can change your where clause to be: where OrderDate >= '2005-05-29' and OrderDate <= '2005-06-10 23:59:59' Hope that helps. Ben

kozu

Kubben, Thanks for the reply, but changing the where clause didn't work. I need to change the 'convert' part of the select in order to alter how the data is being displayed, but to what? I tried casting it to char, and it partially fixed my problem: select cast(OrderDate as char(11)), SUM(Qty) from Orders where convert(datetime,convert(char(10),OrderDate,101)) between '05/29/05' and '06/10/05' group by cast(OrderDate as char(11)) order by cast(OrderDate as char(11)) The result is: Jun 1 2005 2 Jun 2 2005 1 May 30 2005 1 but now the problem is that the ordering will be done alphabetically and not by date. stuck again :(

Shahil Shah

Hi If you are viewing the data in a datagrid you can use the following data formating expression: {0:dd/MM/yyyy} without the need to use the convert staement you are using otherwise try this: SET DATEFORMAT mdy select CAST(OrderDate as varchar(11)) as OrderDate, SUM(Qty) where OrderDate between '05/29/05' and '06/10/05' group by OrderDate order by OrderDate Shahil

kozu

Shahil, The 'order by' will do the sorting in alphabetical order, since now OrderDate is a string, so I'll get 'June' before 'May'. I need to be able to still sort by date. thanx

kozu

okay, I think I found a way around this: add a dummy column and sort by that select cast(OrderDate as char(11)), SUM(Qty), convert(datetime,convert(char(10),OrderDate,101)) from Orders where convert(datetime,convert(char(10),OrderDate,101)) between '05/29/05' and '06/10/05' group by cast(OrderDate as char(11)), convert(datetime,convert(char(10),OrderDate,101)) order by convert(datetime,convert(char(10),OrderDate,101))

Shahil Shah

Hi kozu try this: create table #tmp ( OrderDate datetime, qty int ) insert into #tmp(orderdate, qty) select OrderDate, sum(qty) from orders where OrderDate between '05/29/80' and '06/10/05' group by OrderDate order by OrderDate select CAST(OrderDate as varchar(11)) as OrderDate, Qty from #tmp DROP TABLE #tmp Shahil

Michael Potter

Form your result set then use it as the source for the final query where you can format your data.

SELECT
    CONVERT(VARCHAR(20),OrderDate,101) AS OrderDate,
    TotalQty
FROM
    (
        SELECT  
            CAST(CONVERT(VARCHAR(20),OrderDate,101) AS DATETIME) AS OrderDate, 
            SUM(Qty) AS TotalQty
        FROM 
            Orders
        WHERE 
            CAST(CONVERT(VARCHAR(20),OrderDate,101) AS DATETIME) between '05/29/05' and '06/10/05'
        GROUP BY 
            CAST(CONVERT(VARCHAR(20),OrderDate,101) AS DATETIME)
    ) AS subqry
ORDER BY 
    OrderDate

Speed wise, I think kubben is correct. I would remove the CAST/CONVERT in the WHERE clause of the subquery with a little intelligent manipulation of the date ranges.