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

1
  • 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, 2015 at 14:38

1 Answer 1

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.

2
  • 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
    – Sithered
    Jun 30, 2015 at 6:43
  • I confirm, it worked very well. Thank you
    – Sithered
    Jun 30, 2015 at 7:22

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.