SQL Dates
I would think of it this way: in fact, the server stores the date as a link for that day. It does not concern you. When storing data or reading data from such a date column, the server presents this date using a specific calendar, which is agreed upon. What I'm trying to say, I would not consider the stored value of a gregorian, although it may well be. I would prefer that the rescheduled date be gregorian.
Thus, the best solution, in my opinion, is to accept this fact and the transformation between Gregorians and Hijras on the application side. That way you can use regular checks between .
Numeric Lines
If this is not possible, because the language-dependent conversion is too complicated or because the mapping between Hijri and Grogorian is not unique or unknown in advance, then you will need to save the date in some other form. Possible forms that come to my mind are either varchar containing the lines of the form YYYY-MM-DD , with letters denoting numbers. This scheme ensures that the strings will be compared with the dates that they represent, so you can use between for them. However, including these lines in your dates will still be difficult.
One or more numeric columns
So, I would suggest that you use three columns, each of which contains a date number. Then you could use 10000*year + 100*month + day_of_month to get one number for each day, which you could use for comparison, and between . On the other hand, you can use the ELT function in your queries to return the number for the month back to the name. If performance is a problem, you might be better off saving only one number and splitting it into parts after selection. In the Gregorian calendar, it will look like this:
CREATE TABLE tableName (myDate DECIMAL(8)); SELECT myDate DIV 10000 AS year, ELT((myDate DIV 100) MOD 100, "Jan", "Feb", …) AS month, myDate MOD 100 AS day_of_month FROM tableName WHERE myDate BETWEN 20121021 AND 20121023;
Compatibility and Convenience
If you need to maintain read-only compatibility with code that expects a single text date column, you can use VIEW to provide this. For example, for the German Gregorian format DD. MMMM YYYY DD. MMMM YYYY you can use this code:
CREATE VIEW compatibleName AS SELECT CONCAT(myDate MOD 100, ". ", ELT((myDate DIV 100) MOD 100, "Januar", "Februar", …), ". ", myDate DIV 10000) as dateString, *
Decoding Strings
If you need read and write access by another application in line format, you will have to parse these lines yourself. You can do it at the SQL level. Useful tools are SUBSTRING_INDEX to split the string into fields and FIELD to turn the month name into a number. You might want to add a trigger to the database, which ensures that your lines will always be in a valid format that you can decompose in this way. This question provides details on how to use triggers to enforce such checks.