• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

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.

0
proteam4
Asked:
proteam4
1 Solution
 
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
 
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now