CODEWITHSUNDEEP
mysqldatetime
user3392688
user3392688

Reputation: 47

MySql YYYY-MM-DDTHH:MM:SS to time

I have a table with a varchar column DateFrom that has this format:

2014-02-22T08:08:00

I want an sql that prints 08:08 and one that prints 22-02-2014 but i can't seem to get the time function to work.

What i'm trying to do is get all entries in DateFrom and print them as just time (HH:MM) and the same with date.

Upvotes: 1

Views: 1079

Answers (2)

hakre
hakre

Reputation: 197832

If you're not looking for date/time but for string functions, they are available here:

One string function that I think is useful for your substring operation is SUBSTRING. You can tell per the varchar column that you want a sub-string starting from a position for a certain length with it:

SUBSTRING(DateFrom FROM 1 FOR 8) AS DateName -- "2014-02-22"
SUBSTRING(DateFrom FROM 10 FOR 5) AS TimeName -- "08:08"

Use other string functions to concatenate parts in the order you need it.

Alternatively you can cast your varchar string in that format to a datetime type and then format as needed:

CAST(DateFrom AS datetime)

See the Mysql manual for more information about casting types and the date-time functions that are available:

SQL Example:

SET @DateFrom = '2014-02-22T08:08:00';
SELECT DATE(CAST(@DateFrom AS datetime)); -- '2014-02-22'
SELECT TIME(CAST(@DateFrom AS datetime)); -- '08:08:00'

Upvotes: 1

Benz
Benz

Reputation: 2335

Altough I think string functions are a better option in this case (like @hakre answered) and less cpu expensive, you can also achieve this goal using the STR_TO_DATE, DATE and TIME function.

SELECT 
DATE(STR_TO_DATE('2014-02-22T08:08:00', '%Y-%m-%dT%H:%i:%s')),
    TIME(STR_TO_DATE('2014-02-22T08:08:00', '%Y-%m-%dT%H:%i:%s'))

Upvotes: 3

Related Questions