Question 88571 in Mysql Question posted in
The official MySQL documentation can be found here.

Evaluate a date format from a String value – Mysql

I have a requirement where a filed value is "CAH132242F0A" from this i have to come up with a date.
so here "13" is the year and so yyyy = "2013" and "224" th day. so i have to extract this and make it a date format "yyyy-mm-dd".

Can someone help me how to do it?

Tags:

5

Answers


  1. 0

    You could use some string manipulation olympics here:

    SELECT col,
       TIMESTAMPADD(DAY,
                    CAST(SUBSTRING(col, 6, 3) AS UNSIGNED) - 1,
                    CAST(CONCAT(2000 + CAST(SUBSTRING(col, 4, 2) AS UNSIGNED), '-01-01') AS date)
       ) AS date
FROM yourTable;  -- 2013-08-13

    Demo

    Here we use TIMESTAMPADD and brute force substring operations to add the correct number of days to January 1 in the year specified.

    Login or Signup to reply.
  2. 0

    According to the databricks docs on date/time formats this should work:

    to_date(substring(code, 4, 5), 'yD')

    We extract the five digits for yyddd and apply the format ‘yD’ where y stands for a two- or four-digit year and D for a three-digit day number. The docs say that the base year is always 2000, which is just what you want.

    Login or Signup to reply.
  3. 0

    Use date_add twice: once for the year, and again for the day:

    date_add(date_add('1999-12-31', interval substr(f, 4, 2) year), interval substr(f, 6, 3) day)

    See live demo.

    Login or Signup to reply.
  4. 0 
    SELECT 
  TO_CHAR(
    DATE_TRUNC('year', '2000-01-01'::date) + 
    CAST(SUBSTRING('CAH132242F0A' FROM 4 FOR 2) AS INTEGER) * INTERVAL '1 year' + 
    (CAST(SUBSTRING('CAH132242F0A' FROM 6 FOR 3) AS INTEGER) - 1) * INTERVAL '1 day', 
    'yyyy-mm-dd'
  ) AS date_str;

    The date is 2013-08-12 (and not 13 !)

    Login or Signup to reply.
  5. 0 
    create table mytable(datecolumn varchar(20));
insert into  mytable values('CAH132242F0A');

    Query:

    select makedate( concat('20' ,substring(datecolumn,4,2)),substring(datecolumn,6,Length(datecolumn)-(position('F' in datecolumn))+1)) date
from mytable
    date
    2013-08-12

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search