Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Express 2008 and Access Datatype consideration - time durations Expand / Collapse
Author
Message
Posted Thursday, March 18, 2010 8:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 12:57 AM
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.
Post #885585
Posted Thursday, March 18, 2010 4:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 12:57 AM
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?
Post #885966
Posted Thursday, March 18, 2010 5:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 12:57 AM
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
Post #885992
Posted Saturday, February 19, 2011 5:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 1, 2011 4:28 PM
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
Post #1066770
Posted Saturday, February 19, 2011 7:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #1066784
Posted Saturday, February 19, 2011 8:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 1, 2011 4:28 PM
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

Post #1066788
Posted Saturday, February 19, 2011 1:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #1066807
Posted Saturday, February 19, 2011 1:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 1, 2011 4:28 PM
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
Post #1066810
Posted Saturday, February 19, 2011 4:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #1066819
Posted Saturday, February 19, 2011 4:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 1, 2011 4:28 PM
Points: 9, Visits: 14
Cool I will give a try and let you know... thanks for the reply :)
Post #1066821
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse