A More Precise DateDiff Function

Feb 7, 2007

This will give you the time information about how apart two dates are.CREATE FUNCTION dbo.fnTimeApart
(
@FromTime DATETIME,
@ToTime DATETIME
)
RETURNS @Time TABLE ([year] SMALLINT, [month] TINYINT, [day] TINYINT, [hour] TINYINT, [minute] TINYINT, [second] TINYINT, [millisecond] SMALLINT)
AS
BEGIN
DECLARE@Temp DATETIME,
@Mts INT,
@year SMALLINT,
@month TINYINT,
@day TINYINT,
@hour TINYINT,
@minute TINYINT,
@second TINYINT,
@millisecond SMALLINT

IF @FromTime > @ToTime
SELECT@Temp = @FromTime,
@FromTime = @ToTime,
@ToTime = @Temp

SET@Mts =CASE
WHEN DATEPART(day, @FromTime) <= DATEPART(day, @ToTime) THEN 0
ELSE -1
END + DATEDIFF(month, @FromTime, @ToTime)

SELECT@year = @Mts / 12,
@month = @Mts % 12,
@Temp = DATEADD(month, @Mts, @FromTime)

SELECT@day = datediff(hour, @Temp, @ToTime) / 24,
@Temp = DATEADD(day, @day, @Temp)

SELECT@hour = DATEDIFF(minute, @Temp, @ToTime) / 60,
@Temp = DATEADD(hour, @hour, @Temp)

SELECT@minute = DATEDIFF(second, @Temp, @ToTime) / 60,
@Temp = DATEADD(minute, @minute, @Temp)

SELECT@second = DATEDIFF(millisecond, @Temp, @ToTime) / 1000,
@Temp = DATEADD(second, @second, @Temp),
@millisecond = DATEDIFF(millisecond, @Temp, @ToTime)

INSERT@Time (year, month, day, hour, minute, second, millisecond)
SELECT@year,
@month,
@day,
@hour,
@minute,
@second,
@millisecond

RETURN
ENDAnd to test the functionSELECTd.FromDate,
d.ToDate,
x.*
FROM(
SELECT'19690906' AS FromDate, '19760608' AS ToDate UNION ALL
SELECT'19991231', '20000101' UNION ALL
SELECT'20070207', '20070208' UNION ALL
SELECT'20000131', '20000228' UNION ALL
SELECT'20070202', '20070201' UNION ALL
SELECT'20070207', '20070307' UNION ALL
SELECT'20000131', '20000301' UNION ALL
SELECT'20011231 15:24:13.080', '20020101 17:15:56.343' UNION ALL
SELECT'20011231 17:15:56.343', '20020101 15:24:13.080' UNION ALL
SELECT'20020101 15:24:13.080', '20011231 17:15:56.343' UNION ALL
SELECT'20000131', '20000229'
) AS d
CROSS APPLYdbo.fnTimeApart(d.FromDate, d.ToDate) AS x
ORDER BYd.FromDate,
d.ToDateAnd the output isFromDateToDateyearmonthdayhourminutesecondmillisecond
19690906197606086920000
19991231200001010010000
200001312000022800280000
200001312000022900290000
20000131200003010110000
20011231 15:24:13.08020020101 17:15:56.34300115143263
20011231 17:15:56.34320020101 15:24:13.08000022816736
20020101 15:24:13.08020011231 17:15:56.34300022816736
20070202200702010010000
20070207200702080010000
20070207200703070100000
Peter Larsson
Helsingborg, Sweden

View 14 Replies


ADVERTISEMENT

HELP Me In This Datediff() Function....

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

Using DateDiff Function For Age

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

Datediff Function

Apr 25, 2007

hi
select datediff(m,'3/5/2003',getdate as experience

output is
Experience
----------
49

select (datediff(m,'3/5/2003',getdate()))/12 as experience

output is
Experience
----------
4

but 49/12 is 4.08333=4.1

how i can get this
Actually my task is to output as years.months

Thanks in advance


Malathi Rao

View 7 Replies View Related

Please Help With Datediff Function

Oct 5, 2006

I am trying to break up the age into column from a dob field for a cross tab report. I can query the datediff with an alias but can not individually change the columns I need like a virtual or temp column, but can't figure out how to accomplish it.

use 'Database'

Select datediff(Year, dob, getdate()) As "Under 22", datediff(Year, dob, getdate()) As "22-45",

datediff(Year, dob, getdate()) As "46-65", datediff(Year, dob, getdate()) As "Over 65"

from 'Table'

WHERE (DATEDIFF(yy, dob, GETDATE()) < 22 OR

DATEDIFF(yy, dob, GETDATE()) BETWEEN 22 AND 45 OR

DATEDIFF(yy, dob, GETDATE()) BETWEEN 46 AND 65 OR

DATEDIFF(yy, dob, GETDATE()) > 66) AND (status = 'Current' OR

status = 'Previous' OR

status = 'non-billable')

View 5 Replies View Related

Datediff Function With Hrs And Minutes

Feb 9, 2007

I have two date columns one is sent_date and other is approved_date
my requirment is to find the difference between the two dates
which can be minutes/hrs/days.
using datediff function iam able to get it in minusts or hrs but my
output should be of the format hh:mm
23:10 (ie 23 hrs and 10 min) or say
48:00 (for 2 days)

sample date
sent_date approved_date
2/28/06 11:06 2/28/06 11:39
2/2/06 17:42 2/2/06 18:03
2/8/06 16:55 2/8/06 17:38
1/27/06 17:00 1/27/06 17:54
1/26/06 12:08 1/26/06 12:09
2/28/06 15:46 2/28/06 16:26
1/23/06 10:01 1/23/06 10:43
1/26/06 13:46 1/26/06 13:59
1/13/06 13:51 1/13/06 14:47

View 4 Replies View Related

Stuggling With Datediff Function

Nov 2, 2007

recently i created a system using php and mysql to record faults that the ict technicians could get reported faults out of. so far it does everything i want but some senior members of staff now want the front end to give a report of status, one of the things they wish to see is how many jobs have been completed this week month and year

within my tables is a field date. naturally it has the date submited contained within it. the code i have been using to try and get an output is

mysql_query("SELECT date, datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");

the complete feild is another contained within the datebase. i have also tried SELECT * datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");

unfortunatly my limited knowledge of php and sql is holding me back on this. any help guys would be appriacted

View 2 Replies View Related

Query On Datediff Function

May 18, 2007

Hi,



I am trying to use DataDiff function and I have used the following queries:



1.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.320') as test

Expected Result: 0 milliseconds

Actual Result: 0 milliseconds



2.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.321') as test

Expected Result: 1 milliseconds

Actual Result: 0 milliseconds



3.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.322') as test

Expected Result: 2 milliseconds

Actual Result: 3 milliseconds



4.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.323') as test

Expected Result: 3 milliseconds

Actual Result: 3 milliseconds



5.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.324') as test

Expected Result: 4 milliseconds

Actual Result: 3 milliseconds



6.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.325') as test

Expected Result: 5 milliseconds

Actual Result: 6 milliseconds



7.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.326') as test

Expected Result: 6 milliseconds

Actual Result: 6 milliseconds



8.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.327') as test

Expected Result: 7 milliseconds

Actual Result: 6 milliseconds



9.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.328') as test

Expected Result: 8 milliseconds

Actual Result: 6 milliseconds



10.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.329') as test

Expected Result: 9 milliseconds

Actual Result: 10 milliseconds



Does any one know, why datediff does not return the Expected Result? There does not seem to be any consistency.



Thanks,

Tim

View 1 Replies View Related

Datediff With Today Function

Jan 25, 2008

I want to create and expression that basically says if a datetime value is today minus 2 days then do another formula.

Right now it looks like this...




Code Snippet
=iif(Fields!Channel_Id.Value="SFE1" and Fields!Report_Date.Value=Today-2,0,(Fields!Total_Apps.Value-Fields!total_apps_null_status.Value))





Basically, it spits an error saying [BC30452] Operator '-' is not defined for types 'Date' and 'Integer'.

Should this be done with DateDiff? if so, how can you specify two days ago?

Thanks much

C-

View 5 Replies View Related

Use DateDiff Function Within Select Statement

Feb 3, 2015

I'm trying to use the DateDiff function within my select statement, but I'd like to add the parameter of greater than 30 days. This will have the query only return records where my bill stop date is greater than 30 days from the completion date. I currently have the datediff function within my select statement as

DATEDIFF (d,A.StopBillDate, a.CompletionDate) as [DIFFERENCE]

I would prefer to keep the datediff function within the select statement so as to have difference in days appear as a column within my output.I have been unable to add the parameter of > 30 days to the query without getting an error.

View 2 Replies View Related

Help Needed For Datediff Function For SQL Query

Apr 9, 2007

Hi Experts,I am working on SSRS 2005, and I am facing a problem in counting theno of days.My database has many fields but here I am using only two fieldsThey are Placement_Date and Discharge_DateIf child is not descharged then Discharge_Date field is empty.I am writing below query to count the number of days but is is notworking it is showing the error"The conversion of a char data type to a datetime data type resultedin an out-of-range datetime value."select casewhen convert(datetime,Discharge_Date,103) = '' thendatediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())elsedatediff(day,CONVERT(datetime,Placement_Date,103),CONVERT(datetime,Discharge_Date,103))end NoOfDaysfrom Placement_DetailsSo please tell me where I am wrong?Any help will be appriciated.RegardsDinesh

View 3 Replies View Related

Using Datediff Function To Return 1st Of Month. Different Results With T-SQL And SSIS

May 15, 2007

Hi



I regularly use the T-SQL date functions to return the 1st of a particualr month.



e.g.



SELECT DATEADD(m,DATEDIFF(m,0,getdate()),0)



returns 2007-05-01 00:00:00.000



i.e the first of the current month at midnight.

However, when I try to use a similar expression as a derived column in SSIS it returns a completely different date.



DATEADD("month",DATEDIFF("month",(DT_DATE)0,GETDATE()),(DT_DATE)0)



returns 30/05/2007 00:00:00





Any ideas why and how I can obtain the first of a particualr month using SSIS derived column?





View 3 Replies View Related

Transact SQL :: Error - The Datediff Function Resulted In Overflow

Sep 12, 2015

When running a query such as this on a database view:

select count(*) from WWALMDB.dbo.v_AlarmEventHistory2

SQL throws the following error:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Oddly on a Table the count(*) function works.

Is there a limit on views or the count(*) function that I am not aware of?

View 3 Replies View Related

DATEDIFF Function Dosen't Return Anything If Used Inside ASPX Page!!

Feb 12, 2008

Dear all, I have the Following Query:SELECT DATEDIFF(day, GETDATE(), RECENT_RESERVATION)AS Expr1,RECENT_RESERVATION FROM EMP WHERE SUN=empName;when i run it inside the query analyzer, it returns two columns. but if run it inside The ASPX page it retuns only one column wich is  RECENT_RESERVATION date.Note: i am using one methode that takes care of reading from SQL and assigning the result into an array, it works fine everywhere, but with this query it dosen't work. Any suggestions??   

View 6 Replies View Related

Fetch Data Of User Who Have Created Profile Within 7 Days - DateDiff Function

May 2, 2015

I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .

I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.

select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC

System.DateTime.Now is a function for getting current date time in C#

The query is neither giving error nor giving desired result.

View 4 Replies View Related

Data Warehousing :: DateDiff Function To Return Positive Value Irrespective Of Values Passed

Aug 7, 2015

I have a requirement to use DateDiff(Months,DateTime1,DateTime2) and this must return positive integer values.

Currently negative numbers are being returned because DateTime1 < DateTime2  or DateTime1 > DateTime2 .

The DateTime1 and  DateTime2  values are dynamic and either of them can be bigger than the other.

Any query solution so that always positive value is returned.

View 3 Replies View Related

Datediff

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

DateDiff

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

Datediff Or MAX?

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

DateDiff

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

DATEDIFF?

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

Datediff

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

Datediff

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

DateDiff In Sql?

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

DATEDIFF

Jan 27, 2008

SELECT DATEDIFF(mm,0,'01/25/2008')
Returns 1296 what does it represent...

Thanks

View 2 Replies View Related

DATEDIFF

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

Datediff Problem

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

Using CASE And DATEDIFF

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

DateDiff / DateAdd - Please Help Me:(

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

Sql Server DATEDIFF

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

DATEDIFF In A WHERE Clause

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

DATEDIFF Possibilities

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

DateTime Using DateDiff

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







Copyrights 2005-15 www.BigResource.com, All rights reserved