473,551 Members | 2,693 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to get the Datediff between two fields

347 Contributor
I'm trying to get the datediff (in minutes) between two fields in a database. These fields are going to be supplied by an end user so they can be any number or any day. The two fields are starttime and endtime and the table name is Exceptions. Can anyone offer a way to do this? I'm using SQL 2000.

Thank you,

Nov 1 '10 #1
4 3560
152 New Member
Books online gives you the syntax
its along the lines of
Expand|Select|Wrap|Line Numbers
  1. Select 
  2. DateDiff(mm, starttime, IsNull(endtime, starttime))
  3. From Exceptions
Nov 2 '10 #2
347 Contributor
When I run that query I get a value of 0 in all fields. I know that this can't be right.

here's the query I have:

Expand|Select|Wrap|Line Numbers
  1. Select 
  2. DateDiff(m, starttime, IsNull (endtime, starttime))
  3. From Exceptions2
Nov 2 '10 #3
347 Contributor
I figured this out .... here is the syntax that I have:

Expand|Select|Wrap|Line Numbers
  1. SELECT datediff(minute, starttime, endtime)  as duration from Exceptions2
  2. where exceptiondate between '5/1/2010' and '5/15/2010'
Nov 2 '10 #4
152 New Member
yup, you spotted the problem - mm means minute in time, but month in datediff and datepart

sorry for the bum steer
Nov 2 '10 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Paolo | last post by:
I am having some problem with a Year Function. I have form on which I have 4 field which indicate dates and an additional form which sums those dates: These are the fields: YEARS STARTINGDATE1 ENDINGDATE1 STARTINGDATE2
by: Miguelito Bain | last post by:
hi everybody. i'm trying to find out what you can and can't do with/in calculated fields. any help, pointers, and/or advice would be greatly appreciated. i'm a newbie, but i want to learn, and i've tried a lot before posting. here's my situation. i have a simple form with dates on it. i have an indate and outdate
by: info | last post by:
Can some one show me how to use the datediff function where the dates are being supplied through an access db. For instance, a recordset would contain these fields: DateAssigned, DateDue, ProjectName, ProjectDetail, Class. The student enters a project name, then a date it was assigned and a date it is due...and other details about the project...
by: perryche | last post by:
4 Fields: Date1 Time1 Date2 Time2 How do I calculate days/hours diff between Date1Time1 & Date2Time2? e.g. 3days 12hours If it were 05/02/05 11pm and 05/03/05 8am? It should say 0day 9hrs.
by: T23Ij9 | last post by:
Hi. I have 3 seperate date fields. InitialDate InspDate ReportDate I am trying to setup several unbound Text boxes in a form that will give me days elapsed between these dates. These text boxes act like message boards to alert the user if the days elapsed have exceeded a certain amount of
by: John T. | last post by:
I have two date fields: InitialDate InspDate I've tried to use a Datediff expression within an IIF function (see below) in an unbound textbox to give me the amount of days elapsed between InitialDate and current date: - after 6 days have elapsed from the InitialDate and - only if the InspDate field is empty.
by: rdraider | last post by:
Hi, We have a lame app that uses 2 datetime(8) fields, 1 stores the date, the other the time. example query: select aud_dt, aud_tm from orders results: aud_dt aud_tm
by: Dinesh | last post by:
Hi Experts, I am working on SSRS 2005, and I am facing a problem in counting the no of days. My database has many fields but here I am using only two fields They are Placement_Date and Discharge_Date If child is not descharged then Discharge_Date field is empty. I am writing below query to count the number of days but is is not working it...
by: Wheeler2008 | last post by:
Hi All, I am currently running a query on a MS Access table, in which I want to be able to compare and total the difference in dates between records. I only have one date field within the table. The table contains the following fields: Acc. Number Transaction Date Detail Current Balance Everytime another transaction is completed, the...
by: geraldinegrieve | last post by:
I have a table in access that holds data on vehicle there are 3 fields holding dates on MOT, Tax and Insurance renewal I am looking for 3 different messages on opening if date of any is within next 2 weeks e.g. If DateDiff("d", Me.MOT_Renewal_Date, Now()) < 15 Then MsgBox "Vehicles Due MOT Renewal", , "Renewal Due" If DateDiff("d",...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
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. ...
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...
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...
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...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
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: 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...

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.