0

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

0

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.

2
  • Wow. This works. I wish i could have posted it earlier. Thank you! – swat Jun 6 '20 at 21:02
  • 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? – swat Jun 6 '20 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.