How to extract a date from a string in SQL?

Question

I have a very simple tag in a table, structured as tag-DDMMYY. I want to convert that into a date, MM-DD-20YY. Is there an easy way to do this in SQL?

I'm not an expert developer and I feel like this should be quite easy using PatIndex (there's always only one dash), but I've got nowhere so far and I'm hoping someone expert could help.

welcome, please add code of whatever you have so far and a sample so that we can start from there — gerosalesc, May 31 '16 at 18:38
Welcome to stack! Also, please tag which flavor of SQL you are working with (MSSQL, Oracle, MySQL, etc). Cheers! — S3S, May 31 '16 at 18:41

score 0 · Answer 1 · answered May 31 '16 at 18:51

0

This is mysql

You can use some date and string function

select  str_to_date(substr(my_column, LOCATE('-', my_column), 50),%d%m%y)
from you_table ;

answered May 31 '16 at 18:51

ScaisEdge

131,976
10
91
107

score 0 · Answer 2 · answered May 31 '16 at 18:54

In MSSQL, you can do something like:

SELECT SUBSTRING(T1.OriginalDate, PATINDEX('%-%', T1.OriginalDate)+3, 2) 
     + '-' 
     + SUBSTRING(T1.OriginalDate, PATINDEX('%-%', T1.OriginalDate)+1, 2) 
     + '-' 
     + '20' + SUBSTRING(T1.OriginalDate, PATINDEX('%-%', T1.OriginalDate)+5, 2) 
     AS NewFormatDate
FROM (SELECT 'tag-310516' as OriginalDate) T1

score 0 · Answer 3 · edited May 23 '17 at 12:07

0

Try this..(SQL Server)

  select cast (stuff(stuff(substring(column,patindex('%-%',column)+1,len(column)),5,0,'.20'),3,0,'.') as date) 
   from mytable

Borrowed from here

For mysql you can try something like this

select str_to_date(concat(substring(yourcolumn,instr(yourcolumn,'-')+1,4),'20',substring(yourcolumn,length(yourcolumn)-1,2)),'%d%m%Y')
from yourtable

edited May 23 '17 at 12:07

Community

1
1

answered May 31 '16 at 19:05

cableload

4,215
5
36
62

How to extract a date from a string in SQL?

3 Answers3