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?

  • Is this question of the performance or solution to accomplish that? Commented Nov 29, 2016 at 9:19
  • it is a question :) Commented Nov 29, 2016 at 14:25

2 Answers 2


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.

  • If Sedighe can store the dates as integers instead of varchar why not just save it as datetime in first place. Most probability there must be some strong limitation to opt the varchar type column. Commented Nov 29, 2016 at 9:22
  • The Datetime datatype only goes back to 1753. This is because when countries converted to the Gregorian calendar they did all kinds of crazy things like jumping from 3rd of September to 17th of September overnight.
    – pacreely
    Commented Nov 29, 2016 at 9:44
  • I believe datetime2 data type can accommodate the dates prior to 1753. Commented Nov 29, 2016 at 12:40
  • But valid point on 3rd Sep to 17th Sep jump. :) Commented Nov 29, 2016 at 12:40
  • 2
    Datetime2 requires at least 6 bytes of storage, Integer requires 4. Unless you are interested in storing the time-of-day on 1397-12-09 then Integer will provide improved performance.
    – pacreely
    Commented Nov 29, 2016 at 13:35

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.

    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)
  • i use the query and i have this error "Conversion failed when converting date and/or time from character string." Commented Nov 29, 2016 at 13:20
  • when i use datetime2 datatype and i declare columns i want to put data in it i get this error"unable to read data" Commented Nov 29, 2016 at 13:32
  • @Sedighe: Must be the database version issue. It worked on my SQL 2012 server. But anyhow you should opt for the solution proposed by pacreely. That's a very nice and simple solution for your problem rather that converting it to datetime2 before comparison. Commented Nov 30, 2016 at 6:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.