CODEWITHSUNDEEP
mysqlsql
unicorns ruleee
unicorns ruleee

Reputation: 23

STR_TO_DATE returns null

I'm trying to convert a string to date with a format of yyyy/mm to yyyy

STR_TO_DATE(SUBSTRING(time,1,4), '%Y')

This returns null for every value as well as when I try to convert without getting rid of the mm:

STR_TO_DATE(REPLACE(time,'/',''), '%Y%m')

This method has worked for me before, I'm at a loss for what I'm missing. Thanks in advance!

Edit for clarification: Eventually I am going to insert the year into a column with data type year, so I need to convert a varchar to a date type so I can extract the year in order to insert the data into a new table

I am in the process of making sure it will work before populating the new column with a command like this:

INSERT INTO table (year)    
SELECT STR_TO_DATE(SUBSTRING(time,1,4), '%Y')    
FROM `origtable`

Upvotes: 0

Views: 121

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I'm trying to convert a string to date with a format of yyyy/mm to yyyy

Just use left():

select left(time, 4) as yyyy

There is no need to convert to a date or datetime. You want a simple string operation.

EDIT:

year is a really weird type. I would just use an integer. But if you are using it, you can insert a full date into the field:

select date(concat(time, '/01'))

The resulting date can be inserting to a year column.

Upvotes: 2

Related Questions