Time in SQL???

Hi guys,
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.
Thanks.

proteam4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brejkCommented:
Why not store also dates? Use smalldatetime data type for StartTime and EndTime columns. Then you will be able to use DATEDIFF function to calculate the difference between two values easily.
0
Pratima PharandeCommented:

Is it possible to change the Database structure ?

the nyou can take only one column with min

1 - 20 - 60
2 - 50 - 120
3 - 70 - 180


then while retriving the records you can just calculate min & compare it & retrive data
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveH_UKCommented:
I'd recommend either using a persisted view or a persisted computed column.  Either way, you can index the computed value of duration.

To do this, use the calculation
SELECT *,
(
  DATEDIFF(hour,
    CONVERT(smalldatetime, [StartTime], 8)),
    CONVERT(smalldatetime, [EndTime], 8))) * 60
+
  DATEDIFF(minute,
    CONVERT(smalldatetime, [StartTime], 8)),
    CONVERT(smalldatetime, [EndTime], 8))
) As MinutesDuration
FROM Fare;
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

ee_rleeCommented:
hi

if the spend time is also a string in nn:ss (i.e 01:10 stands for 1 hour and 10 mins) then you can just compare it directly to the StartTime and EndTime column. It will compared as strings and not date/time but it will still return the correct value.

WHERE [StartTime]<'01:10' AND [EndTime]>='01:10'

will return the 2nd record.
0
proteam4Author Commented:
Hi guys,
I like the pratima's solution of converting hours to minutes, but again i am also facing problem with the minute scenario.

I have changed the structure of the table as follows:

Id - INT - IDENTITY
Amount - Float
StartTime - int
EndTime - int

I have changed the datatype of the StartTime and EndTime column to INT from Char(5)

And now i am having the data like below:

1 - 20 - 0 - 60
2 - 50 - 61 - 120
3 - 70 - 121 - 180


And now lets suppose user spent 1 hour, that means 60 minutes, So i have to fetch the first records amount that is 20$, But i am not able to guess that how will i put the query to fetch the correct record.

I have tried it but its not working, i have written the query like below:
SELECT * FROM tblFareMaster(NOLOCK) WHERE StartTime >= '60' AND EndTime <= '60'

So please tell me that after adopting the minutes scenario how can i fetch the data??? or i have to follow some other approach???

Thanks.
0
Pratima PharandeCommented:

In this Senario you need to send one parameter to the query...that is how much time ...right ?

suppose you pass the value 60 min

declare @val int
@val = 60
SELECT * FROM tblFareMaster(NOLOCK) WHERE StartTime >= @val AND EndTime <= @val

in hardcoded values

SELECT * FROM tblFareMaster(NOLOCK) WHERE StartTime >= 60 AND EndTime <= 60
0
Pratima PharandeCommented:
Just Remoce '60' quotes('') as you have changed the datatype now to int no need of that
0
proteam4Author Commented:
Hi Pratima,

Your query is not returning any value??

SELECT * FROM tblFareMaster(NOLOCK) WHERE StartTime >= 60 AND EndTime <= 60

The above query returns no Rows (zero rows),
0
proteam4Author Commented:
And i think single quotes will not make any difference??
0
Pratima PharandeCommented:
SELECT * FROM tblFareMaster(NOLOCK) WHERE StartTime >= 60  
is it workinmg ?
0
Pratima PharandeCommented:
Just try this .. conditions are changes
SELECT * FROM tblFareMaster(NOLOCK) WHERE StartTime <= 60 AND EndTime >= 60
0
proteam4Author Commented:
Yes,
This is working,

SELECT * FROM tblFareMaster(NOLOCK) WHERE StartTime <= 60 AND EndTime >= 60

Thanks Pratima.
0
Pratima PharandeCommented:
your welcome :)
0
proteam4Author Commented:
Thanks Partima, your solution helped me a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.