How could i search between two string type date

Question

Consider these two tables. I use persian dates, so I have to declare startdate and enddate as a string(nvarchar(10)).

crt table:

id title startdate enddate
--------------------------
1  xyz   1395/01/01  1395/12/29
2  xyy   1394/12/01  1395/05/05
3  cvb   1392/02/03  1394/02/03

season table:

id title startdate enddate
--------------------------
1  spring95 1395/01/01  1395/03/31

I want to write a query that gives me crt records that are valid in a season. IE crt.startdate<=season.startdate and crt.enddate>=season.enddate.

How do I do that?

Answer 1

If you encounter performance problems you could store the dates as integers instead of varchar. 1397-12-09 becomes 13971209, all the numbers remain in sequence so you can still carry out BETWEEN queries. Then convert your results to date format at the final point of presentation.

Answer 2

Below query can help you. You can make use of convert function.

SELECT convert(datetime2, '1395/01/01', 111) will return the 1395-01-01 00:00:00.0000000 in datatime2 format. Why to use datetime2, because the data you have provided can be out of bounds for the usual datetime format. More info can be found in the this stack overflow question.

SELECT *
FROM crt AS C
    INNER JOIN season AS S
    ON C.id = S.id -- Or any required join condition
WHERE convert(datetime2, c.startdate, 111) <= convert(datetime2, s.startdate, 111)
    AND convert(datetime2, c.enddate, 111) >= convert(datetime2, s.enddate, 111)