How to select date from datetime column

Question

I have a column of type  datetime  with values like 2009-10-20 10 00 00  I would like to extract date from datetime and write a query like   SELECT   FROM  data  WHERE datetime    2009-10-20   ORDER BY datetime DESC   Is the following the best way to do it   SELECT   FROM  data  WHERE datetime BETWEEN  2009-10-20 00 00 00  AND  2009-10-20 23 59 59   ORDER BY datetime DESC   This however returns an empty resultset  Any suggestions

User · Answer

Well  using LIKE in statement is the best option  WHERE datetime LIKE  2009-10-20    it should work in this case

User · Answer

Here are all formats  Say this is the column that contains the datetime value  table data    --------------------    date created          --------------------    2018-06-02 15 50 30   --------------------      mysql gt  select DATE date created  from data   --------------------    DATE date created     --------------------    2018-06-02            --------------------      mysql gt  select YEAR date created  from data   --------------------    YEAR date created     --------------------                  2018    --------------------      mysql gt  select MONTH date created  from data   ---------------------    MONTH date created     ---------------------                      6    ---------------------      mysql gt  select DAY date created  from data   -------------------    DAY date created     -------------------                    2    -------------------      mysql gt  select HOUR date created  from data   --------------------    HOUR date created     --------------------                    15    --------------------      mysql gt  select MINUTE date created  from data   ----------------------    MINUTE date created     ----------------------                      50    ----------------------      mysql gt  select SECOND date created  from data   ----------------------    SECOND date created     ----------------------                      31    ----------------------

User · Answer

Though all the answers on the page will return the desired result  they all have performance issues  Never perform calculations on fields in the WHERE clause  including a DATE   calculation  as that calculation must be performed on all rows in the table   The BETWEEN     AND construct is inclusive for both border conditions  requiring one to specify the 23 59 59 syntax on the end date which itself has other issues  microsecond transactions  which I believe MySQL did not support in 2009 when the question was asked    The proper way to query a MySQL timestamp field for a particular day is to check for Greater-Than-Equals against the desired date  and Less-Than for the day after  with no hour specified   WHERE datetime gt   2009-10-20  AND datetime lt  2009-10-21    This is the fastest-performing  lowest-memory  least-resource intensive method  and additionally supports all MySQL features and corner-cases such as sub-second timestamp precision  Additionally  it is future proof

User · Answer

You can format the datetime to the Y-M-D portion   DATE FORMAT datetime    Y- m- d

User · Answer

You can use     DATEDIFF   day   startdate   enddate     0   Or     DATEPART  day  startdate     DATEPART day  enddate  AND  DATEPART  month  startdate     DATEPART month  enddate  AND DATEPART  year  startdate     DATEPART year  enddate    Or    CONVERT DATETIME CONVERT VARCHAR 12   startdate  105     CONVERT DATETIME CONVERT VARCHAR 12   enddate  105

User · Answer

You can use MySQL s DATE   function   WHERE DATE datetime     2009-10-20    You could also try this   WHERE datetime LIKE  2009-10-20     See this answer for info on the performance implications of using LIKE

User · Answer

Using WHERE DATE datetime     2009-10-20  has performance issues  As stated here    it will calculate DATE   for all rows  including those that don t match  it will make it impossible to use an index for the query    Use BETWEEN or  gt    lt     operators which allow to use an index   SELECT   FROM data  WHERE datetime BETWEEN  2009-10-20 00 00 00  AND  2009-10-20 23 59 59      Update  the impact on using LIKE instead of operators in an indexed column is high  These are some test results on a table with 1 176 000 rows    using datetime LIKE  2009-10-20      2931ms using datetime  gt    2009-10-20 00 00 00  AND datetime  lt    2009-10-20 23 59 59     168ms   When doing a second call over the same query the difference is even higher  2984ms vs 7ms  yes  just 7 milliseconds    I found this while rewriting some old code on a project using Hibernate

User · Answer

simple and best way to use date function   example   SELECT   FROM  data  WHERE date datetime     2009-10-20     OR   SELECT   FROM  data  WHERE date datetime    gt      2009-10-20    amp  amp  date datetime     lt    2009-10-20

[mysql] How to select date from datetime column?

Examples related to mysql

Examples related to date

Examples related to datetime