asked on

CONVERT datetime stamp to date

I have a field that I would like to only display the date as mm/dd/yyyy. Current field shows mm/dd/yyyy hh:mm:ss AM.

I would most likely create a view that will create a field that will display the date only.

ASKER CERTIFIED SOLUTION

KarinLoos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lowfatspread

give the points to Karin ,
but i'd use

select convert( char(10), datetimefieldname, 101 )

its going to be 10 characters at most , and even with leading zeros being suppressed the additional 2 byte length
overhead of varchar is too much...

lfs

chippyles

ASKER

Do I get my points back. I found the solution before the answer was posted?

wtf666

since i had to do the same thing in sql server so i used this query for conversion and then applying criteria on ur conversion function ::. thought might be helpful for someone else :)

SELECT TOP 100 PERCENT dbo.BillTo.Company AS CustName, dbo.BillTo.City, dbo.Salseman.Name AS SalesRep, dbo.BillTo.AccountNo AS CustNo,
dbo.Orders.OrderNo, dbo.Orders.Total, dbo.Orders.EnteredDate, CONVERT(CHAR(10), dbo.Orders.EnteredDate, 101) AS _EnteredDate,
dbo.Orders.ReqDate, dbo.Orders.EnteredBy
FROM dbo.Salseman INNER JOIN
dbo.BillTo INNER JOIN
dbo.Orders ON dbo.BillTo.RecordID = dbo.Orders.BillToID ON dbo.Salseman.SalesmanID = dbo.Orders.SalesmanID
WHERE (dbo.Orders.EnteredDate IS NOT NULL) AND (CONVERT(CHAR(10), dbo.Orders.EnteredDate, 101) >= CONVERT(DATETIME, '3/29/2005 00:00:00', 102))
AND (CONVERT(CHAR(10), dbo.Orders.EnteredDate, 101) <= CONVERT(DATETIME, '3/30/2005 00:00:00', 102))
ORDER BY dbo.Orders.Total DESC

adios
Ade