none
DDMMYYYY string to Date RRS feed

  • Question

  • Hi Experts,

    I've to copy string data in the form of DDMMYYYY coming from excel sheet to date format like DD/MM/YYYY in sql server table.

    How can I achieve this. please help.

    Thank you.

    Wednesday, March 20, 2013 9:44 AM

Answers

  • Hi,

    There are few ways of doing it..

    Example:

    SELECT CONVERT(VARCHAR,CONVERT(DATE,RIGHT('12032013',4)+SUBSTRING('12032013',3,2)+LEFT('12032013',2)) , 103)
    
    SELECT LEFT('12032013',2)+ '/' + SUBSTRING('12032013',3,2)+ '/' + RIGHT('12032013',4)

    • Marked as answer by SQL2012BI Wednesday, March 20, 2013 10:00 AM
    Wednesday, March 20, 2013 9:51 AM
  • Try the below:

    Declare @s varchar(100) = '22032013'
    Select Convert(Date,SUBSTRING(@s,3,2)+'/'+ SUBSTRING(@s,0,3) + '/'+SUBSTRING(@s,5,10))

    DD/MM/YYYY you can do at the time of representation.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by SQL2012BI Wednesday, March 20, 2013 10:00 AM
    Wednesday, March 20, 2013 9:52 AM
  • CAST (RIGHT(@in_str_date, 4) + '-' + SUBSTRING(@in_str_date, 3, 2)+ '-' + LEFT(@in_str_date, 2) AS DATE) 

    Please note that the only date format allowed in ANSI/ISO Standard SQL is “yyyy-mm-dd”; anything else is a local dialect and good SQL programmers avoid it. T-SQL's DATE data type supports this, and it is a completely portable format. 



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by SQL2012BI Thursday, March 21, 2013 8:46 AM
    Wednesday, March 20, 2013 2:29 PM

All replies

  • Hi,

    There are few ways of doing it..

    Example:

    SELECT CONVERT(VARCHAR,CONVERT(DATE,RIGHT('12032013',4)+SUBSTRING('12032013',3,2)+LEFT('12032013',2)) , 103)
    
    SELECT LEFT('12032013',2)+ '/' + SUBSTRING('12032013',3,2)+ '/' + RIGHT('12032013',4)

    • Marked as answer by SQL2012BI Wednesday, March 20, 2013 10:00 AM
    Wednesday, March 20, 2013 9:51 AM
  • Try the below:

    Declare @s varchar(100) = '22032013'
    Select Convert(Date,SUBSTRING(@s,3,2)+'/'+ SUBSTRING(@s,0,3) + '/'+SUBSTRING(@s,5,10))

    DD/MM/YYYY you can do at the time of representation.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by SQL2012BI Wednesday, March 20, 2013 10:00 AM
    Wednesday, March 20, 2013 9:52 AM
  • CAST (RIGHT(@in_str_date, 4) + '-' + SUBSTRING(@in_str_date, 3, 2)+ '-' + LEFT(@in_str_date, 2) AS DATE) 

    Please note that the only date format allowed in ANSI/ISO Standard SQL is “yyyy-mm-dd”; anything else is a local dialect and good SQL programmers avoid it. T-SQL's DATE data type supports this, and it is a completely portable format. 



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by SQL2012BI Thursday, March 21, 2013 8:46 AM
    Wednesday, March 20, 2013 2:29 PM