Average Turnaround Time - Datediff() Help
Mar 14, 2007
Hi everyone, I need some help with creating a report that calculates the average turnaround time in days that it takes for units to return from trips destined to a location.
The database that I am working with lists a trip each time a unit is dispatched to a destination, and then another trip is created for the units return. In the example below I am trying to calculate the number of days that it takes for a unit to return to Vancouver by calculating the difference between the departure date from Vancouver and the arrival date back into Vancouver. I then need to calculate the average number of days that it takes for a unit to return from a trip. See sample data below.
UNIT -- TRIP -- START LOCATION --START DATE--FIN LOCATION--FIN DATE
================================================== =======
U12 ----001 --- VANCOUVER -------FEB 10 ------ ONTARIO ----- FEB 15
U10 ----002 --- VANCOUVER -------FEB 13 ------ ONTARIO ----- FEB 18
U12 ----003 --- ONTARIO ----------MARCH 13 --- VANCOUVER -- MARCH 18
U10 ----004 --- ONTARIO ----------MARCH 1 ---- VANCOUVER ---MARCH 6
Unit U12 took 36 days to return back to Vancouver
Unit U10 took 21 days to return back to Vancouver
Therefore based on the two trips it takes an average of aproximately 28.5 days for a unit to return from trips destined to Ontario.
View 1 Replies
ADVERTISEMENT
Jan 24, 2008
Hi all,
I have a question about the datediff function. Basically I have two tables. An Export Table with two columns DateSubmitted and PublishDate. So I need the difference between those dates without the weekends. To achive this I created a Calendar table with a column for the date and another one for day of the week to know if the date was monday or saturday etc.
Here is where I have the problem
My Sql query without the calendar table is like this:
Select Date_Submitted, Publish_Date,((DATEDIFF(dd, Date_Submitted, Publish_Date) + 1) )AS Turnaround
from Export
and works
but i need to join the calendar table and will need to aply only to the datediff section
Select Date_Submitted.Export, Publish_Date.Export, DayEmailed.Calendar, DayofWeek.Calendar,((DATEDIFF(dd, Date_Submitted, Publish_Date) + 1) WHERE DateSubmited = DateEmailed and DayofWeek != 'Saturday' OR Sunday??? )AS Turnaround
from Export, Calendar
In the red section is where I have the problem
Please if someone could help me and give some advice on how to execute this query
Thanks & Regards
Eileen
View 13 Replies
View Related
Feb 22, 2007
I made a code to in SQL reporting services for the reports of our company regarding the new applicants. Now I need to get the average datedifference in each phase from all applicants who entered Initial/Data Collection. Example, "Initial/Data collection"PHASE will have an average of 60 minutes to be finished, averaged from all 200 applicants, and "Screening"PHASE 240 minutes to be finished, averaged from 100 applicants.
All applicants must go through stage 1 before he can go to stage 2 and so on.
The date diff will be obtained from the "createdon" column subtracted to the last "modifiedon" column in each phase.
I need to insert that code to the existing code that I have if it is possible(which my boss prefer); or either if i can manipulate it in SQL reporting services in visual studio 2005.
Here is a sample output that I have.Each phase has a different color.
Here is the code that i used
SELECT subject, regardingobjectidname, createdon, modifiedon, stat, phase, initial,
(CASEphaseWHEN 'Initial/Data Collection' THEN 1
WHEN 'Screening' THEN 2
WHEN 'Assesment and Selection' THEN 3
WHEN 'Placement' THEN 4
END) AS Phasesort
FROM(
SELECT subject, regardingobjectidname, createdon, modifiedon, stat, initial,
(CASEstatWHEN 'Application Recieved' THEN 'Initial/Data Collection'
WHEN 'For Screening' THEN 'Screening'
WHEN 'Shortlisted' THEN 'Screening'
WHEN 'For Assesment' THEN 'Assesment and Selection'
WHEN 'For Assesment' THEN 'Assesment and Selection'
WHEN 'Passed Initial Interview' THEN 'Assesment and Selection'
WHEN 'Passed Profiles Assesment' THEN 'Assesment and Selection'
WHEN 'Passed Technical Exam' THEN 'Assesment and Selection'
WHEN 'For Placement' THEN 'Placement'
END) AS phase
FROM(
SELECT subject, regardingobjectidname, createdon, modifiedon, initial,
(CASEinitialWHEN '1 stage' THEN 'Application Recieved'
WHEN '2 stage' THEN 'Application Recieved'
WHEN '3 stage' THEN 'For Screening'
WHEN '4 stage' THEN 'Shortlisted'
WHEN '5 stage' THEN 'For Assesment'
WHEN '6 stage' THEN 'Passed Initial Interview'
WHEN '7 stage' THEN 'Passed Profiles Assesment'
WHEN '8 stage' THEN 'Passed Technical Exam'
WHEN '9 stage' THEN 'For Placement'
WHEN '10 stage' THEN 'For Placement'
END) AS stat
FROM(
SELECT subject, regardingobjectidname, createdon, modifiedon,
(CASEsubjectWHEN N'application received' THEN '1 stage'
WHEN 'process application' THEN '2 stage'
WHEN 'screen application' THEN '3 stage'
WHEN 'Phone interview' THEN '4 stage'
WHEN N'initial interview' THEN '5 stage'
WHEN 'profiles assessment' THEN '6 stage'
WHEN 'technical exam and interview' THEN '7 stage'
WHEN 'background and reference check' THEN '8 stage'
WHEN 'Job Offer' THEN '9 stage'
WHEN 'Contract Signing' THEN '10 stage'
END) AS initial
FROM(SELECT subject, regardingobjectidname, createdon, modifiedon
FROMFilteredTask
WHERE (subject IN ('application received', 'process application',
'screen application', 'initial interview', 'profiles assessment',
'technical exam and interview', 'background and reference check', 'job offer',
'contract signing'))
UNION ALL
SELECT subject, regardingobjectidname, createdon, modifiedon
FROMFilteredPhoneCall
WHEREsubject = 'phone interview'
) AS Orion
) AS Initials
) AS Phases
) AS Stats
ORDER BY regardingobjectidname, initial
I appreciate your help.
Thanks a lot.
__________________________________________________
Your future is made by the things you are presently doing.
Andrew
View 9 Replies
View Related
Apr 2, 2008
My table has two datetime columns (TheatreArivalDate and TheatreDepartDate). Can I get an average value of the time differences of them? Thanks
View 1 Replies
View Related
Apr 30, 2015
I have the following code which returns day of week name, position and date and time of surgeries.
SELECT
DATENAME(WEEKDAY,OPE_START_TIME) AS DOW,
[OPE_ORDER_IN_SESS_ASC] AS POSITION,
OPE_START_TIME AS [TIME]
FROM table1The data looks like thisHow can i group this data so I get the average start time by day of week and position?
View 3 Replies
View Related
Oct 3, 2014
I keep getting an alert from a Third-Party Tool stating my server is reporting failed components with the following message
Components Reporting Problems:
Average Lock Wait Time(ms)(Critical)
When I look at sp_who2 or sp_lock I don't see anything blocking processes or anything else out of the ordinary.
View 2 Replies
View Related
Nov 19, 2006
Hello~,
The table has columns like this.
________________________________
time smalldatetime
value1 int
value2 int
----------------------------------------------------------
for example,
....
'2006-11-16 12:00:00',100,200
'2006-11-16 13:00:00',110,210
'2006-11-16 14:00:00',120,220
....
The record is inserted at every hour.
I want get daily,monthly,yearly average and display the result ordered by time.
View 1 Replies
View Related
Jan 9, 2008
I am trying to figure out how to calculate the average time between phone calls for a user. The initial requirement is to calcualte this on all calls for an entire month but I would guess that would lead to other periods as well, such as daily, weekly, etc. One hurdle is what to do when going from one day to the next. I could possibly just week out any times between calls that are greater than a certain amount of time to address that.
Any way, here is a small sample of what I'll be dealing with. Any ideas on how to approach this or get it to work would be greatly appreciated.
Code Block
CREATE TABLE #avetime (origdate datetime, duration_seconds int, duration_minutes decimal(9,2), phoneuser varchar(20), calltype varchar(15))
INSERT into #avetime VALUES ('Jan 7 2008 9:19AM', 21, 0.4, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:19AM', 48, 0.8, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:33AM', 81, 1.4, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:35AM', 87, 1.5, 'Coleman', 'Internal')
INSERT into #avetime VALUES ('Jan 7 2008 9:37AM', 27, 0.5, 'Coleman', 'Internal')
INSERT into #avetime VALUES ('Jan 7 2008 9:42AM', 13, 0.2, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 9:43AM', 84, 1.6, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 12:00PM', 914, 15.2, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 7 2008 2:24PM', 3, 0.1, 'Coleman', 'Internal')
INSERT into #avetime VALUES ('Jan 8 2008 10:13AM', 21, 0.4, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 8 2008 10:33AM', 482, 8.0, 'Coleman', 'Long Distance')
INSERT into #avetime VALUES ('Jan 8 2008 11:49AM', 56, 0.9, 'Coleman', 'Long Distance')
View 2 Replies
View Related
May 22, 2008
I am running into a barrier and need to understand the average length of time that a fully optimized data cube should take to process.
We are currently running an average of 15 to 20 minutes per cube, with average of 2000 aggregations, 25% performance increase, and approximately 2 million rows, with around 40 dimensions and 30 measures.
I personally think this is a pretty good time to process. However, I am being challenged to reduce this time frame. In theroy I can't possibly see it getting below where we currently are. SO I am reaching out to the group of guru's...
What is your average length of time to process your Data Cubes? Please respond to me at ken.kolk@medcor.com I would greatly appreciate it and need the averages from the field.
View 1 Replies
View Related
Oct 7, 2015
I have a dataset as such:
Student TestTypeDate TestCnt
111-22-1111English2015-09-01 10:00:00 1
111-22-1111Math2015-09-02 11:00:00 2
111-22-1111Geo2015-09-03 12:00:00 3
222-11-2222English2015-09-01 10:00:00 1
333-22-1111English2015-09-01 10:00:00 1
[Code] ...
So some have just 1 test and some have multiple. I have a count for each. What I need to do is use that count and get an average time between each test per student.
View 9 Replies
View Related
Jun 21, 2015
Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:
Avg_AMP:=AVERAGEX('Fct Sales';'Fct Sales'[_AMP]/100)
Avg_AMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_AMPdollar]/100)
Avg_FMP:=AVERAGEX('Fct Sales';'Fct Sales'[_FMP]/100)
Avg_FMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_FMPdollar]/100)
The results were, respectively: 701,68; 2120,60...; -669,441; and finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:
test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17
test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...
So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.
I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.
View 2 Replies
View Related
Feb 15, 2008
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged...
I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25.
The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement.
?confused?
Here's the original query.
accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '
SELECT name, country_cd from weather_station where platformId=&&platformId;
SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS
FROM daily
WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime
GROUP BY to_char(datetime,'MM')
ORDER BY to_char(datetime,'MM');
with a result of:
NAME_________________CO
-------------------- --
OFFUTT AFB___________US
MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS
-- ---------- ---------- ---------- -----------
01_________21______-5.31________-30__________60
02_________26______-2.19______-28.3__________61
03_______31.1_______3.61______-26.1__________60
04_______35.6______11.07______-12.2__________60
05_______37.2_______17.2_______-3.3__________60
06_______41.1______22.44__________5__________60
07_______43.3______24.92________7.2__________60
08_______40.6______23.71________5.6__________60
09_________40______18.84_______-2.2__________59
10_______34.4_______12.5_______-8.9__________59
11_________29_______4.13______-23.9__________60
12_________21______-2.52______-28.3__________60
View 4 Replies
View Related
Aug 7, 2007
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
View 5 Replies
View Related
Dec 5, 2005
If I wanted to get everyone DOB who is over 18 how would I do that? I am currently trying something like this, but no luck...
Declare @todays_date datetime
Select from person
CASE
WHEN dateadd(year, datediff (year, Date_Of_Birth, @Todays_Date), Date_Of_Birth) > @Todays_Date -- Date of Birth check
THEN datediff (year, Date_Of_Birth, @Todays_Date) - 1
ELSE datediff (year, Date_Of_Birth, @Todays_Date)
END
>= 18
View 3 Replies
View Related
Jul 27, 2004
I'm using a datediff(mi, start, stop) to get the duration of an operation. i want it displayed in HH:MM format. can anyone help me w/ a way to do that????
tia,
e3witt
View 4 Replies
View Related
Mar 9, 2004
Hello,
My string is:
SELECT weight FROM progress WHERE dateInput = (SELECT MAX(dateInput) FROM progress) AND memberID = 1
The problem is that the MAX dateInput doesn't belongs to memberID 1. It belongs to memberID 2. What I want is that I wanna choose the MAX Date of memberID 1. I thought of maybe using datadiff function. But I don't know how to make the datediff statement. Maybe I can write the datediff statement whereby the least datediff between the dateInput and getdate() will be the row I want. I really appreciate the person that helps me this problem... Thanks 1st of all!
View 2 Replies
View Related
Apr 15, 2004
I am trying to select records from whatever the current date would be and 12 months before whatever the current date is. How would I go about doing this. The table that I am trying to do this with has a year column and a month column.
I was playing with the date diff function, but I can only get dates from the specified date range. I need it to be where if I run it tomorrow, it will get that day and everything within the last 12 months.
View 7 Replies
View Related
Apr 25, 2008
I need to get a listing of all persons who are atleast 18 years of age. A date of birth field in the database is in this format:
4/25/2008 12:00:00 AM
My solution would be where the difference between the current date and the dob is >= 18.
I tried...
select * from table where datediff(yy,dob,getdate)) >= 18.
But this only seems to subtract the years and ignore the days/months, which I need. Could anyone provide the syntax I need?
Help is appreciated. Thanks.
View 3 Replies
View Related
Jun 24, 2008
Hi there i am using the datediff funtion but it does not seem to be inclusive of the two dates eg
SELECT DATEDIFF(day, s_Date, e_date) AS NumberOfDays,*
FROM weekendtest
i know i could do
SELECT DATEDIFF(day, s_Date, e_date)+1 AS NumberOfDays,*
FROM weekendtest
but i was just wondering if there some other function i should use that would be more appropriate
View 3 Replies
View Related
Oct 21, 2004
Hi
I've a problem!!!
i want to use datediff thats no problem. But the first parameter has to
be parameterised. What datatype do i declare it as. i declare it as varchar
it returns me an error : Invalid parameter 1 specified for datediff.
i'm doing this in a stored procedure.
Is there a way out or do i've to use the good old 'IF' or 'Case' ?
regards
pradeep
View 1 Replies
View Related
Oct 19, 2006
Hi,
I have a Table Where I have Two Date columns and I want to find the Differnce in the 3rd column.ie DateReceived - AsOnDate = NoofDays.I tried this code
SELECT SRNO,CONVERT (VARCHAR,AsonDate,102)as ASONDATE,CONVERT(VARCHAR, DateReceived, 102) AS DateReceived,CONVERT(VARCHAR, DateAcknowledged, 102) AS NoofDays,
sum(CASE WHEN DATEDIFF(DAY, DATERECEIVED,ASONDATE, GETDATE())
FROM Details
Plz help me...
Thanks
View 7 Replies
View Related
Jan 27, 2008
SELECT DATEDIFF(mm,0,'01/25/2008')
Returns 1296 what does it represent...
Thanks
View 2 Replies
View Related
Feb 29, 2008
Could somebody please explain to me why I get the following results:
select datepart (ww, '10/02/08') --returns 6
select datepart (ww, '18/02/08') --returns 8
select datediff (ww, '10/02/08', '18/02/08') -- returns 1
I was expecting the third query to return 2.
Thanks.
View 11 Replies
View Related
Nov 23, 2006
Hi, I am facing problem rite now.. I want to calculate the date different minutes between 23:00:00 and 01:00:00.
My code :
datediff(Minute,'01:00:00','23:00:00')
The result is 1320 minutes. (22 hours)... But, the result that I want is 120 minutes (2 hours)....
Can anybody help ???
Thanks in advance...
View 2 Replies
View Related
Jan 12, 2007
I'm trying to use the same select statement I used in classic SQL, but it isn't working. I get a "The server tag is not well formed." error.
I'm trying to return all the records where the date in a particular field are over 60 days old. My select statement is:
"SELECT [ReNewAd], [Male or Female], [File2], [PHOTO], [First Name], [Last Name], [City], [State], [NewAd] FROM Members Where ('NewAd' IS NOT NULL AND (DateDiff('d', [NewAd], '" & Now() & "') <= 60)) order by [NewAd] Desc"
I've been looking through the datediff posts, but I'm not making any sense of what I'm reading. Can anyone see what I'm doing wrong?
Thanks, Diane
View 5 Replies
View Related
Jun 14, 2007
I'm trying to create a stored procedure that will return a set of data. An input parameter (if specified) will determine what topics will be show based on the date the item "datetime". ALTER PROCEDURE [dbo].[discussions_GetTopics]@board_id as int,@PageNumber INT,@TopicsDays INT = NULL,@TopicsPerPage INT,@HowManyProducts INT OUTPUTAS-- Declare the table that will store all the topics for the given board_idDECLARE @Topics TABLE(RowNumber INT,topic_id INT,topic_title VARCHAR(50),topic_replies INT,topic_views INT,topic_type INT,post_id int,post_time DATETIME,Topic_Author_UserName nvarchar(256),Topic_Author_ID uniqueidentifier,Post_Author_Username nvarchar(256),Post_Author_ID uniqueidentifier)-- populate the tableINSERT INTO @TopicsSELECT ROW_NUMBER() OVER (ORDER BY discussions_topics.topic_id), discussions_Topics.topic_id, discussions_Topics.topic_title, discussions_Topics.topic_replies, discussions_Topics.topic_views, discussions_Topics.topic_type, discussions_Posts.post_id, discussions_Posts.post_time, user_1.UserName AS Topic_Author_Username, user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_IDFROM discussions_Topics INNER JOIN discussions_Posts ON discussions_Posts.post_id = discussions_Topics.topic_last_post_id INNER JOIN aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_idWHERE (discussions_Topics.board_id = @board_id ANDCASE @TopicsDays WHEN '1' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 1 WHEN '2' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 7 WHEN '3' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 14 WHEN '4' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 30 WHEN '5' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 90 WHEN '6' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 180 WHEN '7' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 365END)-- return the total number of products using an OUTPUT variableSELECT @HowManyProducts = COUNT(topic_id) FROM @Topics-- Return the specified page of topicsSELECT * from @TopicsWHERE RowNumber > (@PageNumber - 1) * @TopicsPerPageAND RowNumber <= @PageNumber * @TopicsPerPage I need help where the "CASE @TopicsDays
WHEN '1' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 1
WHEN '2' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 7
WHEN '3' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 14
WHEN '4' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 30
WHEN '5' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 90
WHEN '6' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 180
WHEN '7' THEN DATEDIFF(day, discussions_Topics.topic_time, getdate()) <= 365
END" Is... Im not doing it right. Can someone help me?
View 1 Replies
View Related
Sep 28, 2007
hello,
i have a Pictures table: PictureID, Name, Description, DateAdded (GETDATE() when insert), IsActive...
i need to make some stored procedures to show me the pictures added in last 24hours, in last 3 days, last 2 weeks and so on
the pictures added in database are active (available to be seen by users) only 1yaer after the date added
I tryied to make a stored procedure (in fact i maked a lots of them, for 1day 3 days 1 week 1 month), but i have a problem with that DateDiff and DateAdd
Here is what i tryied CREATE PROCEDURE LastAdded_2monthsAgo
AS
SELECT Pictures.ProductID, Pictures.Name, Pictures.Description, Pictures.DateAdded
FROM Pictures
WHERE (DATEDIFF(month, Pictures.DateAdded, GETDATE()) >= 0) AND (DATEDIFF(month, Pictures.DateAdded, GETDATE()) <= 2)
ORDER BY DateAdded DescI have a feeling that is wrong, please make your own version and show me what i should write...I don't know what should be first the today date or the DateAdded...i need to select the last added products from a specific interval of time...Should i do something with that "1 year available" like WHERE (DATEDIFF(month, GETDATE(), DATEADD(year, 1, Products.DateAdded)) >= 0) AND (DATEDIFF(month, GETDATE(), DATEADD(year, 1, Products.DateAdded)) <= 2) I am sure is a stupid thig up there...if you can, make your own version how you would do it and show me..please help me
View 5 Replies
View Related
Nov 12, 2007
Hi,I'm using the datediff function to display the ages of the users in my database. However the age rounds up once they are 35.5 etc...I could create another function which works similar to the DateDiff function, but use math.floor to always round down, but I need to use this function in a SQL statement WHERE clause. Is there any way around this?Thanks,Curt.
View 3 Replies
View Related
Apr 25, 2008
This is a sql question, so I'm hoping someone can give me some direction.
I need to query a table and return all rows where the individual is >= 18 years of age. It should involve simply subtracting the date of birth from the current date. I thought the answer would be so simple:
select * from table where datediff(yy,dob,getdate()) >= 18
Unfortunately, the results are inconsistent. The DATEDIFF function, it appears, will not show correct results down to the month/day level. I tried posting this on a sql server forum, but the recommendations I received involved coding some gigantic function. It seems that there ought to be a simple 'WHERE' clause which could solve this.
If someone knows of a silver bullet, I would surely appreciate your advice.
Thank you.
View 5 Replies
View Related
May 6, 2008
I'm trying to include a DATEDIFF compare in my Where clause, but it seems to ignore the comparison I'm trying to invoke, and just returns all rows. When I display the result of the DATEDIFF command, it looks good, but I can't seem to have it incorporated in my Where clause.
The following is my code -
select [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time]
from pkgactions
WHERE (8 > DateDiff(year, [End Exec Date], GetDate() ))
order by [pkg id]
Thanks
View 4 Replies
View Related
Aug 10, 2004
Hi,
I have two fields in one of my database table. They are date fields, one for the start and the other for the end of the "problem solving". What I want to do is to show the concrete elapsed time between the two.
My first problem is that I'm not able to show hour AND minutes. I wrote this for instance:
SELECT DATEDIFF(hour, fld_date_debut, fld_date_fin) As elapsed_time
I tried HH:mm instead of hour, but it is not working.
My second problem is that I can have more than one start and end hour for the same "problem solving". In the database it's like:
start hour: 10:00 End hour: 11:00 Number of the problem: 1
start hour: 13:00 End hour: 16:00 Number of the problem: 1
So I would like to add these to my elapsed time. I want (11:00 - 10:00) + (16:00 - 13:00)...but how can i do this in my SQL query ?
Thanks.
View 1 Replies
View Related
Dec 8, 2004
Hello everyone.
Im currently using the DateDiff function to filter my DateTime columns but am finding it somewhat troublesome. Currently I am having to write the same select statement 3 times if I want to filter by month, year or all (ignoring dates).
To find @PurchaseTotal for the year, I have to write the following:
SELECT
@PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses
WHERE
DateDiff(yyyy, DateOf, @IntervalDate) = @Interval
To find @PurchaseTotal for a month, I have to write the following:
SELECT
@PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses
WHERE
DateDiff(mm, DateOf, @IntervalDate) = @Interval
To find @PurchaseTotal for all the records, I have to write the following:
SELECT
@PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses
I've tried the following code but I get an error.
DateDiff(@DateParameter, DateOf, @IntervalDate) = @Interval
Error says something like "incorrect parameter 1 for DateDiff."
It seems you have to write a different select statement for month, day and year. Also If you want a total from all the records you have to write yet another select statement.
Does anyone know of a DateTime function that allows parameters to specify for month, day and Year? Also does anyone know of a DateTime function that works like the COALESCE function to where you can send it a NULL value and give you all the records?
Thank you ahead for any direction you can give.
Alec
View 1 Replies
View Related
Feb 14, 2006
I just discover the result for my query that had use the function dateDiff seems to be in-correct, no matter what's the date, the dateDiff always return a zero. Am I using it in-correctly?
select DateDiff(mm,11/1/2004, 12/31/2005)
SELECT component_id, component_description,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 2 THEN component_qty ELSE 0 END) AS mm1,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 1 THEN component_qty ELSE 0 END) AS mm2,SUM(CASE DateDiff(mm,date_complete,'12/31/2004')WHEN 0 THEN component_qty ELSE 0 END) AS mm3,sum(component_qty) as totalFROM view_jobcomponent WHERE date_complete between '10/1/2004' and '12/31/2004' GROUP BY component_id, component_descriptionorder by component_id, component_description
View 1 Replies
View Related
