CONVERT datetime stamp to date

Posted on 2004-11-29
Last Modified: 2011-08-18
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.  
Question by:chippyles
    LVL 13

    Accepted Solution

    use the convert function  ie:
    select  convert( varchar(20), datetimefieldname, 101 )

    LVL 50

    Expert Comment

    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...


    Author Comment

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

    Expert Comment

    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


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now