How to select only date from a DATETIME field in MySQL?

Question

I have a table in the MySQL database that is set up with DATETIME. I need to SELECT in this table only by DATE and excluding the time.

How do I SELECT in this table by only date and bypassing the time, even if that specific column is set to DATETIME?

Example

Now it is: 2012-01-23 09:24:41

I need to do a SELECT only for this: 2012-01-23

Answer 1

SELECT DATE(ColumnName) FROM tablename;

More on MySQL DATE() function.

Answer 2

you can use date_format

select DATE_FORMAT(date,'%y-%m-%d') from tablename

for time zone

sql2 = "SELECT DATE_FORMAT(CONVERT_TZ(CURDATE(),'US/Central','Asia/Karachi'),'%Y-%m-%d');"

Answer 3

Try to use
for today:

SELECT * FROM `tbl_name` where DATE(column_name) = CURDATE()

for selected date:

SELECT * FROM `tbl_name` where DATE(column_name) = DATE('2016-01-14')

Answer 4

You can use select DATE(time) from appointment_details for date only

or

You can use select TIME(time) from appointment_details for time only

Answer 5

In MYSQL we have function called DATE_FORMAT(date,format). In your case your select statement will become like this:-

SELECT DATE_FORMAT(dateTimeFieldName,"%a%m%Y") as dateFieldName FROM table_name

For more information about Mysql DATE and TIME functions click here.

Answer 6

Please try this answer.

SELECT * FROM `Yourtable` WHERE date(`dateField`) = '2018-09-25'

Answer 7

Simply You can do

SELECT DATE(date_field) AS date_field FROM table_name

Answer 8

I tried doing a SELECT DATE(ColumnName), however this does not work for TIMESTAMP columns^† because they are stored in UTC and the UTC date is used instead of converting to the local date. I needed to select rows that were on a specific date in my time zone, so combining my answer to this other question with Balaswamy Vaddeman's answer to this question, this is what I did:

If you are storing dates as DATETIME

Just do SELECT DATE(ColumnName)

If you are storing dates as TIMESTAMP

Load the time zone data into MySQL if you haven't done so already. For Windows servers see the previous link. For Linux, FreeBSD, Solaris, and OS X servers you would do:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Then format your query like this:

SELECT DATE(CONVERT_TZ(`ColumnName`, 'UTC', 'America/New_York'))

You can also put this in the WHERE part of the query like this (but note that indexes on that column will not work):

SELECT * FROM tableName
WHERE DATE(CONVERT_TZ(`ColumnName`, 'UTC', 'America/New_York')) >= '2015-02-04'

(Obviously substitute America/New_York for your local time zone.)

---

† The only exception to this is if your local time zone is GMT and you don't do daylight savings because your local time is the same as UTC.

Answer 9

Yo can try this:

SELECT CURDATE();

If you check the following:

SELECT NOW(); SELECT DATE(NOW()); SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

You can see that it takes a long time.

Answer 10

Try SELECT * FROM Profiles WHERE date(DateReg)=$date where $date is in yyyy-mm-dd

Alternatively SELECT * FROM Profiles WHERE left(DateReg,10)=$date

Cheers

Answer 11

Select * from table_name where date(datetime)

Answer 12

Use DATE_FORMAT

select DATE_FORMAT(date,'%d') from tablename =>Date only

example:

select DATE_FORMAT(`date_column`,'%d') from `database_name`.`table_name`;

Answer 13

you can use date_format

select DATE_FORMAT(date,'%y-%m-%d') from tablename

for time zone

sql2 = "SELECT DATE_FORMAT(CONVERT_TZ(CURDATE(),'US/Central','Asia/Karachi'),'%Y-%m-%d');"

You can use select DATE(time) from appointment_details for date only

or

You can use select TIME(time)  from appointment_details for time only
if time column is on timestamp , you will get date value from that timestamp using this query

SELECT DATE(FROM_UNIXTIME(time)) from table

Answer 14

SELECT DATE_FORMAT(NOW() - INTERVAL FLOOR(RAND() * 14) DAY,'%Y-%m-%d');

This one can be used to get date in 'yyyy-mm-dd' format.

Answer 15

if time column is on timestamp , you will get date value from that timestamp using this query

SELECT DATE(FROM_UNIXTIME(time)) from table 

Answer 16

In the interest of actually putting a working solution to this question:

SELECT ... WHERE `myDateColumn` >= DATE(DATE_FORMAT(NOW(),'%Y-%m-%d'));

Obviously, you could change the NOW() function to any date or variable you want.

Answer 17

I solve this in my VB app with a simple tiny function (one line). Code taken out of a production app. The function looks like this:

Public Function MySQLDateTimeVar(inDate As Date, inTime As String) As String
Return "'" & inDate.ToString(format:="yyyy'-'MM'-'dd") & " " & inTime & "'"
End Function

Usage: Let's say I have DateTimePicker1 and DateTimePicker2 and the user must define a start date and an end date. No matter if the dates are the same. I need to query a DATETIME field using only the DATE. My query string is easily built like this:

Dim QueryString As String = "Select * From SOMETABLE Where SOMEDATETIMEFIELD BETWEEN " & MySQLDateTimeVar(DateTimePicker1.Value,"00:00:00") & " AND " & MySQLDateTimeVar(DateTimePicker2.Value,"23:59:59")

The function generates the correct MySQL DATETIME syntax for DATETIME fields in the query and the query returns all records on that DATE (or BETWEEN the DATES) correctly.