convert date from 04/20/2014 to text or int 140420

Posted on 2014-08-06
Last Modified: 2014-08-08
I'm going a bit blank on trying to make the conversion. The other thing is that the output needs to be a string or int, not date format

thanks in advance.
Question by:damixa
    LVL 39

    Expert Comment

    SELECT cast(datepart(year, '04/20/2014') as sysname) + cast(datepart(month, '04/20/2014') as sysname) + cast(datepart(day, '04/20/2014') as sysname);

    Author Comment

    thanks for the answer. Is there a way to incorporate the leading zero as well?
    LVL 65

    Expert Comment

    by:Jim Horn
    Copy-paste the below code into your SSMS, execute it to verify it meets your requirement, then motify
    Declare @dt date = '2014-04-20'
    SELECT RIGHT(CAST(YEAR(@dt) as varchar(4)),2)
       + RIGHT('0' + CAST(MONTH(@dt) as varchar(2)),2)
       + RIGHT('0' + CAST(DAY(@dt) as varchar(2)),2)

    Open in new window

    LVL 65

    Expert Comment

    by:Jim Horn
    btw I executed the code in the first comment in this question in SQL 2012 SSMS and it returned 2014420, which is not correct.
    LVL 68

    Expert Comment

    For char/varchar:
    SELECT CONVERT(varchar(8), date_column, 112)

    For int:
    SELECT CAST(CONVERT(varchar(8), date_column, 112) AS int)
    LVL 47

    Accepted Solution

    YYMMDD is style 12

    convert(varchar, your_data_here ,12) -- varchar

    convert(int,convert(varchar, your_data_here ,12)) -- now int


    Author Closing Comment


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    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

    19 Experts available now in Live!

    Get 1:1 Help Now