Calculating Date Based On Business Days
Jul 21, 2001Does anyone know of a way to calculate the date 'x' number of business days after another date?
View 2 Replies
ADVERTISEMENT
Transact SQL :: Calculating Date Difference In Days
Apr 16, 2015I'm trying to calculate the time difference between a date field and today's date in days. The date field is not mandatory and can therefore be blank. I'm trying to execute the following query:
SELECT employee_code, Civil_ID, DATEDIFF(Day, Civil_ID, GETDATE())
FROM ODEV_VIEW_Credentials_Expiry_Dates
WHERE Civil_ID IS NOT NULL AND Civil_ID != ''
ORDER BY employee_code
I keep getting the following message:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Warning: Null value is eliminated by an aggregate or other SET operation.
No matter what filter I use to process non-blank dates, it never works.
T-SQL (SS2K8) :: Query To Find Older Date That Is Less Than 90 Business Days
Feb 12, 2015select DateAdd(dd,-90,getdate())
It gives the 90 days older date but i want to exclude the weekdays and holidays (saturday ,sunday,holiday)
As we dont have permission to call a function or Sp in high level environments looking for a simple query .
SQL Server 2012 :: Add Business Days To A Date Using Calendar Table
May 12, 2015I have a date that I need to add 'n' number of business days to. I have a calendar table that has a 'IsBusinessDay' flag, so it would be good if I was able to use this to get what I need. I've tried to use the 'LEAD' function in the following way;
SELECT A. Date, B.DatePlus3BusinessDays
FROM TableA A
LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey = B.DateKey
Problem with this is that because I am filtering the Calendar for business days only, when there is a date that is not a business day in TableA, a NULL is being returned.
Is there any way to do a conditional LEAD, so it skips rows that are not business days? Or do I have do go with a completely different approach?
Transact SQL :: Calendar Table - Get The Date Post 3 Business Days
Sep 10, 2015I have a calendar table against entire year 2015 with each day with 2 flag,
1. WK_DT_IN == except Satarday and Sunday, value is "Y", for Sat/Sun, value is "N"
2. HOL_DT_IN == value will only be "Y" only for holiday, example for '2015-01-01' date, it's value is "Y"
DECLARE @CAL TABLE (CAL_DT DATE, WK_DT_IN CHAR(1), HOL_DT_IN CHAR(1))
INSERT INTO @CAL VALUES ('2015-01-01', 'Y', 'Y'), ('2015-01-02', 'Y', 'N'),('2015-01-03', 'N', 'N'),
('2015-01-04', 'N', 'N'), ('2015-01-05', 'Y', 'N'), ('2015-01-06', 'Y', 'N'), ('2015-01-07', 'Y', 'N')
We have a given date, example '2015-01-01', I need to find out a date after 2 business days? I can think of loop, but will it work.
I need to exclude date which having HOL_DT_IN = "Y" and WK_DT_IN = "N".
Transact SQL :: Calc Work Date Using Business Days Field In Calendar Table
May 19, 2014I created a dbo.Calendar table that stores dates and a work day flag (1=work day, 0=non-work day) so I can use it to calculate the next business date from a date using a function. I'm using a while group to count only the work days and a couple other internal variables but I'm not sure if I can even use them in a function.
Assuming Sats & Suns are all non-work days in April 2014, if my @WorkDays = 10 for 10 work days and my @DateFromValue - 4/1/2014, I would expect my return date to be 4/15/2014.
------ Messages after I click execute on my query window that has my function ------------------------------------------------------
Msg 444, Level 16, State 2, Procedure FGetWorkDate, Line 19
Select statements included within a function cannot return data to a client.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 20
Invalid column name 'WorkDay'.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 22
Invalid column name 'Date'.
------ my function code ----------------------------
CREATE FUNCTION [dbo].[FGetWorkDate](
@WorkDays VARCHAR(5),
@DateFromValue AS DateTime )
RETURNS DATETIME
[Code] ....
SQL Server 2012 :: Calculate Number Of Days Based On Computer System Date And Due Date Row
Mar 18, 2014I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
Transact SQL :: To Display Days Hours Mins Format Based On Business Hours
Apr 22, 2015I want to display Days Hours Mins Format.
I am Having two columns Like below,
Col1 (in days) col2 (In Hours : Mins)
3days 4:5
In this first have to add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5
From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.
Remove Weekends And Non Working Days When Calculating Days Difference Between Two Dates
Jan 7, 2014I 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.
Obtaining Business Hours Based On Start And End Date
Jul 23, 2005I have a transaction log that tracks issues from a call center. Eachtime an issue is assigned to someone else, closed, etc. I get a timestamp. I have these time stamps for the beginning of an issue to theend of an issue and I'd like to determine how many business hours theseissues were open.Issue BeginDt Enddt Total hours1 3/29/05 5:00 PM 4/1/05 2:00 PM 69Basically, this is the type of data I'm looking at and my hours of workare from 7:30 - 5:00 weekdays. I need to come up with a way to removeall nonbusiness hours, weekends, & holidays from the difference of thetwo dates. Issues can span for 2-3 days or 20-30 days.Please let me know if anyone has any ideas or has done something likethis before.Thanks!
View 5 Replies View RelatedQuery 8 Days Ago Based On System Date
Jan 16, 2008
Hi,
I'm trying to query an SQL table column with date values to show 8 Days ago results.
I've started with this query:
SELECT ficheiro, erro, descritivo_erro, contrato, DO, movimento, data, descritivo, tipo_movimento, desconto, montante, comissao, IVA
FROM status_day
WHERE (YEAR(data) = YEAR(GETDATE())) AND (MONTH(data) = MONTH(GETDATE())) AND (DAY(data) = DAY(GETDATE()) -8)
ORDER BY descritivo_erro, contrato
The problem is that the text in red will have some problems when the month changes - If I want the 8 days ago results from January and the system date is 1st of February the query will not return any values.
I read something about DATESERIAL but is wasn't conclusive on how to use it with system date.
Please help me out with this query.
Case Statement - Calculate No Of Days Based On User Input Start And End Date
Jan 5, 2015I have to produce a report to calculate no of days based on user input start date and end date. I have tried to explain below.
say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days
For this :
I calculated datediff + 1 and got sub jobs days BUT
say financial director wants to see Title of 'Sub Jobs' with 'Days' from 1st Dec to 31st Dec
so on paper I calulated as :
1-31 Dec 2014
PhaniMarketing NULL (Do not fall in Req Dt)
PhaniAdmin 20 (Deduct 2 days of Nov & calculated 20 days of Dec)
PhaniCRM 11 (Deduct 20 days of Nov and deduct 11 days of Jan so for Dec , we got 11 days)
Total days 31
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
T-SQL (SS2K8) :: Generate Working Schedule For Employees For X-days Ahead Based On Starting Date
May 6, 2014I would like to generate a working schedule for employees for x-days ahead based on a starting date that the user can enter.
I have got 3 relevant tables:
1. Table X with (1) resourcenumber, (2) starting date working schedule and (3) the daynumber representing the starting date (this is ISO so 1 for Monday, 2 for Tuesday etc.)
2. Table Y has the schedule itself and can hold a 7-days schedule or a 14-days schedule. In case of 7 days schedule there a 14 (!) records with (1) resourcenumber, (2) daynumber, (3) starting hour a.m. (4) ending hour a.m (5) starting hour p.m and (6) ending hour p.m. In case of a 14-days schedule there are 28 records (a.m. and p.m. records)
3. Table Z with resource data.
An example to clarify (for fake employee 100):
Table X:
Resource: 100
Starting date: 2012-03-01 (from this date the schedule will be effective)
Daynumber: 4 (2012-03-01 was a Thursday)
Table Y (Resource has a 14 days schedule because per 2 weeks Monday is an off-day):
Record 1 shows: Resource: 100, Daynumber: 1 (= Monday, working day), AM-Starting hour: 09:00, AM-Ending hour: 13:00, PM-starting hour: 13:30, PM-ending hour: 17:30
Record 2: same but daynumber is 2
Record 3: same but daynumber is 3 etc.
...
Record 8 shows: Resource: 100, Daynumber: 8 (= Monday, off-day), AM-Starting hour: 00:00, AM-Ending hour: 00:00, PM-starting hour: 00:00, PM-ending hour: 00:00
Record 9: same as record 2 but daynumber is 9.
etc.
...
Record 14: same as record 7 but day is 14 (= last day)
The weekend days show as 00:00 for the hours (same as day 8 in example)
I generated the working schedule with a CROSS APPLY function based on the starting date and the x-number of days ahead.
I then evaluate the actual daynumber corresponding with that date with the daynumber in table Y. That works fine with a 7-days schedule but I can't get it fixed with a 14-days schedule. Day 8 in that schedule represents an actual day 1 but how do I know what actual date day 8 is ... I think I have to start with the starting date in table X ...
I think ideally I would like to have the generated days as follows (as an example in case of a 14-days schedule starting 2014-05-01 for 30 days ahead):
2014-05-01 = day 4 (= actual daynumber)
2014-05-02 = day 5
2014-05-03 = day 6
...
2014-05-10 = day 13
2014-05-11 = day 14
2014-05-12 = day 1
2014-05-13 = day 2
2014-05-14 = day 3
...
2014-05-24 = day 13
2014-05-25 = day 14
2014-05-26 = day 1
2014-05-27 = day 2
...
2014-05-31 = day 6
With this done I can compare the actual daynumber with the daynumber in Table Y.
The rownumber that the CROSS APPLY function generates has to be reset to 1 after day 14. I tried PARTITION BY in THE ROW_NUMBER function but to no avail ... The only field I can partition by is the maximum value of the daynumber (14 is the example) but that is not allowed in the rownumber function.
SQL 2012 :: Produce Report To Calculate No Of Days Based On User Input Start And End Date
Jan 5, 2015I have to produce a report to calculate no of days based on user input start date and end date.
say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days
for this :
[code]...
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
Calculating Business Day
Aug 20, 2007Hi,
I am wondering what is vb.net method of calculating the prior business day (Monday - Friday).
For example, if today is Monday, Aug. 20, the last business day would have been Friday, Aug. 17.
I have a way to do this in SQL, but not vb.net.
Thanks much
Calculating Business Hours
May 10, 2006I am trying to calculate business hours that an order that is open. The rules are 8am-5pm. For instance the first row, the clock would stop at 17:00, and pick up again at 8am and add on to total business hours. If the order was created after business hours, and the endstamp was before business hours, this would be 0. If created after 5pm friday, and the endstamp was before 8am monday, this would be 0. In the second set of timestamps I have here the order was recieved at 14:39 but took until the following day at 14:49 to be ordered. I imagine I have to use datepart and datediff for this, but other than that I am not sure on how to do it. Any help would really be appreciated!
StartStampEndstamp
6/27/2005 14:356/27/2005 17:41:11
6/27/2005 14:396/28/2005 14:49
6/27/2005 18:486/27/2005 18:54
6/27/2005 11:416/27/2005 11:45
Calculating Overdue Days
Aug 21, 2005I have table with three columns: 1) TodaysDate,
2)
DueBackDate,
3) DaysOverdue
The problem I am having is creating a formula in MS SQL, so that when
the 'DueBackDate' column is more than todays date, I want the
'DaysOverdue' column to be incremented accordingly.
I have tried creating an SQL Query something like this:
UPDATE rental
SET DaysOverdue = DaysOverdue + 1
WHERE ({ fn NOW() } > DueBackDate)
But the problem with this that it only increments the 'DaysOverdue'
field by 1. When I want it to basically subtract todays date by
'DueBackDate'.
I have tried to also create a formula within MS SQL but I come up with
errors. I have done something like this in Access which worked. The
Formula looked like this:
IIF(Now()>[DueBackDate],int(Now()-[DueBackDate]),0)
Is there a way to convert this formula so that is would work in MS SQL?
Thanks.
Calculating The No. Of Days In A Month
Jul 14, 2004How can I calculate the no. of days in a month.I know it is a simple question but I am not able to find any code to calculate that.
Can someone help me?
Business Days
Jun 20, 2008dear gurus,
I want to get the working days between two days..
in a single query.
i will give the start_date '06-02-2008',end_date '06-13-2008' the result should be as below.
06-02-2008Monday
06-03-2008Tuesday
06-04-2008Wednesday
06-05-2008Thursday
06-06-2008Friday
06-09-2008Monday
06-10-2008Tuesday
06-11-2008Wednesday
06-12-2008Thursday
06-13-2008Friday
Thanks in advance.
cool...,
Business Days
Sep 24, 2007I Have a date range and i want to calculate the number of days - the weekends(saturdays and sundays) so that only businessdays are obtained.
A simple table's example:
Name, surname, datebeg, dateend
How can can it be done with sql?
BB
Calculating Avg Of Days Broken Down By Period
Nov 28, 2013Aim – Calculate the number of days between CreatedDate and [Date_Docs_In_Complete__c],count how many Ids, Fall within a specific month by year, and then work out the avg number of days it took for each month
My query so far
select
ID,
left(CreatedDate,10) as CreatedDate,
left([Date_Docs_In_Complete__c],10) as [Date_Docs_In_Complete__c],
DATEDIFF(day,CreatedDate,[Date_Docs_In_Complete__c]) as Days
from #build
Produces following results
IDCreatedDateDate_Docs_In_Complete__cDays
0063000000ausKGAAY2010-03-262013-07-161208
0063000000mC359AAC2011-06-302013-07-03734
0063000000oyaSPAAY2011-11-292013-07-18597
Desired outcome results would be
Year Month Date_Docs_In_Complete__c Total Days Avg_Days
2013 07 3 2539846.3
DateDiff: Calculating Working Days
Aug 14, 2007I am trying to use the DateDiff function to calculate the difference between two dates in working days only... Is this possible in SSRS 2005, or can anyone suggest an alternate solution?
View 8 Replies View RelatedNumber Of Business Days
Mar 22, 2001Is there any easy way to calulate number of business days between the 2 dates ?
I want to exclude all sat and sun between the 2 dates.
DATEDIFF For Business Days Only
Aug 16, 2007I'm looking for a DATEDIFF function which will give me the
BUSINESS days difference between 2 dates.
e.g: datediff(day,'08/08/2007','08/14/2007) would normally result in 6 (i think), however in business days this would be 4.
is there such a function?
Thx
TSQL : Calculating Working Days Between Two Dates
Jul 12, 2000Hello,
Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !
Jon Reade
Sql Server DBA
NEC Technologies (UK) Ltd.
TSQL : Calculating Working Days Between Two Dates
Jul 12, 2000Hello,
Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !
Jon Reade
Sql Server DBA
NEC Technologies (UK) Ltd.
Calculating Number Of Working Days In A Month
Sep 4, 2007Hi 2 all,
I need to calculate the number of working days in a month. In clear, i need to ignore saturday.sunday and holiday in a month.
can anyone say the solution?
thanks.
Transact SQL :: Calculating Duration In Days For Generic From And To
Oct 7, 2015I need the SQL to calculate the duration between a day and time, no specific date.
For example:
Sun 00:00 > Mon 08:00 = 5.67 days
So there is no date, it's just the general duration (in days), from a specific day and time to a specific day and time.
Need Function To Count Business Days
Apr 21, 2000I need a function to count business days (exclude Sat, Sun) that I can call within a view. I would rather not build a "calendar table" this will be used ongoing for years into the future.
Does anyone have anything like this they could share? If there is another source you could direct me to I would appreciate that as well.
TIA
Phil
SELECT To Get The Last 5 Business Days- Tricky
Apr 7, 2008I have stumbled upon a sql question I cannot answer. I am looking for the following SELECT sql statement:
Basically I need a way to get "5 days ago from today". BUT, the trick is that there is a table called tblnoworkday with contains weekends and holidays and those dates cannot count. So basically what I am really trying to get is "5 Business days ago from today".
So it would basically do this for an query input date of '4/9/08'. If the table is called TblNoWorkday and contains the following records:
...
2008-04-06 00:00:00.000
2008-04-05 00:00:00.000
...
This is the last 5 business days then: (not in the table)
4/9/08
4/8/08
4/7/08
****weekend****
4/4/08
4/3/08
The query should return just 4/3/08. The problem is 4/3/08 doesn't exist in the database since the database only contains the no work days.
Any ideas on how to do this in a simple query without having to create another table with the valid working dates?
Thanks Before Hand,
Adiel
Finding Number Of Business Days Between Two Dates
Nov 27, 1999Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas
Finding Number Of Business Days Between Two Dates
Nov 27, 1999Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas
Analysis :: How To Get Cumulative Count Of Business Days
Jul 9, 2015I am trying to get a cumulative count of business days. I have a column in my date dimension BusinessDayInd which is 'Y' or 'N'. I have been trying to create a calculated column in the cube but have not been successful yet. I think I'm close with this:
COUNTROWS(FILTER(DATESMTD(PostingDate[Posting Date]), PostingDate[BusinessDayInd] = "Y"))
However, this is giving me a count of calendar days for all business days and null for non-business days. How do I apply the filter to DATESMTD? I've tried many iterations of this using CALCULATETABLE, CALCULATE, and FILTER. All are giving me the same result.
What am I doing wrong?