Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.0k views
in Technique[技术] by (71.8m points)

datetime - how to convert weird varchar "time" to real time in mysql?

I have a time value being stored in a database as a varchar(4) and I need to convert it to real time.

for example, if the time is "23:59" I want 11:59PM returned.

The problem is that there is no ":" between the hours and minutes. So when I run this query

SELECT TIME_FORMAT('2359', '%h:%i');    -- 12:23, wtf??

However if I ran this:

SELECT TIME_FORMAT('23:59', '%h:%i');   -- returns 11:59 as expected.

So, to sum up: 1. the time is stored as a varchar(4) in the database. Example:

1200, 1201, 0153, 1364, 1923
  1. I want time returned as 12 hr time with a colon in it.

my brain hurts and this is prb much easier than I realize...

like this, but for mysql Convert varchar into datetime in SQL Server mysql 12 hr to 24 hr time conversion

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

never mind, this works fine:

TIME_FORMAT(CONCAT(SUBSTRING(THE_TIME, 1,2), ':', SUBSTRING(THE_TIME, 3,4)), '%h%i')

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...