How to select only date from a DATETIME field in MySQL?
MysqlDateMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
SELECT DATE(ColumnName) FROM tablename;
More on MySQL DATE() function.
Solution 2 - Mysql
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');"
Solution 3 - Mysql
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')
Solution 4 - Mysql
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
Solution 5 - Mysql
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.
Solution 6 - Mysql
Simply You can do
SELECT DATE(date_field) AS date_field FROM table_name
Solution 7 - Mysql
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.
Solution 8 - Mysql
Please try this answer.
SELECT * FROM `Yourtable` WHERE date(`dateField`) = '2018-09-25'
Solution 9 - Mysql
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
Solution 10 - Mysql
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.
Solution 11 - Mysql
Select * from table_name where date(datetime)
Solution 12 - Mysql
Use DATE_FORMAT
select DATE_FORMAT(date,'%d') from tablename =>Date only
example:
select DATE_FORMAT(`date_column`,'%d') from `database_name`.`table_name`;
Solution 13 - Mysql
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.
Solution 14 - Mysql
if time column is on timestamp , you will get date value from that timestamp using this query
SELECT DATE(FROM_UNIXTIME(time)) from table
Solution 15 - Mysql
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
Solution 16 - Mysql
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.
Solution 17 - Mysql
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.