SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Express 2008 and Access Datatype consideration - time durations


Express 2008 and Access Datatype consideration - time durations

Author
Message
flebber.crue
flebber.crue
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 31
I am very new to SQL. IS there a suitable data-type for storing time values, specifically durations. What do I mean, well I am beginning a sports database. For sports, times as durations, laptimes, sectional times etc are captured and reviewed. Also lap comparisons eg faster and slower than previous or best times are recorded.

Does sql feature a data-type suitable for this need? Or would I need to use a data-type like decimal and then create a formula to convert the time to its lowest base for storage and the same back out.
flebber.crue
flebber.crue
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 31
So what I am asking is, there a date and time formats, that handle what date it is or what the clock time is. But is there a type in SQL that deals with measured time, so that is measured units of time.

So for example car a took 1:21:30 (mm:ss:hundredths) for lap two, which can be saved and then compared to his/her previous lap and others lap times. Of course in a business environment this would be an handle time (time on call) in a call centre etc.

Lots of potential uses but how is it implemented and used?
flebber.crue
flebber.crue
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 31
I have actually found a good resource for time durations and date/time which is very helpful to me. Should be able to help you too.
sqlteam time durations
graham.hilton23
graham.hilton23
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 14
Hi

Did you find a solution to this? All the examples I have seen are taken the system time where as all I need to do is input an event time and compare this to a PB time and update as per.

I am doing this on a voluntary bases for a local competitive swim team to aid them in Sqaud development and event entry!

Thanks
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7901 Visits: 25280
graham.hilton23 (2/19/2011)
Hi

Did you find a solution to this? All the examples I have seen are taken the system time where as all I need to do is input an event time and compare this to a PB time and update as per.

I am doing this on a voluntary bases for a local competitive swim team to aid them in Sqaud development and event entry!

Thanks


Your question is rather vague, to have someone assist you, post your table definition, sample data and required output following the recommendations/instructions/T-SQL in the article whose link is the first link in my signature block. Doing this will more than likely have someone suggest/provide a tested answer to your question.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
graham.hilton23
graham.hilton23
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 14
Objective - to record swimming times for a local club per stroke per person. We will have 2 tables one recording the events on an ongoing basis (Swim Meets) and one recording the current Personal bests per distance and stroke.

The swimmers stroke time needs to be entered into a table as: -
minutes:seconds.hundreth of a second ie 1:32.54

Currently this project is in a design stage, but this value will be manually entered by a user and needs to be in the stated format.

My question is how do I record this value as SQL and Access to not appear to understand the hundreth of a second so I do not how who to record this value! so any advice would be fantastic!

Thanks
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7901 Visits: 25280
Post removed . misunderstood the question

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
graham.hilton23
graham.hilton23
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 14
The input will change on every event so does this just format the field to mins:secs.hundreths of secs?

Every time the person swims on the clock this will be recorded into the database in the events table per distance and stroke.

The idea then will be another table holding the swimmers PB's will be compared with the event times on update and if the time is faster the PB table will be updated.

Hope this makes sense? So the issue is inputting the swim time in the correct format. The time will not be constant as this depending on distance and stroke but the inputted time will be the format evrytime.

If I can make this work it will help the coaches look at the PB's so the swimmer can be assigned to the right training group and also help the coaches submit swimmers and times to new individual or group events!

The buzz word here is trend analyses. Just need to format a field to enter the correct time format!

Thanks
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7901 Visits: 25280
Using the data type TIME(4) test this code to determine if it is what you need.


/*Using your input format think the following will work
to reformat the text field to a TIME data type
(note.01 one hundredth of a seconds = 10 milliseconds */
DECLARE @I VARCHAR(10)
SET @I = '1:32.54'
--Replace the period/decimal point
SET @I = REPLACE(@I,'.',':')
--set the first part of time (hours) to zero
SET @I = '00:'+@I
SELECT @I
SELECT CAST(@I as TIME(4)) -- for testing
-- must edit to correct statement for other
-- data columns and then execute
INSERT INTO table name(column name,columename,racetime)
SELECT somevalue, somevalue, CAST(@I as TIME(4))




If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
graham.hilton23
graham.hilton23
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 14
Cool I will give a try and let you know... thanks for the reply Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search