Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

date

mysql

People also ask

How do I get just the date from a timestamp?

You can use date(t_stamp) to get only the date part from a timestamp. Extracts the date part of the date or datetime expression expr.

How do I select a date in MySQL?

How to Select rows from a range of dates with MySQL query command. If you need to select rows from a MySQL database' table in a date range, you need to use a command like this: SELECT * FROM table WHERE date_column >= '2014-01-01' AND date_column <= '2015-01-01';

How do I select a date from a timestamp in SQL?

In MySQL, use the DATE() function to retrieve the date from a datetime or timestamp value. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a timestamp/datetime column. (In our example, we use a column of the timestamp data type.)


SELECT DATE(ColumnName) FROM tablename;

More on MySQL DATE() function.


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


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')

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.


Simply You can do

SELECT DATE(date_field) AS date_field FROM table_name

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.