CODEWITHSUNDEEP
mysqloracle-databasedate
Prajith A S
Prajith A S

Reputation: 477

Trunc date field in mysql like Oracle

I am to not able to use the 'trunc(in oracle)' function in 'mysql' database. I have a table called dlb_cc_purchase and date field called due_date in my 'mysql' database. The data displaying in the date field like 20-11-2014 00:00:00 (20-nov-2014). in oracle we are using query

select * from dlbcc_purchase where trunc(due_date) = '20-nov-2014'

Oracle DB will fetch the row with due date 20-11-2014 00:00:00. How can I use this function in 'mysql'?

I know this is a basic question, but i was trying to do this for long time with truncate, str_to_date... but not able to fetch value. Please help.

Upvotes: 25

Views: 75927

Answers (2)

Dens
Dens

Reputation: 469

You can use DATE_FORMAT().

example:

select * from dlbcc_purchase where DATE_FORMAT(due_date,'%d-%b-%Y') = '20-nov-2014'

Upvotes: 5

Rimas
Rimas

Reputation: 6024

Use DATE(expr) function. Query example:

SELECT *
  FROM dlbcc_purchase
  WHERE DATE(due_date) = '2014-11-20'

Upvotes: 52

Related Questions