Convert datetime to seconds

Kristina123 · #1 (**permalink**) 12-17-04, 17:19

Hello. I have a datetime field that stores data that looks like:
1899-12-30 00:01:28.000

Unfortunately this column represents a Call Duration (in seconds). For example, in the above data the call lasted 1 minute and 28 seconds.

I can't seem to figure out how to either
1) return only the last eight characters of this field (in excel it would be the equivelent of MID, RIGHT, LEFT). From here I was thinking that I could use CONVERT?
OR
2) convert from date/time to int which represents total seconds.

Any help is greatly appreciated!
-Kristina

Pat Phelan · #2 (**permalink**) 12-17-04, 23:14

Where in foo-floggy did you get the 1899-12-30 ?!?! I've seen lots of strange values pop up in applications, but that's a new one to me! There are two different approaches to your problems (one for each problem, of course). They are:

Code:

DECLARE @d DATETIME
SET @d = '1899-12-30 00:01:28.000'

SELECT DateDiff(second, '00:00', Convert(VARCHAR(30), @d, 14))
,  Right(Convert(VARCHAR(12), @d, 14), 8)

-PatP

healdem · #3 (**permalink**) 12-18-04, 05:39

taking the hint that

Quote:

in excel it would be the equivelent of MID, RIGHT, LEFT

you might be using a MS product what are you accessing this database with. I'd be tempted to stick this into a formatting funtion. In Access SQL you can put the function into the SQL to create a derived field in the query, you may be able to do the same or similar depending on the server you are using.

However it does sound as if the data model is either incorrectly specified or populated. What is it that is setting the day/date component. If that is consistent then you may be OK as you are. However if the vendor decides to change the baseline method between versions (its hardly unkown for suppliers to be so creative) then you could have a problem.

Personally I'd want to tie down the input so that it was only Hours/minutes/seconds, which I if couldn't get a stable method of storing that in the DB I'd probably resort to storing it as a long integer, and writing an encoding / decoding function to het back to the DD:MM:MM

S value you expect to see.

r937 · #4 (**permalink**) 12-18-04, 06:54

pat, 1899-12-30 is the base date for datetime values supplied without a date component in either sql server or access

kristina, go with option 2)

time durations should never be stored as datetimes

Pat Phelan · #5 (**permalink**) 12-19-04, 16:32

Quote:

Originally Posted by r937

1899-12-30

Of the two values I've seen used as a base date, that one hasn't ever been a choice. What version are you using?

-PatP

r937 · #6 (**permalink**) 12-19-04, 17:05

me? i'm using 08.00.0760

Pat Phelan · #7 (**permalink**) 12-20-04, 04:01

Interesting. The two choices that I've seen are 1900-1-0 and 1904-1-0, which compute as 1899-12-31 and 1903-12-31. If I try to enter 1899-12-30 as a date, neither MS-Excel nor MS-Access recognize it as such, and they treat it as raw text.

Maybe it is something in the international versions? I'll have to try it on a UK machine if I have a chance tomorrow.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech