Showing Difference Between Dates

Feb 14, 2008

Its early in the morning and I am struggling with an easy one.

I have a calculated field in a report which displays the difference between 2 dates. The value that is returned is a number which I can then format to display difference like so 'Days.hh:mms'

I need the diffence between the dates to be expressed in hours, minutes and seconds only. eg '36:45:12'

It would also be usefull if I could build a parameter in to the query which would allow me to discount days selected by the user (Using multi select drop down in day format i.e 'Monday', 'Tuesday', etc)

Any Idea's?

View 7 Replies


ADVERTISEMENT

Table Diff Utility - Showing Only Difference Of Records

Dec 10, 2007

Hi Madhu,


my table does not have primary key so i created a seperate index on each of the table.

I used the recommended tablediff utility and it works successfully. But its only show the difference of records in each table and does not copy rows from source to destination and destination to source table. I was expecting database1.dbo.table1 contains same records as in database1.dbo.table2.


C:Program FilesMicrosoft SQL Server90COM>tablediff /sourceserver kashif-pcs
qlexpress /sourcedatabase AB /sourcetable table1 /destinationserver kashif-pcsq
lexpress /destinationdatabase CD /destinationtable table2
Microsoft (R) SQL Server Replication Diff Tool
Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.

User-specified agent parameter values:
/sourceserver kashif-pcsqlexpress
/sourcedatabase AB
/sourcetable table1
/destinationserver kashif-pcsqlexpress
/destinationdatabase CD
/destinationtable table2

Table [AB].[dbo].[table1] on kashif-pcsqlexpress and Table [CD].[dbo].[table2]
on kashif-pcsqlexpress have 5 differences.
Err Sno
Src. Only 101
Src. Only 102
Dest. Only 103
Dest. Only 104
Dest. Only 105
The requested operation took 0.466767 seconds.



Can you write a short script for my problem, just like comparison of database1.dbo.table1 compares in database2.dbo.table2 and which ever records not present it should copy those and vice-versa.

It means Database1.dbo.table1 contains 5 records
Database2.dbo.table2 contains 5 records




Regards
Kashif Chotu

View 3 Replies View Related

Getting The Difference Between Two Dates

May 17, 2005

Hey people,

i was wondering if it was possible to get the difference bettween the current date/time and a date feild and store it in a feild in the same table. id like to do this to prevent constant php update querys.

cheers

mish

View 1 Replies View Related

Difference In The Dates...

Oct 23, 2007

I have 2 datatime columns....i want to get the difference in the dates...

EXample

Startdate Enddate Difference

10/10/2007 20/10/2007
11/10/2007 21/10/2007
12/10/2007 22/10/2007
13/10/2007 23/10/2007
14/10/2007 25/10/2007


I want the difference to be in the Difference column...

Kindly help me...thanks in advance...

pavan

View 3 Replies View Related

How To Get Difference Between Two Dates

Mar 15, 2007



By using DateDiff we can do.Could you please send me the expression

Thanks

View 5 Replies View Related

Time Difference Between Two Dates

Apr 10, 2008

advance thanks to all...can anybody helpme soon...........
i am using sql server 2005........i want to find out time difference between two dates in HH,MM,SS format
eg i want the time difference of these two dates
02/Nov/06 9:14:21 AM
19/Apr/07 11:52:31 AM 
now i am using this calculation in my procedure is as follows...but i think this is wrong...if we will seperatly calculate like this wrong i am getting
set @totaltravelHr=datediff(HH,@datediff1,@datediff2)
set @totaltravelMI=datediff(MI,@datediff1,@datediff2)
set @totaltravelSE=datediff(SS,@datediff1,@datediff2)
 
 

View 9 Replies View Related

Calculate Difference Between Two Dates

Oct 18, 2006

Hi, i'm trying to calculate the number of days between two dates, but within an UPDATE statement, so far I can't wrap my head around how I can update a field with the number of days.

I was thinking something like


Code:

Update #ClaimMaster
Set covered_days = (then insert select statement that subtracts the two dates)



Does that make any sense?

View 1 Replies View Related

Difference Between Dates In The Same Column

Jun 23, 2007

i was working on a appication and just got stuck with this.
I have a column on a table which is a date column and i need to get the days differenct and copy it in another column
e.g

date days between
27-02-2005 1
28-02-2005 0
28-02-2005 0
28-02-2005 1
01-03-2005 0
01-03-2005

View 4 Replies View Related

Difference Between Dates Of 2 Rows

Nov 27, 2007

This is tricky so please read it through


For displaying data on the report I am using the following query

SELECT ReferenceNumber, ActivityID, ActivityTimeStamp, ActivityType, ActivityPerformedBy FROM ActivityDetails
ORDER BY ReferenceNumber, ActivityID

The result set is







Issue Reference #

Activity ID

Activity Date/Time

Activity Type




100819

4521404

11/4/07 2:06 PM

INIT




100819

4521405

11/4/07 2:07 PM

LOG




100819

4521406

11/4/07 2:07 PM

LOG




100819

4521473

11/4/07 2:28 PM

TR




100819

4521501

11/4/07 2:33 PM

WIP




100819

4521839

11/4/07 3:25 PM

RE




100819

4521844

11/4/07 3:27 PM

RE_Method




100819

4522575

11/4/07 8:53 PM

CL




100820

4521412

11/4/07 2:10 PM

INIT




100820

4521419

11/4/07 2:13 PM

ATTACHTDOC




100820

4525856

11/5/07 2:49 PM

ATTACHTDOC




100820

4525859

11/5/07 2:49 PM

LOG




100820

4525869

11/5/07 2:49 PM

CL




100821

4521423

11/4/07 2:14 PM

INIT




100821

4521425

11/4/07 2:14 PM

LOG




100821

4521429

11/4/07 2:14 PM

TR




100821

4521432

11/4/07 2:14 PM

ACK




100821

4522219

11/4/07 4:58 PM

RE




100821

4522221

11/4/07 4:58 PM

RE_Method




100821

4522447

11/4/07 6:51 PM

CL




On the report I have used the grouped by clause on 'Issue Reference #'. I want one more column which would calculate the difference between two consecutive Activity Date/Time of the same reference #.

e.g. Time difference between 4521404 and 4521405, 4521405 and 4521406, 4521406 and 4521473 etc. Please note that the difference between 4521412 and 4522575 will NOT be calculated since they are from different Reference Numbers.

Thanks,

View 2 Replies View Related

Difference Between Dates In Different Rows...

Sep 26, 2006

Hi all,

I have a table named Orders and this table has two relevant fields: CustomerId and OrderDate. I am trying to construct a query that will give me the difference, in days, between each customer's order so that the results would be something like: (using Northwind as the example)

...
ALFKI 25/08/1997 03/10/1997 39
ALFKI 03/10/1997 13/10/1997 10
ALFKI 13/10/1997 15/01/1998 94
ALFKI 15/01/1998 16/03/1998 60
ALFKI 16/03/1998 09/04/1998 24
...

At the moment, I have the following query that I think is on the right track:
€¦
SELECT dbo.Orders.CustomerID, dbo.Orders.OrderDate AS LowDate, Orders_1.OrderDate AS HighDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) AS Difference FROM dbo.Orders INNER JOIN dbo.Orders Orders_1 ON dbo.Orders.CustomerID = Orders_1.CustomerID AND dbo.Orders.OrderDate < Orders_1.OrderDate GROUP BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate, DATEDIFF([day], dbo.Orders.OrderDate, Orders_1.OrderDate) ORDER BY dbo.Orders.CustomerID, dbo.Orders.OrderDate, Orders_1.OrderDate
€¦

However, this gives me too much data:
€¦
ALFKI 25/08/1997 03/10/1997 39
ALFKI 25/08/1997 13/10/1997 49
ALFKI 25/08/1997 15/01/1998 143
ALFKI 25/08/1997 16/03/1998 203
ALFKI 25/08/1997 09/04/1998 227
ALFKI 03/10/1997 13/10/1997 10
ALFKI 03/10/1997 15/01/1998 104
ALFKI 03/10/1997 16/03/1998 164
ALFKI 03/10/1997 09/04/1998 188
ALFKI 13/10/1997 15/01/1998 94
ALFKI 13/10/1997 16/03/1998 154
ALFKI 13/10/1997 09/04/1998 178
ALFKI 15/01/1998 16/03/1998 60
ALFKI 15/01/1998 09/04/1998 84
€¦

So, do any of you have any ideas how I might achieve this? I know how to do it using a stored procedure, but I am trying to avoid that; I€™d like to do this in a single query.

Thanks for any help you have to offer,

Regards,

Stephen.

View 4 Replies View Related

Calculating Time Difference Between Two Different Dates

Dec 9, 2011

I have data in which i need to calculate employees working hours for a day...

name time in_out
manisha 2011-01-01 9:30:00.000am 1
manisha 2011-01-01 10:30:00.000 0
manisha 2011-01-01 10:45:00.000 1
manisha 2011-01-02 1:00:00.000am 0

How can i calculate time in that two dates as 1 is for entry an 0 is for exit..

View 2 Replies View Related

Time Difference Between Sequence Dates?

Feb 27, 2013

It has been a while since I have used SQL server and require to show a time difference between a start and end time, with only 1 date to determine these times

SQL
select
Arrival_date
locationdate as LocDate,
locationid as Loc_ID,
locations.loc_name as Location_Name
from current_locations
left outer join locations on
locations.loc_id = current_locations.location_id
where current_locations.Attend_ID = '1234567'

Results
Arrival Date LocDate Loc_IDloc_name
26/02/2013 19:2126/02/2013 19:271270Queue
26/02/2013 19:2126/02/2013 19:341278Dept 1
26/02/2013 19:2126/02/2013 21:10222Dept 2
26/02/2013 19:2127/02/2013 02:5631Left Department

What I want to acheive is the following where the Start location date is either the arrival date or the next location date and the endLocDate is the next finish date.

StartLocDateEndLocDateTime DifferenceDept
26/02/2013 19:2126/02/2013 19:2700/01/1900 00:06Queue
26/02/2013 19:2726/02/2013 19:3400/01/1900 00:07Dept 1
26/02/2013 19:3427/02/2013 02:5600/01/1900 07:22Dept 2

View 4 Replies View Related

Date Difference For Multiple Dates

Jun 18, 2014

I have a field called 'LOG_COMMENTS' in a table named T_PRODUCTION_WORK_LOG.

In the 'LOG_COMMENTS' whenever a request is placed on hold comments are added by the application, such as 'Status changed from Open to On Hold' and 'Status changed from On Hold to Open' along with a 'LOG_DATESTAMP' field. A request can go on and off Hold multiple times, how do I determine the days a request is On Hold?

I know I can use the sql function DATEDIFF ( datepart , startdate , enddate ), but how do I account for the possiblity that the request was On Hold more than once? And how would I get LOG_DATESTAMP' times for 'LOG_COMMENTS' that contain 'Status changed from Open to On Hold' and 'Status changed from On Hold to Open''?

View 7 Replies View Related

Power Pivot :: Difference Between Two Dates

Jun 2, 2015

Is it possible to get difference between two dates - first one and last one per every group in table (records are sorted by ID)?

ID Group Date
739 Group 1 2015-04-17 14:27
740 Group 1 2015-04-19 06:51
760 Group 1 2015-04-19 11:51
762 Group 1 2015-04-21 09:30
763 Group 1 2015-04-20 09:20

[Code] ....

View 4 Replies View Related

Displaying Difference Between Two Dates In The HH:mm:ss Format

Feb 28, 2007

I have a need to display the difference between two dates, a start date and a end date in the format HH:mm where the hours could be greater than 60.

For example:

Start Date - 30/01/2007 09:00:01
End Date - 01/02/2007 20:40:04

When i use the following code (=Fields!dateend.Value - Fields!DateStart.Value) i get 2.11:40:03 which i can easily understand, but the customer wants it as above!

I would like to be able to get it to be 59:40:03.

Any help would be much appreciated.

View 1 Replies View Related

Calculate Date Difference Excluding Dates

Aug 27, 2012

I have already seen stored procedures that can calculate a difference in dates, excluding the weekends. Any extension of such a SQL query to exclude not only weekends, but other dates as well. We have a table of "holidays" (not necessarily standard holidays), and I am wondering if there is a way to exclude them from the calculation.

View 7 Replies View Related

T-SQL (SS2K8) :: How Datediff Determine Difference Between Two Dates

Jan 19, 2015

select datediff(wk, '2015-01-11', '2015-01-19') returns 1 (shouldn't it return 2?)
select datediff(wk, '2015-01-10', '2015-01-19') returns 2

View 5 Replies View Related

Transact SQL :: Date Difference Between Two Dates In Due And OverDue

Sep 25, 2015

I want a difference in days 

Select datediff(dd,Target_Date,Achv_Date) 
Now , checks are 
1] when target date greater than achv_Date the difference should be greater than 0 
means for FileID 77608 
Select datediff(dd,'2015-09-24 00:00:00.000','2015-09-24 10:42:32.823')
 
i am getting -6 it should be 6 cant switch Target_Date and Achv_Date in datediff else i will get opposite result in first four records basically, i want a two column TAT and Status beside  achv_date based on the values of two dates difference see above ..and also want a result of (No. of Yes in status / No. of Files that has achv_date )i.e. result= (7/8) = 87% 

View 6 Replies View Related

SQL Server 2012 :: Difference Between Two Dates (Excluding Weekends)

May 19, 2014

I have a table with a list of jobs along with their start and end datetime values.

I am looking for a function which will return the time taken to process a job using a start date and an end date. If the date range covers a Saturday or Sunday I want the time to ignore the weekends.

Example

Start Date=2014-05-15 12:00:00.000
End Date=2014-05-19 13:00:00.000

Total Time should be: 2 Days, 1 Hour and 0 Minutes

View 5 Replies View Related

SQL Server 2008 :: Time Difference With Dates In Same Column

Mar 17, 2015

How do I find the time difference when the dates are in one column? I need to find hours and minutes between each row.

CREATE TABLE #Time ([TimeStamp] DATETIME, TimeDiff INT)
INSERT INTO #Time (TimeStamp)
VALUES ('2014-09-02 07:51:02.810'), ('2014-09-02 07:48:09.567'), ('2014-09-02 08:37:09.647')
, ('2014-09-02 16:16:42.593'), ('2014-09-02 08:06:13.387'),('2014-09-02 14:32:00.113')
DROP TABLE #Time

View 6 Replies View Related

SQL Server 2012 :: How To Calculate Dates Difference In A Same Table

Sep 4, 2015

I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.

Each customer is uniquely identified by a customer#

I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.

How can i do that in a query?

View 5 Replies View Related

Integration Services :: How To Get MM:SS When Doing Difference Between Start And End Dates In SSIS

May 28, 2015

I need getting the HH:MM:SS format when we do datepart.

For example, am using a variable to capture the time taken to execute the package using Start and End time stamps captured.

I would like to get the HH:MM:SS format of the datediff.

View 4 Replies View Related

Transact SQL :: How To Get Date Difference Between 2 Dates In DAYS Excluding Weekends

Oct 14, 2015

Here I have 2 Dates. CreatedDttm & ModifiedDttm.

I want  - DATEDIFF(Day,CreatedDttm,ModifiedDttm)  and I have to exclude the Weekend days from that query result.

View 10 Replies View Related

SQL Server 2012 :: Split Difference (number Of Nights) Between 2 Dates Into Respective Month Column

Sep 23, 2014

I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.

Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:

I need my query to output in the following format:

How to write this query?

View 9 Replies View Related

Remove Weekends And Non Working Days When Calculating Days Difference Between Two Dates

Jan 7, 2014

I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:

ce.enquiry_time represents when the enquiry was logged

(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))

It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.

View 1 Replies View Related

Can Anyone Give Me A Layman's Explanation Of The Difference Between CURRENT_TIMESTAMP And GETDATE() (if There Is A Difference)?

Oct 24, 2007

Question is in the subject.

Thanks in advance
-Jamie

View 7 Replies View Related

Want To Use Parameters To Filter For Dates Between Two (parameter, User-input) Dates

Mar 2, 2006

SQL 2005 Dev

How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?

View 3 Replies View Related

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: Insert Into Table Dates In Between Two Dates

Feb 28, 2015

I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.

CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL

[code]...

View 7 Replies View Related

Comparing Dates With Today Dates

Jun 21, 2005

I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date.  the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?

View 1 Replies View Related

Generate Dates Between Two Dates

Nov 8, 2006

Hi,

I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.

I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:

SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday
FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate
WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)

Any advice will be greatly appreciated!

View 1 Replies View Related

Getting Individual Dates Between 2 Dates

Mar 4, 2008

Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?

So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?

View 13 Replies View Related

Leap Year Dates Causing Error When You Pull Dates For Non Leap Year

Feb 28, 2008



I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error


Here is what I'm trying to generate






InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev

ADDIS
Q1
Jan
1
1
2008
NULL
NULL


ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65


ADDIS
Q1
Jan
1
1
2008
NULL
NULL


ADDIS
Q1
Jan
1
1
2008
NULL
NULL



Here is the error that I'm getting:



Code Snippet

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(4834 row(s) affected)



Here is my Transact-SQL Syntax (summarized because I Couldn't post it):

SELECT

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'

AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'

THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT Trans1.TR_Amount

FROM dbo.Trans Trans1

WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)

AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)

AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,


--AA Script Here AS AARmRev,

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)

= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT SUM(Trans1.TR_Amount)

FROM dbo.Trans Trans1

WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND

trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))

+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND

trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev



FROM dbo.EntityDef INNER JOIN

dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN

dbo.CurrentDate INNER JOIN

dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate

WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')

ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate


I appreciate all your help

Thanks

View 13 Replies View Related







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