We are now using NHibernate to connect to different database base on where our software is installed. So I am porting many SQL Procedures to Oracle. SQL Server has a nice function called DateDiff which takes a date part, startdate and enddate. Date parts examples are day, week, month, year, etc. . . What is the Oracle equivalent? I...
I've got the following rough structure: Object -> Object Revisions -> Data The Data can be shared between several Objects. What I'm trying to do is clean out old Object Revisions. I want to keep the first, active, and a spread of revisions so that the last change for a time period is kept. The Data might be changed a lot over the cour...
What would be the best way to calculate someone's age in years, months, and days in TSQL (SQLServer 2000)? The datediff function doesn't handle year boundaries well, plus getting the months and days separate will be a bear. I know I can do it on the client side relatively easily, but I'd like to have it done in my sproc. ...
I'm building a quick csv from a mysql table with a query like: select DATE(date),count(date) from table group by DATE(date) order by date asc; and just dumping them to a file in perl over a: while(my($date,$sum) = $sth->fetchrow) { print CSV "$date,$sum\n" } There are date gaps in the data, though: | 2008-08-05 | 4 |...
Hi, What is the best way to count the time between two datetime values fetched from MySQL when I need to count only the time between hours 08:00:00-16:00:00. For example if I have values 2008-10-13 18:00:00 and 2008-10-14 10:00:00 the time difference should be 02:00:00. Can I do it with SQL or what is the best way to do it? I'm buildi...
I'm attempting to fulfill a rather difficult reporting request from a client, and I need to find away to get the difference between two DateTime columns in minutes. I've attempted to use trunc and round with various formats and can't seem to come up with a combination that makes sense. Is there an elegant way to do this? If not, is th...
Given a date range, I need to know how many Mondays (or Tuesdays, Wednesdays, etc) are in that range. I am currently working in C#. ...
I have a query in which I am pulling the runtime of an executable. The database contains its start time and its end time. I would like to get the total time for the run. So far I have: SELECT startTime, endTime, cast(datediff(hh,starttime,endtime) as varchar) +':' +cast(datediff(mi,starttime,endtime)-60*datediff(hh,starttime,endtime) as...
I have a need to create a gross requirements report that takes how much supply and demand of a item in inventory from a start date onwards and 'buckets' it into different weeks of the year so that material planners know when they will need a item and if they have enough stock in inventory at that time. As an example, today’s date (repor...
I need to calculate the DateDiff (hours) between two dates, but only during business-hours (8:30 - 16:00, no weekends). This result will then be put into the Reaction_Time column as per the example below. ID Date Reaction_Time Overdue 1 29.04.2003 15:00:00 1 30.04.2003 11:00:00 3...
Hi, I have to dates of the form Start Date: 2007-03-24 End Date: 2009-06-26 Now I need to find the difference between these two in the below form: 2 years, 3 months and 2 days Can someone please help me with this ...
This is my SQL statement that works using datediff.............. SELECT SUM(b134_nettpay) AS Total, b134_rmcid, b134_recdate FROM B134HREC WHERE datediff (dd, '2006-05-05', getdate()) > 90 GROUP BY b134_rmcid, b134_recdate ORDER BY b134_recdate DESC, b134_rmcid I need to Replace the Hardcoded date '2006-05-05' with MAX(b134_recdat...
hello, please can you help me to resolve my problem which is : i have field calculted in my query like (sysdate-adate) to have a difference between this two date i would like to have this field appear like a progressbar in dbadvgrid. but my problem is how to change the color of this progressbar when his value is >100 than...
INSERT INTO timecrunch.dbo.intervals (IntervalID, Duration) SELECT ixInterval, DATEDIFF(hour, dtStart, dtEnd) FROM fogbugz.dbo.TimeInterval WHERE dtEnd is not NULL IntervalID is an int, Duration is a float I am trying to put all the hourly durations into the Duration column... I assume that even if it is less than an hour it will use...
I am using DATEDIFF in an SQL statement. I am selecting it, and I need to use it in WHERE clause as well. This statement does not work... SELECT DATEDIFF(ss, BegTime, EndTime) AS InitialSave FROM MyTable WHERE InitialSave <= 10 It gives the message: Invalid column name "InitialSave" But this statement works fine... SELECT DATEDIF...
I need to calculate the difference between two timestamps in milliseconds. Unfortunatley, the DateDiff-function of VBA does not offer this precision. Are there any workarounds? Thanks in advance! ...
I use DATEDIFF function to filter records added this week only: DATEDIFF(week, DateCreated, GETDATE()) = 0 and I noticed what it's assumed what week starts on Sunday. But in my case I would prefer to set start of week on Monday. Is it possible somehow in T-SQL? Thanks! Update: Below is an example showing what DATEDIFF doesn't che...
Ok, got a tricky one here... If my data looks like this: Table1 ID Date_Created 1 1/1/2009 2 1/3/2009 3 1/5/2009 4 1/10/2009 5 1/15/2009 6 1/16/2009 How do I get the records that are 2 days apart from each other? My end result set should be rows 1-3, and 5-6. Thanks! ...
I need to determine if a set of dates contain two different weeks. For example: When returning a set of records from a database that contain a date, there needs to be something to distinguish between the different weeks. <cfloop query="datesExample"> <cfif DateDiff("d",DateFormat(lastDate),DateFormat(OriginalDate)) GTE 7> ...
How can I get the whole time like this datediff(time, logindate, logoutdate) I know this built-in function doesn't accept time argument but how can I get the whole time rather than minute, millisecond, second etc. ? logindate datetime2 logoutdate datetime2 I want something like 1:05:45 rather than portion of it. ...