SQL Query - parse string

Posted on 2011-04-27
Last Modified: 2012-06-27
I need help parsing a date so that it is in the format I need it to be for sql query.

2011-04-26 05:53:05.000   -->  20110426055305
2011-04-26 13:12:09.000   -->  20110426131209

Question by:holemania
    LVL 73

    Assisted Solution

    if you'll always have .000 then simple replace will work

    LVL 40

    Accepted Solution

    try this.
    select REPLACE(REPLACE(REPLACE(convert(varchar,date_col,120),'-',''),':',''),' ','')

    Open in new window

    declare @date datetime
    select @date = '2011-04-26 05:53:05.000'
    select REPLACE(REPLACE(REPLACE(convert(varchar,@date,120),'-',''),':',''),' ','')
    -- 20110426055305
    select @date = '2011-04-26 13:12:09.000'
    select REPLACE(REPLACE(REPLACE(convert(varchar,@date,120),'-',''),':',''),' ','')
    -- 20110426131209
    select @date = GETDATE()
    select REPLACE(REPLACE(REPLACE(convert(varchar,@date,120),'-',''),':',''),' ','')
    -- 20110427113730

    Open in new window


    Author Comment

    When I use the replace my date isn't number any more, but it's looking like the following:

    Apr 26 2011 553AM

    I need for it to look like 201104260553.

    Author Comment

    Okay got it working now.  Thanks

    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

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    755 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