473,320 Members | 1,695 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

HELP with TIME Calculations

I am looking to calculate the difference between and event time and a sample
time of Now. This is the query that I thought would do it, however I'm
returning DIFFERENCE values that look the same when the calcuation is being
made on different EVENT_TIME values....

I thought I knew how DateDiff worked, but apparently not.

select GetDate()as NOW,event_time,Datediff(s,(Cast(event_time as
Numeric)),(Cast(GetDate() as Numeric))) as DIFFERENCE
FROM events
-----------NOW----------|-----EVENT_TIME------|-DIFFERENCE
2004-06-30 11:22:38.560 1999-10-30 23:51:37.000 147225600
2004-06-30 11:22:38.560 1999-10-30 23:23:47.000 147225600
2004-06-30 11:22:38.560 1999-10-30 06:49:38.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:50:00.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:50:41.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:49:59.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:49:58.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:50:53.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:50:46.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:49:42.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:50:36.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:50:07.000 147312000
2004-06-30 11:22:38.560 1999-10-30 10:54:37.000 147312000
2004-06-30 11:22:38.560 1999-10-30 11:40:15.000 147312000
2004-06-30 11:22:38.560 1999-10-30 09:52:51.000 147312000
2004-06-30 11:22:38.560 1999-10-30 12:12:46.000 147225600
2004-06-30 11:22:38.560 1999-10-30 12:32:45.000 147225600
2004-06-30 11:22:38.560 1999-10-30 12:32:45.000 147225600
2004-06-30 11:22:38.560 1999-10-30 12:32:46.000 147225600
2004-06-30 11:22:38.560 1999-10-30 12:46:30.000 147225600
2004-06-30 11:22:38.560 1999-10-30 15:31:25.000 147225600
2004-06-30 11:22:38.560 1999-10-30 23:08:25.000 147225600
2004-06-30 11:22:38.560 1999-10-30 16:35:51.000 147225600
Can someone help?
TIA!

Joe..
Jul 20 '05 #1
2 3020
"Joe User" <jo*@user.com> wrote in message news:cb**********@tribune.mayo.edu...
I am looking to calculate the difference between and event time and a sample
time of Now. This is the query that I thought would do it, however I'm
returning DIFFERENCE values that look the same when the calcuation is being
made on different EVENT_TIME values....

I thought I knew how DateDiff worked, but apparently not.

select GetDate()as NOW,event_time,Datediff(s,(Cast(event_time as
Numeric)),(Cast(GetDate() as Numeric))) as DIFFERENCE
FROM events
-----------NOW----------|-----EVENT_TIME------|-DIFFERENCE
2004-06-30 11:22:38.560 1999-10-30 23:51:37.000 147225600
2004-06-30 11:22:38.560 1999-10-30 23:23:47.000 147225600
2004-06-30 11:22:38.560 1999-10-30 06:49:38.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:50:00.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:50:41.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:49:59.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:49:58.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:50:53.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:50:46.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:49:42.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:50:36.000 147312000
2004-06-30 11:22:38.560 1999-10-30 06:50:07.000 147312000
2004-06-30 11:22:38.560 1999-10-30 10:54:37.000 147312000
2004-06-30 11:22:38.560 1999-10-30 11:40:15.000 147312000
2004-06-30 11:22:38.560 1999-10-30 09:52:51.000 147312000
2004-06-30 11:22:38.560 1999-10-30 12:12:46.000 147225600
2004-06-30 11:22:38.560 1999-10-30 12:32:45.000 147225600
2004-06-30 11:22:38.560 1999-10-30 12:32:45.000 147225600
2004-06-30 11:22:38.560 1999-10-30 12:32:46.000 147225600
2004-06-30 11:22:38.560 1999-10-30 12:46:30.000 147225600
2004-06-30 11:22:38.560 1999-10-30 15:31:25.000 147225600
2004-06-30 11:22:38.560 1999-10-30 23:08:25.000 147225600
2004-06-30 11:22:38.560 1999-10-30 16:35:51.000 147225600
Can someone help?
TIA!

Joe..


DATEDIFF returns the difference between 2 datetime values with respect
to a specified unit of time, e.g., difference between 2 datetime values in
seconds. See Books Online (BOL), the SQL Server online documentation,
for more information. To get the number of seconds between 2 datetime values,
try

SELECT CURRENT_TIMESTAMP AS now,
event_time,
DATEDIFF(SECOND, event_time, CURRENT_TIMESTAMP) AS
difference
FROM Events

CURRENT_TIMESTAMP is the Standard SQL way of getting the current
date and time, functionally equivalent to GetDate which is specific to T-SQL.

--
JAG
Jul 20 '05 #2
Joe User (jo*@user.com) writes:
I am looking to calculate the difference between and event time and a
sample time of Now. This is the query that I thought would do it,
however I'm returning DIFFERENCE values that look the same when the
calcuation is being made on different EVENT_TIME values....

I thought I knew how DateDiff worked, but apparently not.

select GetDate()as NOW,event_time,Datediff(s,(Cast(event_time as
Numeric)),(Cast(GetDate() as Numeric))) as DIFFERENCE
FROM events


Now, pray tell, why did you throw in Numeric into the lot? This is the
source of your problem. If you say Numeric without specifying precision
and scale, you get a default precision which is 18 and a default scale
of 0. This means that you are throwing the time portions out the window.

I don't really which scale you need to use to not lose precision, but
then again, if you just say

select GetDate()as NOW, event_time,
Datediff(s, event_time, getdate()) as DIFFERENCE
FROM events

it's even simpler.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: viator | last post by:
Hello Everybody! I am working on a program which renders its GUI using swing components. The program does some bath processing (some lengthy matematical calculations) when user submit a task using...
15
by: kpp9c | last post by:
I am kind of in a bit of a jam (okay a big jam) and i was hoping that someone here could give me a quick hand. I had a few pages of time calculations to do. So, i just started in on them typing...
12
by: BC | last post by:
I have read the FAQ and the discussions but I am not a Javascript programmer and do not know how to make toFixed (and other techniques) work, after several attempts. The following calculations...
11
by: lduperval | last post by:
Hi, I`m trying to do date calculations in three types of time zones: local, GMT and specified. The issue I am facing is that I need to be able to specify a date in the proper time zone, and I`m...
5
by: iminal | last post by:
I am trying to make a very simple program and am very new to the whole programming thing. my program is supposed to ask a user for any time in the for format XX:XX:XX and then ask for a time...
15
by: Colin | last post by:
I have a query that calculates the selling price of products on customer orders. Selling prices are calculated based on the average cost of the items when purchased. As I make new purchases, the...
1
by: Jessica | last post by:
I have a database where a person is able to enter the different tasks they do each day by specific department and matter. On a form they enter the date, their name, and their title; in a subform...
6
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and creates multiple threads to perform the processing...
1
by: learningvb123 | last post by:
I have about 400 rows of data in Excel. Each Row is a record set. I want to read each cell in each row into VB6 (one row at a time) and then do calculations on the cells in the same row and then...
1
by: Grubsy4u | last post by:
Grubsy4u Newbie 7 Posts October 5th, 2007 11:31 AM #1 Report calculations --------------------------------------------------------------------------------
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.