CODEWITHSUNDEEP
mysqlsqldate
aelgoa
aelgoa

Reputation: 1201

mysql comparing two date fields against two date strings

select
start_date,stop_date_original
from dates
where 
start_date is not null
and stop_date_original is not null 
and start_date > str_to_date('10/10/2009','%d/%m/%Y')
/*and stop_date_original < str_to_date('01/24/2013','%d/%m/%Y')*/

this query works fine but when i uncomment the last line or use it to replace the one before the result doesnt get affected or i get an empty result set.

are there issues with this approach that might cause this behaviour?

also, are the null checks intrinsically necesarry across different database systems?

Upvotes: 1

Views: 2197

Answers (1)

fthiella
fthiella

Reputation: 49079

Stop date has to be 24/01/2013, not 01/24/2013:

select
  start_date,
  stop_date_original
from
  dates
where 
  start_date is not null
  and stop_date_original is not null 
  and start_date > str_to_date('10/10/2009','%d/%m/%Y')
  and stop_date_original < str_to_date('24/01/2013','%d/%m/%Y')

or you have to invert day and month on your function str_to_date('01/24/2013','%m/%d/%Y').

Also, if start_date is null, or if stop_date_original is null, the condition would be evaluated as null anyway so you don't need to check if they are not null, although this make things more readable.

Upvotes: 2

Related Questions