Convert YYMDD into normal date

Question

I have string '12B17T' which represents exactly YYMDDA (A is an irrelevant attribute). For months, 1-9 is used for January to September; A-C is used for October, November, and December

The size of this string is fixed (e.g. if the date is January 1, 2012, it will look like '12101T')

How do I get something like 17.11.2012 from this using SQL?

12 is year (there is assumption that every year will be 20YY so for 12 -> 2012, for 13 -> 2013), 1 for january and 01 for 1st day, T is irrevelant — Martin, Dec 17 '12 at 10:19
ouch, slap the guy that initially formed the date the way it is. It is very ugly to decode/convert those field values into a normal date in a sql query. Better use/write a stored procedure taking column and giving back date. — Najzero, Dec 17 '12 at 10:25

score 2 · Answer 1 · answered Dec 17 '12 at 10:16

This might be an ugly way to do this, but I would create a function that you can call in your queries to transform the data:

create function TransformDate(@myString varchar(6))
returns datetime
as
begin

  return cast('20'+left(@myString, 2) +'-'
    + case substring(@mystring, 3, 1)
        when '1' then '01'
        when '2' then '02'
        when '3' then '03'
        when '4' then '04'
        when '5' then '05'
        when '6' then '06'
        when '7' then '07'
        when '8' then '09'
        when '9' then '09'
        when 'A' then '10'
        when 'B' then '11'
        when 'C' then '12' end +'-'
    + substring(@mystring, 4, 2) as datetime)

end

Then you could use it this way:

declare @value varchar(6) = '12B17T'

select dbo.transformdate(@value) as dt

See SQL Fiddle with Demo

Which would result in:

|         DT |
--------------
| 2012-11-17 |

Kaf · Accepted Answer · 2012-12-17T10:40:00.877

2

Try this;

DECLARE @x varchar(50)= '12B17T' 
SELECT  SUBSTRING(@x,4,2) +'-'+ 
   RIGHT( '0' + CASE SUBSTRING(@x,3,1) WHEN 'A' THEN '10'
                          WHEN 'B' THEN '11'
                          WHEN 'C' THEN '12' 
   ELSE SUBSTRING(@x,3,1) END, 2) +'-'+ 
   '20' + SUBSTRING(@x,1,2)

SQL Demo Fiddle here

--Results 17-11-2012

edited Dec 17 '12 at 10:40

answered Dec 17 '12 at 10:17

Kaf

33,101
7
58
78

@MahmoudGamal, thanks for that. I have just added `RIGHT('0' + ..,2)` to cover up 2 digits for the month. – Kaf Dec 17 '12 at 10:21
Your day and year values are backwards. The initial format is `YYMDD` not `DDMYY` – Taryn Dec 17 '12 at 10:34
I have noticed that but its just cosmetic thing. – Martin Dec 17 '12 at 10:37

score 0 · Answer 3 · answered Dec 17 '12 at 10:37

If your server recognize month in this format, you can try:

SELECT TO_CHAR(t_date,'YYYY.MM.DD') FROM (SELECT TO_DATE(some_date,'YYMMDD') AS t_date FROM some_date_table);

If not, then you have to use CASE or DECODE:

SELECT dt_year||'.'||dt_month||'.'||dt_day AS date_ FROM
  (SELECT 
     CASE 
      WHEN SUBSTR(some_date,1,2) BETWEEN 0 AND 12 THEN 20||SUBSTR(some_date,1,2)
      WHEN SUBSTR(some_date,1,2) BETWEEN 13 AND 99 THEN 19||SUBSTR(some_date,1,2)
     END AS dt_year,
     CASE 
      WHEN SUBSTR(some_date,3,1) IN('1','2','3','4','5','6','7','8','9') THEN 0||SUBSTR(some_date,3,1)
      WHEN SUBSTR(some_date,3,1) = 'A' THEN '10'
      WHEN SUBSTR(some_date,3,1) = 'B' THEN '11' 
      WHEN SUBSTR(some_date,3,1) = 'C' THEN '12'
     END AS dt_month,
     SUBSTR(some_date,4,2) AS dt_day FROM some_date_table);

Mudassir Hasan · Answer 4 · 2012-12-17T10:52:31.017

col is column of a table tb3 containing string

create table tb3(col varchar(max))
insert into tb3 values('120617T')

declare @tmp varchar(max) 
select @tmp=case 
when substring(col,3,1)='A' then replace(col,'A','10')
when substring(col,3,1)='B' then replace(col,'B','11')
when substring(col,3,1)='C' then replace(col,'C','12') 
when substring(col,3,1)!='0' then replace(col,substring(col,3,1),'0'+substring(col,3,1))
else col end 
from tb3

SELECT CONVERT(VARCHAR(10), convert(datetime,substring(@tmp,1,len(@tmp)-1),109), 104)

OUTPUT

17.06.2012

Convert YYMDD into normal date

4 Answers4