CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Xhonor
Xhonor

Reputation: 59

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

Upvotes: 0

Views: 358

Answers (3)

Xhonor
Xhonor

Reputation: 59

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

Upvotes: 0

O. Jones
O. Jones

Reputation: 108736

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.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

you should use str_to_date this way 

 select str_to_date('10.04.2018 12:52:18', '%d.%m.%Y %T')

Upvotes: 0

Related Questions