CODEWITHSUNDEEP
mysqlsqlsql-update
user11743296
user11743296

Reputation:

How to move substrings to new columns?

So, I've got an MYSQL Database with just one table called log. In that table there's a column called Date_Time in which all data looks like this: 2019-04-01-02.24.34.657661. I want to make two seperate columns called Time and Date in which I want to put the date and time from the Date_Time column, obviously.

The code, which you can see below is what I've already tried. As you can see, I know how to select a substring, but I don't know how to put it in new, seperate columns. I hope you can help me.

SELECT SUBSTRING(Date_Time, 1, 10) AS Date
from log;

By the way, the database has 8.000.000 lines, just so you know :D

Upvotes: 1

Views: 99

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You need an UPDATE statement :

update log
   set Date = SUBSTRING(Date_Time, 1, 10), 
       Time = SUBSTRING(Date_Time, 11)

Seems that Date_Time is a string type column. Since you are directly using SUBSTRING() function without type conversion.

Upvotes: 2

Niranjan Rajawat
Niranjan Rajawat

Reputation: 563

Try using cast:

Select cast(Date_Time as date) as Date, cast(Date_Time as time) as Time
from log;

Upvotes: 0

Related Questions