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

User · Answer

Use DATE FORMAT  select DATE FORMAT date   d   from tablename   gt Date only   example   select DATE FORMAT  date column    d   from  database name   table name

User · Answer

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  quot   quot   amp  inDate ToString format   quot yyyy - MM - dd quot    amp   quot   quot   amp  inTime  amp   quot   quot  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    quot Select   From SOMETABLE Where SOMEDATETIMEFIELD BETWEEN  quot   amp  MySQLDateTimeVar DateTimePicker1 Value  quot 00 00 00 quot    amp   quot  AND  quot   amp  MySQLDateTimeVar DateTimePicker2 Value  quot 23 59 59 quot    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

User · Answer

In the interest of actually putting a working solution to this question   SELECT     WHERE  myDateColumn   gt   DATE DATE FORMAT NOW     Y- m- d       Obviously  you could change the NOW   function to any date or variable you want

User · Answer

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

User · Answer

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

User · Answer

if time column is on timestamp   you will get date value from that timestamp using this query   SELECT DATE FROM UNIXTIME time   from table

User · Answer

Select   from table name where date datetime

User · Answer

SELECT DATE ColumnName  FROM tablename   More on MySQL DATE   function

User · Answer

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

User · Answer

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

User · Answer

Please try this answer   SELECT   FROM  Yourtable  WHERE date  dateField      2018-09-25

User · Answer

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

User · Answer

Simply You can do   SELECT DATE date field  AS date field FROM table name

User · Answer

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

User · Answer

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

User · Answer

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     gt    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

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

Examples related to mysql

Examples related to date