[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

Sports timing 00:00:00:00 and sql data types

I need to store seconds and hundreths in mssql.
This is to record sports times.
I tried inputting based on my best guess 00:00:23:09 being 23 seconds and 09 hundreths of a second but obviously this doesnt work.
How should I go about doing this as I need to have time so I can rank the results.
0
roblickley
Asked:
roblickley
  • 8
  • 5
  • 2
  • +2
2 Solutions
 
James MurrellProduct SpecialistCommented:
0
 
roblickleyAuthor Commented:
thanks - read it but still none the wiser!
2005 SQL server I should have stated sorry.
I still cant see how this is going to allow me to store what I need
0
 
Patrick MatthewsCommented:
Hello roblickley,

Use a datetime column, and enter it like this:

INSERT INTO LapTimes (AthleteID, EventID, Elapsed) VALUES (1, 42, '00:00:23.090')

The last bit is miliseconds.  Keep in mind that that the maximum precision of a datetime column is three
miliseconds.

Regards,

Patrick
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Chris LuttrellSenior Database ArchitectCommented:
Since you are on an older version then you have the two options for date/time; Datetime and SmallDatetime.  2005 does not have just a Time datatype so everyone just uses one of these and ignores the date portion.
Datetime will get down to 3.33 milliseconds accuracy with a value like '00:00:23.090' and smalldatetime will hold to just the second.
See 2005 docs, http://msdn.microsoft.com/en-us/library/ms187819%28SQL.90%29.aspx 
0
 
roblickleyAuthor Commented:
Ok, so I have read everything on this post - many thanks to all contributors.

I still have a big problem - how to actually get the data into the table.

I have to have fields on the results page which allow officials to enter the seconds and hundreths in boxes to make this process easy.

I am taking, for example, TimeS and TimeH as seconds and fractions respectively into a sotred procedure and then trying to get this converted within to a date time - with the correct numbers in the correct places!

Attached is the line of code - but it is generating an error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
 varchar value '00:00:' to a column of data type int

how can I take these two values and merge them so that they represent the correct part of the time and then store in the database.

convert(datetime,'00:00:'&@RoundTimeH&'.'&@RoundTimeS,14)

Open in new window

0
 
roblickleyAuthor Commented:
sorry I know the RoundTimeH and S are the wrong way around. Still no difference!
0
 
Chris LuttrellSenior Database ArchitectCommented:
assuming your 2 variables are of type varchar so that the .09 is stored as '09' in the @RoundTimeH variable then
convert(datetime,'00:00:'+@RoundTimeS+'.'+@RoundTimeH,14)
should work, the & is a boolean operator in SQL.
0
 
roblickleyAuthor Commented:
The two variables are integers - should I change to varchar
0
 
roblickleyAuthor Commented:
i did that and it is working - however my database does not seam to be recording the milliseconds and when I try to rank based on this it does not take into account.

the colum shows 01/01/1900 00:00:58 but no 58.450 as I would have expected.

I checked and datetime is set for the column - am I missing something on my database set up to allow this to operate?
0
 
Chris LuttrellSenior Database ArchitectCommented:
Integers will make it trickier, because of the .09.  Use
=DATEADD(ms,@RoundTimeH*10,DATEADD(s,@RoundTimeS,0))
Starting at the right, inner most (), it is starting with Date 0, (01/01/1900) the same as the other example would do, and adding your seconds value to it, then multiplying your hundreths value by 10 to convert to a millisecond value and adding that.  The example below gives 1900-01-01 00:00:23.090 as the result.
DECLARE @d DATETIME, @RoundTimeH SMALLINT, @RoundTimeS SMALLINT
SET @RoundTimeS = 23
SET @RoundTimeH = 09
 
--SET @d = convert(datetime,'00:00:'+convert(VARCHAR,@RoundTimeS)+'.'+convert(VARCHAR,@RoundTimeH),14)
SET @d = DATEADD(ms,@RoundTimeH*10,DATEADD(s,@RoundTimeS,0))
 
SELECT @d

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
what is the column datatype?  I bet it is smalldatetime not datetime as I tried to describe above.
0
 
roblickleyAuthor Commented:
it is in fact datetime - I read the above and made sure it was.
0
 
Anthony PerkinsCommented:
>>it is in fact datetime - I read the above and made sure it was.<<
It is because you are viewing it from Open Table in SSMS, don't do that.  The milliseconds are there, just not displayed.  Use the query pane instead and you will see them.
0
 
Chris LuttrellSenior Database ArchitectCommented:
cool, thanks ac, I was wondering why he wasn't seeing them.  Did not know how he was viewing the date value.
0
 
Anthony PerkinsCommented:
I am just guessing and I hope they do not award me any points if I am right, they would be undeserved.
0
 
roblickleyAuthor Commented:
thanks guys - it is indeed stored there.
Have another question - will post new.
0
 
roblickleyAuthor Commented:
excellent again!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 8
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now