asked on

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.

brejk

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.

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveH_UK

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;

ee_rlee

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.

proteam4

ASKER

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.

Pratima

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

Pratima

Just Remoce '60' quotes('') as you have changed the datatype now to int no need of that

proteam4

ASKER

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),

proteam4

ASKER

And i think single quotes will not make any difference??

Pratima

SELECT * FROM tblFareMaster(NOLOCK) WHERE StartTime >= 60
is it workinmg ?

Pratima

Just try this .. conditions are changes
SELECT * FROM tblFareMaster(NOLOCK) WHERE StartTime <= 60 AND EndTime >= 60

proteam4

ASKER

Yes,
This is working,

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

Thanks Pratima.

Pratima

your welcome :)

proteam4

ASKER

Thanks Partima, your solution helped me a lot.