locked
Date Format - Varchar MMDDYYYY to DD/MM/YYYY RRS feed

  • Question

  • Hi Folks:

    I have date stored in SQL column as mmddyyyy with datatype as varchar. For reporting output I would like to display the date in DD/MM/YYYY format.

    DECLARE @tblData TABLE
    (EffDt VARCHAR(15))
    
    INSERT INTO @tblData values ('06172020')
    INSERT INTO @tblData values ('05152020')
    INSERT INTO @tblData values ('04142020')
    INSERT INTO @tblData values ('03162020')
    INSERT INTO @tblData values ('02152020')
    
    SELECT EffDt FROM @tblData
    

    Thanks!

    Friday, June 26, 2020 1:55 PM

Answers

  • Try

    SELECT EffDt, substring(EffDt,3,2)+'/'+ left(EffDt,2)+'/'+ right(EffDt,4) as [OutputDt] FROM @tblData


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by sqldba20 Friday, June 26, 2020 2:03 PM
    Friday, June 26, 2020 1:58 PM

All replies

  • Try

    SELECT EffDt, substring(EffDt,3,2)+'/'+ left(EffDt,2)+'/'+ right(EffDt,4) as [OutputDt] FROM @tblData


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by sqldba20 Friday, June 26, 2020 2:03 PM
    Friday, June 26, 2020 1:58 PM
  • DECLARE @tblData TABLE
    (EffDt VARCHAR(15))
    
    INSERT INTO @tblData values ('06172020')
    INSERT INTO @tblData values ('05152020')
    INSERT INTO @tblData values ('04142020')
    INSERT INTO @tblData values ('03162020')
    INSERT INTO @tblData values ('02152020')
    
    SELECT EffDt
    ,Substring(EffDt,3,2)+'/'+stuff(EffDt,3,2,'/') 
     ,Format(Cast(RIGHT(EffDt,4) +Left(EffDt,4) as date),'dd/MM/yyyy') [DD/MM/yyyy]
     ,Replace(Convert(varchar(10),Cast(RIGHT(EffDt,4) +Left(EffDt,4) as date),104),'.','/')
      ,Replace(Convert(varchar(10),Cast(RIGHT(EffDt,4) +Left(EffDt,4) as date),105),'-','/')
      ,CONVERT(varchar(10),Cast(RIGHT(EffDt,4) +Left(EffDt,4) as date), 103)  
    FROM @tblData

    • Proposed as answer by pituachMVP Friday, June 26, 2020 3:32 PM
    Friday, June 26, 2020 3:03 PM
  • Hi Folks:

    I have date stored in SQL column as mmddyyyy with datatype as varchar. For reporting output I would like to display the date in DD/MM/YYYY format.

    DECLARE @tblData TABLE
    (EffDt VARCHAR(15))
    
    INSERT INTO @tblData values ('06172020')
    INSERT INTO @tblData values ('05152020')
    INSERT INTO @tblData values ('04142020')
    INSERT INTO @tblData values ('03162020')
    INSERT INTO @tblData values ('02152020')
    
    SELECT EffDt FROM @tblData

    Thanks!

    Good day sqldba20,

    I have date stored in SQL column as mmddyyyy with datatype as varchar.

    Just to clarify an extremely important point, You DO NOT have date stored with database varchar, since varchar is a string and NOT date!

    You have a poor use of database which uses string to represent dates. You should probably stop what you do and re-design your database ASAP! You probably pays on performance and resources in each second that pass.

    >> For reporting output I would like to display the date in DD/MM/YYYY format.

    If it was dates then you could use the function FORMAT or CONVERT with style 103

    Since this is a string, you can use one of the solution you got, but again... I highly recommend to re-design your database

    Note: solution which based on converting the string to date first, will provide you a check that the string fits to a date format and will help you keep your data integrity


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, June 26, 2020 3:38 PM
  • Here's an idea 

    DECLARE @tblData TABLE
    (EffDt VARCHAR(15))
    
    INSERT INTO @tblData values ('06172020')
    INSERT INTO @tblData values ('05152020')
    INSERT INTO @tblData values ('04142020')
    INSERT INTO @tblData values ('03162020')
    INSERT INTO @tblData values ('02152020')
    
    SELECT format( parse(stuff(stuff(effdt,5,0,'/'),3,0,'/') as date using 'en-US'), 'dd/MM/yyyy') FROM @tblData

    Friday, June 26, 2020 5:24 PM