CODEWITHSUNDEEP
phpmysqlcakephp-1.3
Panda
Panda

Reputation: 23

Convert date in mysql

I have 'dob' field that is varchar type and the data is '12/07/1988' and '10/30/1988' inside my database.

I want to filter the record base on 'dob' date range.My input date range is '01/01/1988' and '31/12/1988' but all others record also come out.

This is my code,anyone can help?

    SELECT
        dob
    FROM
        `tblcustomers`
    WHERE
        DATE_FORMAT(STR_TO_DATE(dob, "%m/%d/%Y"), "%d/%m/%Y") >= '01/01/1988' 
    AND 
        DATE_FORMAT(STR_TO_DATE(dob, "%m/%d/%Y"), "%d/%m/%Y") <= '31/12/1988';

Thank you.

Upvotes: 0

Views: 533

Answers (4)

user7527172
user7527172

Reputation:

It is better to use DATE/DATETIME than VARCHAR, otherwise it will waste time converting string to date all the time when you have queries.

You can simply change your dob field to DATE

and then filter the record like this

SELECT * FROM tblcustomers WHERE dob BETWEEN '1988-01-01' AND '1988-12-31'

Notice: When using BETWEEN...AND, '1988-01-01' & '1988-12-31' will not included

so you may decrease the start-day 1 day and increase the end-day 1 day it may look like this

SELECT * FROM tblcustomers WHERE dob BETWEEN '1987-12-31' AND '1989-01-01'

or easier way similar to what you have done, this one is also possible

SELECT * FROM tblcustomers WHERE dob >= '1988-01-01' AND dob <= '1988-12-31'

Upvotes: 1

PHP Geek
PHP Geek

Reputation: 4033

You can try this. I hope it will help you.

$from_date = date("Y-m-d",strtotime('01/01/1988'));
$to_date = date("Y-m-d",strtotime('31/12/1988'));

$query = "SELECT * FROM tblcustomers WHERE from_date >= '".$from_date."' AND to_date <= '".$to_date."'";

Upvotes: 0

jaty muhammad
jaty muhammad

Reputation: 56

try it...

SELECT dob FROM tblcustomers where DATE_FORMAT(STR_TO_DATE(dob, '%m/%d/%Y'), '%Y-%m-%d') between '1988-01-01' and '1988-12-31'

Upvotes: 1

Shubham Dixit
Shubham Dixit

Reputation: 1

Date range will work on sql date type not on varchar ,so first change it to date type and insert date in format yyyy-mm-dd then you can use range it will work

Upvotes: 0

Related Questions