Remove hour/min/second from date

Posted on 2007-08-06
Last Modified: 2013-11-05
I would like to remove the following time element. How would I update it.

6/27/2007 9:00:  >>>>>  to display as 6/27/06
 7/1/2007  7:00 :34 >>>> 7/1/2007
4/2/2007 5:20:45>>>>> 4/2/07
Question by:zachvaldez
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    Hello zachvaldez,

    SELECT CONVERT (Varchar, urDateColumn,101)


    Aneesh R
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    > How would I update it.
    if it is a datetime column, you can't remove the time part, what you can do is, can update those min/secs to zero

    update urTable
    SET urDateColumn = CONVERT(VARCHAR,urdateColumn,112)

    in case you dont want to show the time portions, you can use the above query

    LVL 42

    Accepted Solution

    To remove the time component from a datetime column:

    update YourTable
    set YourDate = convert(datetime,convert(varchar(10),YourDate,101),101)

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    754 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

    22 Experts available now in Live!

    Get 1:1 Help Now