MYSQL using CASE statement to create a column based on dates-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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.