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,(Cas t(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..
2  3042 
"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,(Cas t(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_TIMESTA MP AS now,
event_time,
DATEDIFF(SECOND , event_time, CURRENT_TIMESTA MP) AS
difference
FROM Events
CURRENT_TIMESTA MP is the Standard SQL way of getting the current
date and time, functionally equivalent to GetDate which is specific to T-SQL.
--
JAG
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,(Cas t(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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 a command
button. but when that task is submitted the GUI stops responding to
events. I want to provide the option of interrupting the processing
prematurely but providing a command button is not helping beacause the
GUI is not responding to...
|
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 them in my
time calculator and writing them in by hand. Now i realize, that i
really need a script to do this because:
1. It turns out there are hundreds of pages of this stuff.
2. I have to do something similar in again soon.
3. By doing it by...
|
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 work when the form is submitted (and maybe
they should not...), but I do not know how to add a Rounding function
to the existing code, so that each Result is Rounded to 2 decimal
places.
toFixed sounds like it would be ideal...
|
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 having a heck of a time
doing so.
I have created a form where I use drop downs do specify year, month,
date, hour, minute and seconds. When the form is loaded, the dropdowns
have to display the proper values for the current time zone type. This
|
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 corrrection to add or
subtract to this. my only problem is that once the user inputs the time
and the correction its adding it like it was 100 not to 60 any help?
| | |
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 product cost changes (as it should) to
reflect the new average cost. However, the problem is that the selling
price of previously sold items also change when new purchases are
made.
Keep in mind that I'm not storing any calculated values anywhere....
|
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 they select from a drop down list what department/section this
task pertains to and then they select the matter they are discussing
or working on with that department. They enter the time they spent on
this matter and they also enter some more...
|
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 for long running reports. When
the processing is complete it uses crystal reports to load a template file,
populate it, and then export it to a PDF.
It works fine so far....
|
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 rewrite them back to a new Excel file.
When my progam starts I'm able to assign (for example) A1 to a variable and B1 to a variable, and so on and can then do my calculations. But how do I make my program go to the next row (for example) A2, B2,...
|
by: Grubsy4u |
last post by:
Grubsy4u
Newbie
7 Posts October 5th, 2007
11:31 AM
#1
Report calculations
--------------------------------------------------------------------------------
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
