CODEWITHSUNDEEP
mysqldatedatetime
Mohideen bin Mohammed
Mohideen bin Mohammed

Reputation: 20147

How to get Time from Mysql Date Format DD/MM/YYYY?

I want to get time from mysql dd/mm/YYYY H:M:S format.

I have tried,

SUBSTRING_INDEX(field, 'delimiter', index)

but am looking for a better solution.

have tried, DATE_FORMAT(field, "%H:%i:%s") but it returns NULL because my date format was not native (YYYY-mm-dd)

it was 02/05/2019 19:38:27

How to get time from this above format in a better way?

NOTE: I am storing date like above.. this fetching form SQL Server

Upvotes: 0

Views: 1296

Answers (2)

Mohideen bin Mohammed
Mohideen bin Mohammed

Reputation: 20147

This is how i Resolved,

TIME(STR_TO_DATE(d.in_punch, "%d/%m/%Y %H:%i:%s"))

also as per @Salman A

CAST(STR_TO_DATE('02/05/2019 19:38:27', "%d/%m/%Y %H:%i:%s") AS TIME)

this also worked.

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272146

I guess you can first use STR_TO_DATE followed by CAST(... AS time). Casting instead of formatting allows you to use the result in date/time calculations.

SELECT CAST(STR_TO_DATE('02/05/2019 19:38:27', "%d/%m/%Y %H:%i:%s") AS TIME)

Ideally you should teach SQL Server to export dates in yyyy-MM-dd hh:mm:ss format.

Upvotes: 2

Related Questions