Convert string to date in mySQL 2

In my previous blog, I've show how to convert a string in database to a datetime format. That syntax is only supported in mySQL 4.0 and above. In that version, it uses this syntax:

STR_TO_DATE(date,'%d/%m/%Y')

So, for mySQL 4.0 and below, just change the syntax to:

DATE_ADD( CONCAT(SUBSTRING_INDEX(`date`,'/',-1) , '-' , SUBSTRING_INDEX(substring_index(`date`,'/',2),'/',-1) , '-', SUBSTRING_INDEX(`date`,'/',1)), interval 0 day)

Example:

4.0 and above:
SELECT STR_TO_DATE(date,'%d/%m/%Y') AS myNewdate FROM myTable

below 4.0
SELECT DATE_ADD( CONCAT(SUBSTRING_INDEX(`date`,'/',-1) , '-' , SUBSTRING_INDEX(substring_index(`date`,'/',2),'/',-1) , '-', SUBSTRING_INDEX(`date`,'/',1)), interval 0 day) AS myNewdate FROM myTable

Convert string to date in mySQL

if you have a date column in a table that has a string datatype, it is impossible to query or find the record between 2 durations of date. for example, you have a table "myTable"

myTable

---------------------------
Column  | Type
---------------------------
date            | varchar

it is impossible to query like this
SELECT * FROM myTable WHERE date>='@dtFrom' AND date<='@dtTo' the correct way to get the record between the 2 dates is like this SELECT * FROM myTable WHERE STR_TO_DATE(date,'%d/%m/%Y') <= '@dtFrom' AND STR_TO_DATE(date,'%d/%m/%Y') <= '@dtTo' STR_TO_DATE(date,'%d/%m/%Y') the type '%d/%m/%Y' is depend on how you store the date into the table. In my case, I store it as 18/04/2006 more help can be found here - dev.mysql.com

Introduction

Just an introduction. This blog will be discussing on programming with database or database query. Stay tuned for the next update.