CODEWITHSUNDEEP
sqlsql-server
Martin
Martin

Reputation: 100

Convert YYMDD into normal date

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?

Upvotes: 1

Views: 298

Answers (4)

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

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

Upvotes: 0

Kaf
Kaf

Reputation: 33819

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

Upvotes: 2

DARK_A
DARK_A

Reputation: 575

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);

Upvotes: 0

Taryn
Taryn

Reputation: 247710

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 |

Upvotes: 2

Related Questions