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

MYSQL using CASE statement to create a column based on dates

There is a column in a table with dates called ‘date_activated’. I would like to add a column and use the information from the ‘date_activated’ to produce a datetime format that sets to the first day of the year.

For example:

id date_activated datetime
1. 2016-05-12 2016-01-01 0:00:00
2. 2019-05-16 2019-01-01 0:00:00
Tags:

2

Answers


  1. 0

    Here is the query:

    select
    *,
    cast(concat(year(date_activated), '-1-1') as datetime) 'datetime'
from
    t;

    and matching output:

    id date_activated datetime
    1 2016-05-12 2016-01-01 00:00:00
    2 2019-05-16 2019-01-01 00:00:00

    If you want to alter the table you do that, then update the column with the cast expression.

    Login or Signup to reply.
  2. 0

    First, ALTER your table to add your additional column:

    ALTER TABLE sample_table
ADD COLUMN `datetime` timestamp AFTER date_activated;

    Note: your newly created column named datetime is a Keyword in MySQL. It is not advised to name things after Keywords or Reserved Words.

    Next, UPDATE your column using an INNER JOIN to self-join your tables in order to get the year from the date_activated column:

    UPDATE sample_table a
INNER JOIN sample_table b ON a.id = b.id
SET b.datetime = CONCAT(YEAR(b.date_activated), '-01-01 00:00:00')

    Using YEAR(), you can extract the year from date_activated then CONCAT it with '-01-01 00:00:00' to fit your new timestamp columns format.

    If you’re just trying to add it to your SELECT statement without altering or updating your table:

    SELECT id, 
       date_activated, 
       CONCAT(YEAR(date_activated), '-01-01 00:00:00') AS datetime
FROM sample_table

    Input:

    id date_activated
    1 2016-05-12
    2 2019-05-16

    Output:

    id date_activated datetime
    1 2016-05-12 2016-01-01 00:00:00
    2 2019-05-16 2019-01-01 00:00:00

    db<>fiddle here.

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

Back To Top
Search