[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 52076
  • Last Modified:

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.  
0
chippyles
Asked:
chippyles
1 Solution
 
KarinLoosCommented:
use the convert function  ie:
select  convert( varchar(20), datetimefieldname, 101 )

0
 
LowfatspreadCommented:
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
 
 
0
 
chippylesAuthor Commented:
Do I get my points back.  I found the solution before the answer was posted?
0
 
wtf666Commented:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now