locked
How to convert '20140620' to 'DD-MM-YYYY' formate RRS feed

  • Question

  • Hi Experts,

    I have data for date fields like "20140620"(YYYYMMDD) while inserting into the table it should be in the format "DD-MM-YYYY"

    Can any one please help me to achive this.

    Thursday, June 12, 2014 8:31 AM

Answers

  • Hello Asma,

    You can use the CAST and CONVERT (Transact-SQL) function with Format Parameter 105 = Italian = dd-mm-yyyy

    SELECT CONVERT(varchar(10), CONVERT(datetime, '20140620', 112), 105) AS DDMMYYY


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, June 12, 2014 8:38 AM
  • Hi Experts,

    I have data for date fields like "20140620"(YYYYMMDD) while inserting into the table it should be in the format "DD-MM-YYYY"

    Can any one please help me to achive this.

    You do not really want to worry about how or what format the date is storing, you can always do the format while you fetch or at application layer:

    create table Test(Co1 date)
    Insert into test Select '20140620'
    
    Select * From test
    
    Select CONVERT(varchar(10),co1,105) From test
    
    Drop table test
    

    • Marked as answer by Elvis Long Monday, June 23, 2014 2:58 AM
    Thursday, June 12, 2014 8:42 AM
  • check this:

    select format(cast('20140620' as date), 'dd-MM-yyyy', 'en-US'), cast('20140620' as date)

    this applies for SQL Server >= 2012


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page


    Thursday, June 12, 2014 11:00 AM

All replies

  • Hello Asma,

    You can use the CAST and CONVERT (Transact-SQL) function with Format Parameter 105 = Italian = dd-mm-yyyy

    SELECT CONVERT(varchar(10), CONVERT(datetime, '20140620', 112), 105) AS DDMMYYY


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, June 12, 2014 8:38 AM
  • Hi Experts,

    I have data for date fields like "20140620"(YYYYMMDD) while inserting into the table it should be in the format "DD-MM-YYYY"

    Can any one please help me to achive this.

    You do not really want to worry about how or what format the date is storing, you can always do the format while you fetch or at application layer:

    create table Test(Co1 date)
    Insert into test Select '20140620'
    
    Select * From test
    
    Select CONVERT(varchar(10),co1,105) From test
    
    Drop table test
    

    • Marked as answer by Elvis Long Monday, June 23, 2014 2:58 AM
    Thursday, June 12, 2014 8:42 AM
  • What data type do you have for the column in your table?

    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, June 12, 2014 10:01 AM
  • check this:

    select format(cast('20140620' as date), 'dd-MM-yyyy', 'en-US'), cast('20140620' as date)

    this applies for SQL Server >= 2012


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page


    Thursday, June 12, 2014 11:00 AM