Converting dates in MySql confusing

Question

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 !

It’s a bit confusing to say you need to use a format with slashes and then use one with dashes... — Sami Kuhmonen
– Sami Kuhmonen, Commented Mar 31, 2019 at 12:37

GMB · Accepted Answer · 2019-03-31 15:18:45Z

3

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

edited Mar 31, 2019 at 15:18

answered Mar 31, 2019 at 12:36

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

juniordev101 Over a year ago

This works in theory but taking values for where clause from variables in csharp and it doesnt support - it uses / to seperate dd/mm/yyyy, ill edit question for full scope i think

GMB Over a year ago

@juniordev101... ok so basically you want : STR_TO_DATE('22-03-2019' , '%d/%m/%Y')

juniordev101 Over a year ago

So yeah thats correct, im very new to all this so sorry for all the questions how to i pass parameters in istead of having dates hardcoded in where clause, do you know what i mean ?

GMB Over a year ago

@juniordev101: you can easily find the answer to this question on StackOverflow (or Google). Basically it is not a good practice on Stack Overflow to substantially modify the question after answers were posted. If my answer properly responded to your (original) question, please upvote it and accept it... If you have additional queries that you cannot find answers to by yourself, you may also ask another question. Cheers!

Gordon Linoff · Accepted Answer · 2019-03-31 12:36:21Z

3

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.

answered Mar 31, 2019 at 12:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

juniordev101 Over a year ago

They have to be converted because my program passes the vairables into the sql string

Gordon Linoff Over a year ago

@juniordev101 . . . You should be passing values into the query using parameters not strings. And, parameters can be typed so you can have dates.

forpas · Accepted Answer · 2019-03-31 15:20:14Z

1

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;

edited Mar 31, 2019 at 15:20

answered Mar 31, 2019 at 12:37

forpas

165k10 gold badges51 silver badges85 bronze badges

6 Comments

juniordev101 Over a year ago

This works perfect, i was looking through the mysql documentation and it seems STR_TO_DATE only takes a string, but could this be passed as a param?

forpas Over a year ago

You use STR_TO_DATE when you have a string to convert to date although mysql could do also this: WHERE date_time between '2019-03-22' AND '2019-03-24', but for clarity I use conversion. Of course you can pass a string as a parameter.

juniordev101 Over a year ago

Im sort of confused now so i want the where clasued checked as 31/03/2019 NOT 2019/03/31

forpas Over a year ago

I edited, now you can use 31/03/2019 in the where clause

juniordev101 Over a year ago

YES, this works now, so my last question i researched it im still confused say i have param1 = 22/03/2019, how do i put this in STR_TO_DATE cuase it wont work if i just replace with hardcoded date

|

Collectives™ on Stack Overflow

Converting dates in MySql confusing

3 Answers 3

4 Comments

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related