Times in Sql Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Times in Sql Server

Author

Topic

bobbabuoy
Starting Member

33 Posts

Posted - 2004-08-29 : 08:28:50

I want to enter times for running event performances (like 5:02.35 for a person's mile time, for instance). I am using sql server 2000. What is the best data type to use for that? I was thinking smalldatetime but wondering if there is a better way. Ultimately these times will have to be ordered so I want to use the correct data type to facilitate that process.

Thanks!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-29 : 08:37:36

Hi,

I would use a datetime. ( smalldatetime is only accurate to the minute )
Also I would start the yyyymmdd part of some specified date, say 19000101.

SELECT CAST( '19000101 ' + '0:5:2:350' AS DATETIME ) union all
SELECT CAST( '19000101 ' + '00:05:02:350' AS DATETIME )
------------------------------------------------------
1900-01-01 00:05:02.350
1900-01-01 00:05:02.350

SELECT CAST( '19000101 ' + '0:5:2:350' AS SMALLDATETIME ) union all
SELECT CAST( '19000101 ' + '00:05:02:350' AS SMALLDATETIME )
------------------------------------------------------
1900-01-01 00:05:00
1900-01-01 00:05:00

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-29 : 08:54:43

You can leave out the date and it will default to 0 (= 19000101)
for your example with a datetime

insert tbl select '5:02:35'
no need for a convert as this will be implicit.
Note that a datetime is only accurate to about 3 millisecs but if you are talkimg about athletics I think they are given to 10 ms so that should be ok.

To retrieve the times use a style that excludes dates e.g. 114

You could also hold the values in a character column and convert for any aggregates - doesn't really matter - both types will support ordering.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bobbabuoy
Starting Member

33 Posts

Posted - 2004-08-29 : 09:14:53

Ok this is helpful. So here is what I am trying to do:

I have an input in my asp that has a running time entered (say 5:02.35 for instance) and I am updating a recordset with that as one of the records to update. Here is how I am doing it:

.
.
.
rs(2).Value = Request.Form.Item("this_time")
.
.
.

And it puts 5:02:35 AM into the database, which doesn't appear to me to be acceptable. I am unfamiliar with the 'select cast' concept. Coule you please explain this or point to a tutorial for me?

Thanks!

SamC
White Water Yakist

3467 Posts

Posted - 2004-08-29 : 09:21:37

If you add 5:02:35 to a base date, it'll be AM if you think about it. (Or for that matter, it will be AM even if you don't think about it.)

Nigel suggested returning the time using style 114

SELECT Name, CONVERT(VARCHAR, ElapsedTime, 114) As [Elapsed Time]

This will return a varchar field in the format hh:mi:ss:mmm, and it's in 24 hour format so there won't be an AM or PM.

Sam

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-29 : 09:58:07

Basically there are 3 options for storing the time
1. As a datetime field
2. As a char field
3. In several (int) fields : hour, minute, second, millisecond

In this discussion we have mentioned 1 & 2.
The cast and convert syntax is just for converting one datatype to another,
with the convert() function you can format a datetime in various ways

Note:
Make the app insert the data in a formated manner
hh:mm:ss:mmm

create table #time( time1 datetime not null, time2_hhmissmmm char(12) )

insert #time( time1, time2_hhmissmmm )
values( '00:05:02:350', '00:05:02:350' )

select time1, time2_hhmissmmm, convert(varchar,time1,114), cast(time2_hhmissmmm as datetime) from #time

time1                                                  time2_hhmissmmm                                                                                       
------------------------------------------------------ --------------- ------------------------------ ------------------------------------------------------ 
1900-01-01 00:05:02.350                                00:05:02:350    00:05:02:350                   1900-01-01 00:05:02.350

Maybe this helps.

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-29 : 10:24:57

option 3 that rockmoose mentions is probably not a good idea as it complicates arithmetic.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bobbabuoy
Starting Member

33 Posts

Posted - 2004-08-29 : 14:06:59

Ok I feel like an idiot but I am just not processing the entire procedure in terms of what I need to do. I will include my code below. This code will take a time of 10:25.2 and write it to the database as 10:25:02 AM:


Set rs=Server.CreateObject("ADODB.Recordset")
sql = "SELECT FinalTime FROM CCIndRslts"
rs.Open sql, conn, adOpenKeyset, adLockOptimistic
	
rs(0).Value = Request.Form.Item("time")
rs.Update
rs.MoveNext
rs.Close
Set rs = Nothing

I don't have a problem with that but I don't like the fact that '.2' turned in to ':02'. I am also getting occassional 'type mismatch' errors.

Thanks for putting up with my ignorance!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-29 : 14:27:45

quote:
Originally posted by nr

option 3 that rockmoose mentions is probably not a good idea as it complicates arithmetic.

Ok, don't agree with that.
If arithmetic is complicated you could always create a view or computed columns in the table that does the logic for You.
Furthermore this design is fully normalized.
Here is an example of computed columns.

CREATE TABLE #timings(
	hr TINYINT NOT NULL DEFAULT 0,
	mi TINYINT NOT NULL DEFAULT 0,
	sec TINYINT NOT NULL DEFAULT 0 CHECK(sec BETWEEN 0 AND 59),
	msec SMALLINT NOT NULL DEFAULT 0 CHECK(msec BETWEEN 0 AND 999),
	timed AS DATEADD(HOUR,hr,DATEADD(MINUTE,mi,DATEADD(SECOND,sec,DATEADD(MILLISECOND,msec,0)))),
	timec AS CAST(REPLACE(STR(hr,2)+':'+STR(mi,2)+':'+STR(sec,2)+':'+STR(msec,3),' ','0') AS CHAR(11)) )

INSERT #timings( hr, mi, sec, msec )
VALUES( 0, 5, 2, 350 )

INSERT #timings( mi, sec )
VALUES( 42, 23 )

SELECT timed, timec FROM #timings

timed                                                  timec       
------------------------------------------------------ ----------- 
1900-01-01 00:05:02.350                                00:05:02:35
1900-01-01 00:42:23.000                                00:42:23:00

/* Chaos is the nature of things...Order is a lesser state of chaos */

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-29 : 14:33:00

quote:
Note:
Make the app insert the data in a formated manner
hh:mm:ss:mmm

This will solve Your problem.

Also when you retirve the data you should know in what format it comes back to you.
If that doesn't suit the presentation, then the presentation logic would have to
do the proper formatting logic.

/rockmoose

/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources