MySQL convert date string to Unix timestamp

Question

How do I convert the following format to unix timestamp?

Apr 15 2012 12:00AM

The format I get from DB seems to have AM at the end. I've tried using the following but it did not work:

CONVERT(DATETIME, Sales.SalesDate, 103) AS DTSALESDATE,  
CONVERT(TIMESTAMP, Sales.SalesDate, 103) AS TSSALESDATE

where Sales.SalesDate value is Apr 15 2012 12:00AM

Solution 1

Here's an example of how to convert DATETIME to UNIX timestamp:
SELECT UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p'))

Here's an example of how to change date format:
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE('Apr 15 2012 12:00AM', '%M %d %Y %h:%i%p')),'%m-%d-%Y %h:%i:%p')

Documentation: UNIX_TIMESTAMP, FROM_UNIXTIME

Solution 2

You will certainly have to use both STR_TO_DATE to convert your date to a MySQL standard date format, and UNIX_TIMESTAMP to get the timestamp from it.

Given the format of your date, something like

UNIX_TIMESTAMP(STR_TO_DATE(Sales.SalesDate, '%M %e %Y %h:%i%p'))

Will gives you a valid timestamp. Look the STR_TO_DATE documentation to have more information on the format string.

Solution 3

For current date just use UNIX_TIMESTAMP() in your MySQL query.

Comments

[strnk]

Is the date stored as text in the DB ?

[Álvaro González]

Is it really a MySQL question? Your syntax doesn't suggest so.

[Fedir RYKHTIK]

Seems what the question is about MSSQL.

[Salman A]

This answer is for MSSQL (although this question has some confusion).

[Evan Donovan]

Doesn't answer question from original post.

[stackMonk]

it is for people who needed to convert only the current date in their query, answers a part of the question, no need for your gatekeeping Evan, people have upvoted the same to show they need this

[Evan Donovan]

The original question asked to convert a specific format; this is not answering that question. I will cancel the downvote if you edit to clarify that.

[stackMonk]

it has been clarified in the very first line that this is for current date.

[Evan Donovan]

I edited to clarify the relationship of this response to the original question and give an example.

[MR_AMDEV]

Thanks .This works.This can also be used in other statements such as update,delete,insert etc

[Damodar Bashyal]

After so many trials your solution worked for me: select * from (SELECT order_increment_id, FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE(order_date, '%d %M %Y %h:%i:%s %p')),'%Y-%m-%d') as order_date, email_sent FROM `packingslip_header` where email_sent=0) t where order_date >= '2019-11-13' ORDER BY order_increment_id ASC This is the date I had 31 Oct 2017 4:16:49 pm so I needed to use %d %M %Y %h:%i:%s %p for STR_TO_DATE