Create table using select statement Truncated incorrect datetime value:

Question

I have looked up different answers on similar question before posting it here.

So i have a table with user id and date column

enter image description here

I would like to store the results of select query to a temporary table. On using the below statement:

Create Temporary table dummy_table ( select User_Id , DATE_FORMAT(Date,'%M %Y') from result_table.user_signup_date );

throws error Error Code: 1292. Truncated incorrect datetime value: '2019-06-16 00:00:00 UTC'

I noticed the error occurs only when i format date column. I tried converting date to string. I also tried with str_to_date(DATE_FORMAT(Date,'%M %Y') ,'%M %Y %H:%i:%s') and few more syntaxes but with no result.

I am new to Sql. Any help would be appreciated. Thanks in advance

Answer 1

From the looks of the error message, the UTC should not be part of the date. My guess is that date is not using the DATETIME or TIMESTAMP data type. It must be a string.

Try running it like this :

Create table dummy_table AS
select Cust_ID User_Id , DATE_FORMAT(LEFT(`Date`,19),'%M %Y') DT
from result_table.user_signup_date;

The expression LEFT(Date,19) will drop off the everything after the timestamp.

• swat: Wow. This works. I wish i could have posted it earlier. Thank you!
• swat: Just Curious. If 'Date' column is read as string by SQL, how does it work when i use DATE_FORMAT(Date,'%M %Y'). Am i missing any basics here?