select date from datetime field in sql server 2008

[JohnDW]

Hello,

I Want to make a query that is selecting the
date part from a datetime field in sql server 2008 db, and order the query
on
the date field the query selected.
I have the following query that
selects the datetime field,
(underlined). I want to chanche that by
selecting the date part from that field.

[/COLOR][/SIZE]
[FONT=courier][SIZE=2]SELECT     TOP (100) PERCENT dbo.Orders.Klantnummer, [U](dbo.Orders.Orderdatum) AS Date[/U], dbo.Orderdetail.Verkoopprijs, dbo.Orderdetail.Tal,                       SUM(dbo.Orderdetail.Verkoopprijs * dbo.Orderdetail.Tal) AS Expr1, dbo.Orders.Store, dbo.Orders.IsBetaaldFROM         dbo.Orders INNER JOIN                      dbo.Orderdetail ON dbo.Orders.Orderid = dbo.Orderdetail.OrderIdGROUP BY dbo.Orders.Klantnummer, [U](dbo.Orders.Orderdatum)[/U] , dbo.Orderdetail.Verkoopprijs, dbo.Orderdetail.Tal, dbo.Orders.Store, dbo.Orders.IsBetaaldHAVING      (dbo.Orderdetail.Tal <> 0) AND (dbo.Orders.Store IS NULL) AND (dbo.Orders.IsBetaald <> 3)ORDER BY [U](dbo.Orders.Orderdatum)[/U] DESC[/SIZE][/FONT]

Can someone help?

Txs,

John

 

[Herman]

CONVERT(DATE, dbo.Orders.Orderdatum) AS [Date]

Keep in mind that the DATE datatype was only introduced in SQL Server 2008, so if you need to keep compatibility with SQL Server 2005, do the field formatting in the displaying control. Also because 'Date' is a reserved word, make sure you use the brackets. Or better yet change the alias.

 

[JohnDW]

I've changed the query

SELECT     TOP (100) PERCENT dbo.Orders.Klantnummer, CONVERT(DATE, dbo.Orders.Orderdatum)  AS Datum, dbo.Orderdetail.Verkoopprijs, dbo.Orderdetail.Tal, 
                      SUM(dbo.Orderdetail.Verkoopprijs * dbo.Orderdetail.Tal) AS Expr1, dbo.Orders.Store, dbo.Orders.IsBetaald
FROM         dbo.Orders INNER JOIN
                      dbo.Orderdetail ON dbo.Orders.Orderid = dbo.Orderdetail.OrderId
GROUP BY dbo.Orders.Klantnummer,  CONVERT(DATE, dbo.Orders.Orderdatum), dbo.Orderdetail.Verkoopprijs, dbo.Orderdetail.Tal, dbo.Orders.Store, 
                      dbo.Orders.IsBetaald
HAVING      (dbo.Orderdetail.Tal <> 0) AND (dbo.Orders.Store IS NULL) AND (dbo.Orders.IsBetaald <> 3)
ORDER BY Datum DESC

But i get an error.
ERROR: CANNOT CALL METHODS ON DATE

John