How to format a DATETIME field in yyyy-mm-dd without dropping any leading zeros?

Posted on 2005-04-19
Last Modified: 2010-05-18
How can I format a DATETIME field in yyyy-mm-dd without dropping any leading zeros?
Question by:rocketTendon
    LVL 28

    Accepted Solution

    Try this:

    SELECT CONVERT(VARCHAR(10), YourDate, 111)
    LVL 28

    Expert Comment

    Another way:

    SELECT CAST(YEAR(YourDate) AS VARCHAR(4)) + '-' + RIGHT('00' + CAST(MONTH(YourDate) AS VARCHAR(2)), 2) + '-'  + RIGHT('00' + CAST(DAY(YourDate) AS VARCHAR(2)), 2)
    LVL 3

    Author Comment


    Used the following:

    replace(CONVERT(VARCHAR(10), [Sale Start], 111),'/','-')
    LVL 75

    Expert Comment

    by:Anthony Perkins
    I suspect you will find the following more efficient (no need for the REPLACE):
    Select CONVERT(VARCHAR(10), [Sale Start], 120)
    LVL 3

    Author Comment

    Will do.. thanks acp.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    737 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

    23 Experts available now in Live!

    Get 1:1 Help Now