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


ADVERTISEMENT

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

Excluding Weekends When Finding The (dd/hh/mm/ss) Between Two Dates

Mar 27, 2008

I have two datetime fields that I would like to find out how much time has passed between them. First field is "start date" and the second is "end date" the dates in both fields are in this format (03/27/2008 4:00PM). The problem I am having is I need to exclude the time passed from Friday, 6:00PM to Monday, 7:00AM if the dates happen to go over a weekend.



Any help would be greatly appreciated.



Thanks

JP

View 6 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

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

Data From Previous Day Excluding Weekends

Jan 23, 2014

One of our departments once to automate a query that they have to pull data from the previous day. We are going to set this up as a job. How can we do this without using the Saturday and Sunday dates? So what we want to do is Pull the data from Friday on Monday. Is this possible? This is what they have. I know this pull the data from the day before.

select distinct

clm_id1, clm_rcvd, clm_6a, clm_6b, clm_dout, clm_cc1, clm_clir, clm_65a, clm_5, clm_1a, clm_1a1, clm_1a2, clm_1b, clm_1d, clm_1e, clm_1f, clm_tchg, clm_nego, clm_sppo, clm_att1, clm_att2, clm_att3, clm_att4, clm_att5, clm_chast, clme_fild

from

impact.dbo.clm
left join impact.dbo.clme on clm_id1 = clme_id
where
clm_dout = getdate()-1

View 5 Replies View Related

DateAdd Function - Excluding Weekends

Jan 4, 2008

Im using the DateAdd Function to establish a future date base on the required time for a series of events to transpire. I'd like to exclude weekends, does anyone know a way to do this?

Thanks in advance

Alex

View 1 Replies View Related

T-SQL (SS2K8) :: Consecutive Streak Excluding Weekends

Aug 8, 2014

I'm trying to write an algorithm that returns the most recent and longest consecutive streak of positive or negative price changes in a given stock. The streak can extend over null weekends, but not over null weekdays (presumably trading days).

For example, lets say Google had end of day positive returns on (any given) Tuesday, Monday, and previous Friday, but then Thursday, it had negative returns. That would be a 3 day streak between Friday and Tuesday. Also, if a date has a null value on a date that is NOT a weekend, the streak ends.

In the following code sample, you can get a simplified idea of what the raw data will look like and what the output should look like.

set nocount on
set datefirst 7
go
if object_id('tempdb.dbo.#raw') is not null drop table #raw
create table #raw

[Code] .....

I should also mention that this has to be done over about half a million symbols so something RBAR is especially unappealing.

View 5 Replies View Related

Transact SQL :: Calculate Working Days Excluding Weekends

Jun 7, 2015

Iam trying to calculate the number of working days between two dates. Iam getting the uouput as only 1 02 r working days??

select  building_number as SchoolID,building_name as Campus,   count( distinct( CASE  WHEN(( DATEPART(dw, CurDate) + @@DATEFIRST)%7 NOT IN (0,1)) tHEN 1 ELSE 0 END)) as NumberofDaysServed   from   Sales sl join Buildings b on  sl.Building_Num =b.Building_number join students2 s on  s.Student_Number= sl.Student_Num   join Sale_Items SI on   si.UID = sl.UID   where  CONVERT(CHAR(10),CurDate,120) between '2015-05-01' and   '2015-05-07'      and VoidReview <> 'v' and  SI.INum = '1'    group by  building_number,building_name order by building_number,Building_Name;

View 8 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

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

Date Difference Measurement And Weekends / Holidays

Jun 4, 2008

The below code works fine to measure the difference in days between two dates.
However, there is an additional business requirement to subtract week-ends, and holidays, from the equation. 
Any ideas on how to accomplish this task, and leverage the below, existing code?  Thanks in advance! 
(SELECT ABS((TO_DATE(TO_CHAR(" & ToFieldDate & "),'yyyymmdd') - TO_DATE(TO_CHAR(" & FromFieldDate & "),'yyyymmdd'))) FROM DUAL) AS Measurement "

View 2 Replies View Related

SQL Server 2012 :: Calculate Number Of Days From A Date - Exclude Weekends And Holidays

Feb 2, 2014

I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.

CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime)
RETURNS int
AS
BEGIN
/*
Description:
Function designed to calculate the number of business days (In hours) between two dates.

[Code] ......

View 4 Replies View Related

# Of Weekdays Between Dates Excluding Sat/Sun

May 14, 2002

Does anyone know how to write code to tell the amount of days between 2 dates excluding Saturday and Sunday.
Datediff will tell me the total amount of days and I guess I need some logic to look at the startdate and manipulate the total after the fact.

Any thoughts or solutions out there?

View 4 Replies View Related

Calculate Date Difference Excluding Bank Holidays

Aug 28, 2013

I have a Bank holiday table (ID column, and Date column), how to exclude bank holidays in the datediff returned.

Create FUNCTION [dbo].days_diff ( @date1 datetime,@date2 datetime )
RETURNS int
AS
BEGIN
declare @i int
Declare @count int

[Code] .....

View 4 Replies View Related

SQL Server 2012 :: Excluding Records Whose Values From 2 Different Fields Match

Aug 31, 2015

Using MSSQL 2012

I have a simple select query and I need to eliminate records whose values from 2 different fields match. I thought I had this working, but if one of those fields in my data IS NULL it filters out those records. If I comment out my last line then my number record shows, if I include that statement that record drops. The only thing I see in my data is the Name and PName are both NULL in the data for that particular number. Just need to filter out any records where it finds those 3 Names that also have "Default" as the PName, then include everything else even if Name or Pname is NULL.

Below is my where clause.

WHERE [DETERMINATION] <> 'Denied'
AND [Number] ='A150731000039'

---- Removes incorrect records where these names match----
AND ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')

View 4 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

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

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 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

SQL Server 2012 :: Behavioral Difference Between DELETE And TRUNCATE

Apr 4, 2014

I have an issue with Delete statement.In the code given below (its a part of actual proc),if we use TRUNCATE to clean the temp tables, everything goes fine.But if I use DELETE in place of truncate, system skips the IF loop 'if (@script_type = 1 OR @script_type = 2)'I am not able to understand this behavioral difference between DELETE and TRUNCATE.Recently the database is being used for replication, but that should not be a reason.

SELECT @max_rows = COUNT('X') FROM #temp_table1
SET@row_cnt = 1
WHILE @row_cnt <= @max_rows
BEGIN

[code],...

View 2 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







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