Post reply

varchar to datetime

  • August 14, 2015 at 1:26 pm

    #299773

    I have column moddat which is of varchar(10,null)

    Here is my data:

    20020415

    20020508

    19991104

    19990701

    20040514

    20021112

    20020124

    19990628

    20020514

    20010822

    I want those data in this format YYYY-MM-DD

    How to convert varchar to datetime?

  • August 14, 2015 at 1:50 pm

    #1820213

    Simply use CONVERT( datetime, TheData) or CAST( TheData AS datetime).

    Reference: https://msdn.microsoft.com/en-us/library/ms187928.aspx

    FYI, datetime is not stored in any format. The formats are for display and the same value can be shown in different formats as needed.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 14, 2015 at 2:15 pm

    #1820218

    If you really do want that specific format, you have two options: 1) Manipulate the string without converting, or 2) convert to datetime and then back to a string.

    SELECT dt.dt_string

    , STUFF(STUFF(dt.dt_string, 7, 0, '-'), 5, 0, '-') AS manipulate_string

    , CONVERT(CHAR(10), CAST(dt.dt_string AS DATE), 121) double_convert

    FROM dt

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply