DDMMYYYY string to Date

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.

Answer 1

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)

Answer 2

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.

Answer 3

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