1

I have a SQLite database with a table "Rate" who has a column "timeToMaturity". This column contains strings such as :

  • 7Months
  • 26Days
  • 3Years

I'd like to order a query results by timeToMaturity (i.e. 26Days < 7Months < 3Years). However, because they are strings, I have no idea how I should proceed.

Thank you for your help

  • I'm not familiar with Sql Lite, but in other db's the way to proceed might involve casting or converting the strings to date types and ordering by that – Thronk Jun 29 '15 at 14:38
2

If (and it's a big if) the format remains constant with a number and a unit, you should be able to create a case statement that will get the total of the smallest unit. In your example, let's use days as the smallest unit:

CASE
    WHEN timeToMaturity LIKE '%Days' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 1
    WHEN timeToMaturity LIKE '%Weeks' THEN cast(rtrim(InterestRate.timeToMat,'abcdefghijklmnopqrstuvwxyz')as INT) * 7
    WHEN timeToMaturity LIKE '%Months' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 30
    WHEN timeToMaturity LIKE '%Years' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 365
        ELSE 0
    END AS TotalDaysToMaturity

Now, I realize that you may need to do something different when it comes to months as all months are not equal, but it may fit your needs. Perhaps someone would come by to figure that out dynamically.

  • No, it should work. These duration are starting from the current date, so the different number of days in months shouldn't be a problem. I'l try to add it and let you know. Thank you for your help – MarinD Jun 30 '15 at 6:43
  • I confirm, it worked very well. Thank you – MarinD Jun 30 '15 at 7:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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