MYSQL using CASE statement to create a column based on dates

jason lee 0
Viewed 27

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
mysql
2 Answers

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.

Allan Wind0

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.

GRIV0
Related