CODEWITHSUNDEEP
mysqlsql
Praful
Praful

Reputation: 191

Date field datatype text in database

I want to fetch data between two range but in my database Date field as Text. How Now fetch data between two range

         $startdate = 01-01-2020
         $enddate = 31-12-2020

and my database field name DATE as text datatype(format 01-12-2019) Below is the query I am using

            SELECT m.id, m.centers, c.BUDGET_ANNUAL_AMOUNT
            FROM Cost_centers m INNER JOIN ANNUAL_BUDGET_BUDGET_CENTER c 
            ON c.BUDGET_ID = 25
            where (START_DATE BETWEEN '$startdate' AND '$enddate')
            ORDER BY ID DESC

Please help how to get data with text datattype with range. How to convert text to data . Please help me

Upvotes: 0

Views: 173

Answers (2)

Craig Gers
Craig Gers

Reputation: 621

According to My SQL Reference - DATE, DATETIME, and TIMESTAMP Types

MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format.

I would suggest trying this format.

There is also a worked example where variables are used in date queries on :

TutorialsPoint

Upvotes: 0

karan shah
karan shah

Reputation: 370

have you tried using STR_TO_DATE() function available in mysql ? I think something like below should work.

SELECT m.id, m.centers, c.BUDGET_ANNUAL_AMOUNT
            FROM Cost_centers m INNER JOIN ANNUAL_BUDGET_BUDGET_CENTER c 
            ON c.BUDGET_ID = 25
            where (STR_TO_DATE(START_DATE, "%d-%m-%Y") BETWEEN '$startdate' AND '$enddate')
            ORDER BY ID DESC

Upvotes: 2

Related Questions