phpmyadmin Question posted in
The official documentation can be found here.

Text date to dateformat SQL phpmyadmin

I am trying to display the number of records for the current month for a specific user. Unfortunately, it does not work, because At one time, the date format in the Database was not correctly indicated.
The text column, the date is written in plain text format.
How to be? I tried a lot, nothing comes out.

I try STR_TO_DATE, convert and other – the output is either null or mysql error.
I also tried to manually enter a request into phpmyadmin. Unsuccessfully.

Mysql version: 5.6.39-83.1 (Linux)

$modercount = mysqli_query($link, 'SELECT moder, COUNT(*) FROM my_logs WHERE server="'.$profileid.'" AND MONTH(time) = MONTH(NOW()) AND YEAR(time) = YEAR(NOW()) GROUP BY moder ORDER BY COUNT(*) DESC') or die(mysqli_error($link));

Date format in my sql column: 10.04.2018 12:52:18

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    This works for me. 

    $modercount = mysqli_query($link, 'SELECT moder, COUNT(*) FROM mylogs WHERE server="'.$profileid.'" AND MONTH(str_to_date(`time`, "%d.%m.%Y %T")) = MONTH(NOW()) AND YEAR(str_to_date(`time`, "%d.%m.%Y %T")) = YEAR(NOW()) GROUP BY moder ORDER BY COUNT(*) DESC') or die(mysqli_error($link));

    Big thanks to @scaisedge and @o-jones


  2. 0

    you should use str_to_date this way 

     select str_to_date('10.04.2018 12:52:18', '%d.%m.%Y %T')
    Login or Signup to reply.
  3. 0

    First of all, get your textually formatted dates translated correctly to DATETIMEs. STR_TO_DATE() uses the same format strings as its reverse, DATE_FORMAT().

     SELECT STR_TO_DATE(`time`, '%Y.%m.%d %H:%i:%s')

    Second, select the range of datestamps you need like this, using LAST_DAY() to handle month arithmetic.

     WHERE  STR_TO_DATE(`time`, '%Y.%m.%d %H:%i:%s') 
                >= LAST_DAY(CURDATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH
   AND  STR_TO_DATE(`time`, '%Y.%m.%d %H:%i:%s')
                <  LAST_DAY(CURDATE) + INTERVAL 1 DAY

    When you get a chance, add a new column to your table with the datestamps in DATETIME or TIMESTAMP format. That way your date-range selections can exploit indexes. How to do that is beyond the scope of your question today.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search