Retrieving date from mysql

Question

I have mysql datatype as date, my date format in database is 20/1/2015. Now I wish to retrieve data using date.

For example:

SELECT * FROM Device_Count where Date='20/1/2015' 
GO

I am getting error like: Incorrect date value: '20/1/2015' for column 'Date' at row 1

Please find and post the status

Ravinder Reddy · Accepted Answer · 2015-02-03 10:55:59Z

6

MySQL's date format is yyyy-mm-dd

change your query as below and it should work, provided your Date column data type is date.

SELECT * FROM Device_Count where Date=STR_TO_DATE('20/1/2015', '%d/%m/%Y' )

Refer to Documentation: STR_TO_DATE(str,format)

answered Feb 3, 2015 at 10:55

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

roeygol · Accepted Answer · 2015-02-03 10:54:46Z

0

Check your date format and compare it by the same format

answered Feb 3, 2015 at 10:54

roeygol

5,07812 gold badges60 silver badges103 bronze badges

jrarama · Accepted Answer · 2015-02-03 10:56:56Z

0

Date is stored in MYSQL in the format of YYYY-mm-dd. Try this query:

SELECT * FROM Device_Count where Date='2015-01-20'
GO

answered Feb 3, 2015 at 10:56

jrarama

9147 silver badges8 bronze badges