I’m developing a program in Java that saves medical information of employees, and within it, there is the date of the last medical studies they have done. The dates are saved in a SQL database as String with the format “dd/MM/yyyy”. So at some point, the program needs to filter only those whose last medical studies were one year ago or more to be deployed in a JTable.
I’m looking for a SQL command to filter them and that can be globally used in different types of SQL Servers. Right now I’m using SQLite but I want the statement to work the same in MySQL by saving the dates as Strings in the column. This is because the infrastructure of the workplace is poor and the internet is really bad, that’s why I’m using SQLite but if this problem is solved, the idea is to change to MySQL with very few changes in the code.
The program already filters them with an algorithm that returns the period of time between the last medical study and the current date, but I want an SQL statement so I don’t have to recover all the information and deploying only those whose period is more than or equal to a year.
I’ve been searching for ways to cast the String into a date and then returning those whose period is 1-year or more but none of them worked properly.
Advertisement
Answer
You got the right answer – use the ISO 8601 format.
If you still want to keep the dates in dd/MM/yyyy format, then you’ll need to complicate your SELECTs to convert the date in something that, while still a string, has a lexicographic order that is date-compatible (which means something much more like ISO-8601).
Select dates after 14/02/2019:
…WHERE (SUBSTR(dateaschar, 7, 4)||SUBSTR(dateaschar, 4, 2)||SUBSTR(dateaschar, 1, 2)) > ‘20190214’;