I am having a problem related to comparision of Time in SQL, I am describing my problem here, please suggest me whtever ur suggestion regarding this problem.
I am having a table Fare in this table i am having columns as follows:
Id - INT - IDENTITY
Amount - Float
StartTime - char(5)
EndTime - char(5)
And i am having the data like below:
1 - 20 - 00:00 - 01:00
2 - 50 - 01:00 - 02:00
3 - 70 - 02:00 - 03:00
Now i have retrive the amount from the table on the basis of time spent by the user. Lets suppose user spent 1 hour or 1hour 10 min Then i have to retireve 20 and the for 1:10 min it will be 50.
Now as i mentioned above that these starttime and endtime two columns are of char datatype so i am not able to perform any comparison with these columns on the basis of time spent.
So please, help me to solve this problem. OR if someone having a better approach to handle this situation then tell me.