Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-26-2008
11:31 AM

06-26-2008
11:31 AM

Any one may help a novice on how to add to Time feilds?

example: '05:50:31' + '01:12:30' it's supposed to be '07:03:01' but I'm getting '06:62:61'

Thanks,

example: '05:50:31' + '01:12:30' it's supposed to be '07:03:01' but I'm getting '06:62:61'

Thanks,

6 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-26-2008
02:53 PM

06-26-2008
02:53 PM

Use INTERVAL

select cast('05:50:31' as interval hour to second) + cast('01:12:30' as interval hour to second)

result

7:03:01.000000

select cast('05:50:31' as interval hour to second) + cast('01:12:30' as interval hour to second)

result

7:03:01.000000

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-26-2008
04:21 PM

06-26-2008
04:21 PM

It works, thanks Jeff

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-14-2008
10:25 AM

07-14-2008
10:25 AM

I am trying a similar opperation of subtracting an interval from a timestamp, but I'm getting a syntax error when I try to add days or years into the Interval:

SELECT EXTRACT( SECOND FROM( TIMESTAMP '2008-06-01 00:00:10.000000' - CAST( '06-01 00:00:05' AS INTERVAL DAY TO SECOND)))

If there is a better way to do this, my goal is that I have 2 Timestamp fields in the format of 'YYYY-MM-DD HH:MM:SS.000000' where the zeroes are miliseconds. I'm looking to subtract the two timestamps and determine if at a precision of seconds the two timestamps are equal, or if not but how many seconds they are different. The above is a test to see if the syntax/algorithm of how I'm trying to do this works.

I hope that makes sense.

Thanks for any help in advance.

-Matt

SELECT EXTRACT( SECOND FROM( TIMESTAMP '2008-06-01 00:00:10.000000' - CAST( '06-01 00:00:05' AS INTERVAL DAY TO SECOND)))

If there is a better way to do this, my goal is that I have 2 Timestamp fields in the format of 'YYYY-MM-DD HH:MM:SS.000000' where the zeroes are miliseconds. I'm looking to subtract the two timestamps and determine if at a precision of seconds the two timestamps are equal, or if not but how many seconds they are different. The above is a test to see if the syntax/algorithm of how I'm trying to do this works.

I hope that makes sense.

Thanks for any help in advance.

-Matt

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-28-2010
03:06 PM

09-28-2010
03:06 PM

I am having the extact opposite problem. I can not subtract from a time type field.

I have two time fields (starttime and endtime). I’m wanting to subtract the start from the end and I keep receiving the error “Invalid operation on an ANSI datetime or interval value.

Starttime = 17:41:05

endTime = 18:45:14

Help. I’ve tried the extract function, I’ve tried the convert function but I keep getting the same error.

I have two time fields (starttime and endtime). I’m wanting to subtract the start from the end and I keep receiving the error “Invalid operation on an ANSI datetime or interval value.

Starttime = 17:41:05

endTime = 18:45:14

Help. I’ve tried the extract function, I’ve tried the convert function but I keep getting the same error.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-30-2010
09:26 PM

09-30-2010
09:26 PM

try this:

select cast(cast(endTime as time) - cast(Starttime as time) as time)

select cast(cast(endTime as time) - cast(Starttime as time) as time)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-30-2010
11:16 PM

09-30-2010
11:16 PM

You have to define the resulting datatype:

select (endTime - startTime) hour to second

Dieter

select (endTime - startTime) hour to second

Dieter