CODEWITHSUNDEEP
mysqlsql
juniordev101
juniordev101

Reputation: 23

Converting dates in MySql confusing

I am using MySql and by standard their date format is 2019/03/31 in the database.

In my program .net, csharp i read the values into a variable as 31/03/2019, so str_date_format will not work as this only accepts - instead of /.

The strings CAN'T be hard coded in the where clause they have to be read in as parameters.

So from reading the manual i came to the conclusion that i need to use date_format with delimiters in the where clause 

SELECT 
    DATE_FORMAT(date_time,'%d-%m-%Y'), 
    AVG(Total), 
    SUM(Total_ly),  
    AVG((Total + Total_ly)/2) 
FROM transaction 
WHERE 
    date_format(date_time >=  @param1 , '%d-%m-%Y') 
    AND date_format(date_time <= @param2, '%d-%m-%Y') 
GROUP BY date_time;

But its returning null for every column and it doesn't make sense because without the where clause it works fine.

I edited with more detail hope this helps, Appreciate any advice in the right direction !

Upvotes: 1

Views: 115

Answers (3)

forpas
forpas

Reputation: 164099

The format that you use:

'%d-%m-%Y'

is not a comparable format like %Y-%m-%d.
But even if it was, this:

date_format(date_time >=  "22-03-2019" , '%d-%m-%Y')

is wrong, it should be written as

date_format(date_time, '%d-%m-%Y') >=  "22-03-2019"  -- again not comparable.

You can do what you need by using STR_TO_DATE() to convert a string to date:

SET @start = '22/03/2019';
SET @end = '24/03/2019';
select 
  date_format(date_time,'%d/%m/%Y'), 
  AVG(Total), sum(Total_ly),  
  AVG((Total + Total_ly)/2) 
from Transaction 
WHERE date_time between STR_TO_DATE(@start, '%d/%m/%Y') AND STR_TO_DATE(@end, '%d/%m/%Y') 
Group By date_time;

Upvotes: 1

GMB
GMB

Reputation: 222482

I guess that you actually mean:

SELECT 
    DATE_FORMAT(date_time,'%d-%m-%Y'), 
    AVG(Total), 
    SUM(Total_ly),  
    AVG((Total + Total_ly)/2) 
FROM transaction 
WHERE 
    date_time >=  STR_TO_DATE('22/03/2019' , '%d/%m/%Y') 
    AND date_time <= STR_TO_DATE('24/03/2019', '%d/%m/%Y') 
GROUP BY date_time;

Details:

  • DATE_FORMAT() can indeed be used to turn a datetime to a string in the expected format

  • STR_TO_DATE() converts a string to a date, given a format spec; you can use it to turn your input strings to dates before comparing them with column date_time. Or you can simply provide the input dates in the correct format, which is yyyy-mm-dd

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269923

I think you intend the where clause to be:

where date_time >= '2019-03-22' and
      date_time <= '2019-03-24'

Don't convert dates to strings for comparisons.

Upvotes: 3

Related Questions