CODEWITHSUNDEEP
mysqlsql
confused101
confused101

Reputation: 99

How to add 0 to string column in SQL

I have a string column that is missing leading 0's. An example of this is

9/14/2006 4:5:19 PM

It should have a 0 to make it 09/14/2006 4:05:19 PM

Upvotes: 0

Views: 70

Answers (1)

Ergest Basha
Ergest Basha

Reputation: 8973

I don't want to do anything with what was already created just create my own new column with leading 0's for those items

You can create your column with proper date datatype, it will be much easier to query and format the date as you wish

Use str_to_date to convert string to date

select str_to_date(my_dt,'%m/%d/%Y %I:%i:%s %p') 
from test;

Then update the newly created column :

update test 
set correct_dt = str_to_date(my_dt,'%m/%d/%Y %I:%i:%s %p');

You can find all the steps above in the fiddle here: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6b53020805776571c7c9f644eb94123c

Upvotes: 1

Related Questions