Transact SQL :: Calculating Date Difference In Days
Apr 16, 2015
I'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.
View 12 Replies
ADVERTISEMENT
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
May 21, 2015
declare @siva1 datetime;
declare @siva2 datetime;
set @siva1='2014-03-10 05:02:11'
set @siva2='2014-03-12 23:52:11'
i want output like this 2.18 means 2day 18hours difference how using query
View 6 Replies
View Related
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
Oct 7, 2015
I 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.
View 3 Replies
View Related
Jul 21, 2001
Does anyone know of a way to calculate the date 'x' number of business days after another date?
View 2 Replies
View Related
Apr 2, 2007
Suppose I have these two days fields
ddold 1/1/2005 12:00:00 AM
ddnew 2/1/2007 12:00:00 AM
How can i get the DateDifference of these two dates in days.
View 4 Replies
View Related
Mar 31, 2008
I am using oracle 10G DB as back end.I have two date fields in a table.
1)premium_paying_start_date
2)premium_paying_end_date
i have to get the premium_term i.e, the difference between the two dates(premium_paying_end_date-premium_paying_start_date).
The difference should show the year,month and no of days difference.
For example :
premium_paying_start_date : 14-10-1984
premium_paying_end_date : 01-03-2008
Difference should be : 23 Y : 4 M : 15 D (Y = years, M = months , D= days)
So please give me the solution for this.
View 6 Replies
View Related
Oct 28, 2015
i have written a sql function which returns only number of working days (excludes holidays and Weekends) between given StartDate and EndDate.
USE [XXX]
GO
/****** Object: UserDefinedFunction [dbo].[CalculateNumberOFWorkDays] Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO
[code]...
I need a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date. Example scenario: If i give date as 12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table tblMasHolidayList where all the 2015 year holidays dates and info are stored.
View 18 Replies
View Related
Jul 13, 2015
We are maintaining 3 Shifts in our database. Problem in maintaining date and time for 3rd Shift. For example, today date is 13th July and third shift timing is 11 PM - 7 AM. Then I have to display the beginning date as 13/07/2015 11 PM and end date as 14/07/2015 7 AM. Please find the data(in seconds) available in database which I need to use for my calculation.
Date(Fcreacion)
Start time in Seconds(Hcreacion)
End time in seconds(Hcerrar)
turno(Shift)
[code]...
View 3 Replies
View Related
Sep 23, 2015
I have a column which stores a set of dates. I want to tell how many days left of a date till it’s month end. It should be noted that month ends are taken from the date series, not a calendar month end.
Something like below,
DateTD Days left
2009-01-05 14
2009-01-06 13
2009-01-07 12
2009-01-08 11
2009-01-09 10
2009-01-12 9
2009-01-13 8
2009-01-14 7
2009-01-15 6
2009-01-16 5
2009-01-19 4
2009-01-20 3
2009-01-21 2
2009-01-22 1
2009-01-23 0
2009-02-02 /
2009-02-03 /
View 28 Replies
View Related
Sep 11, 2015
I require outputting the date difference between two date's if it is greater than 7(DateDiff(day, DateAdd(day, t.[Started], Nxt.started), (t.[started])) > 7).I get incorrect syntax on my operator.What is the correct code?
View 6 Replies
View Related
Sep 8, 2015
I need to run a select on Mondays to pull data for 7 days prior to the Thursday of last week; i.e. Friday - Thursday inclusive. I'm sure this is simple, but I work with dates so infrequently that I need a refressher.
View 7 Replies
View Related
Sep 10, 2015
I 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".
View 3 Replies
View Related
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
Sep 21, 2015
I have a dynamic sql query where in I am comparing two tables and loading data for last 15 days. e.g today 2050921 then I am going to load till 20150906.
I pass on 2 variables @currentdate and @currentdate-1 to the query which are in date format 'yyyymmdd'
I need to do this for last 15 days how do I do this using while loop.
Note my date format is YYYYMMDD.
DECLARE @SQL VARCHAR(MAX)
@sql = ' insert into target
select from table_1_currentdate a
LEFT JOIN Table_2_currentdate-1 b
on a.col1=b.col1 where b.col1 is null '
exec(@sql)
I have to use while loop and decrement it every time and load data for last 15 days comparing two tables. I tried so many times I am not getting it right .
View 3 Replies
View Related
May 19, 2014
I 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] ....
View 10 Replies
View Related
Aug 21, 2005
I 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.
View 5 Replies
View Related
Jul 14, 2004
How 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?
View 7 Replies
View Related
Nov 28, 2013
Aim – 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
View 2 Replies
View Related
Aug 14, 2007
I 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 Related
Jul 12, 2000
Hello,
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.
View 2 Replies
View Related
Jul 12, 2000
Hello,
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.
View 1 Replies
View Related
Sep 4, 2007
Hi 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.
View 2 Replies
View Related
Feb 12, 2014
To calculate how many months are between the current date minus the First_Post_Date
For example
The First_Post_Date is displayed as follows following “25/07/2012”
Current date is 12-02-2014
The difference between the two dates is approx 20 months ..To make the calculation easier, it might be easier to default the day of First_Post_Date to 01 and do the same with the currentdate
So it would be 01/07/12 and 01/02/14
View 3 Replies
View Related
Jul 20, 2005
Hi,I have a table called Bookings which has two important columns;Booking_Start_Time and Booking_End_Time. These columns are both of typeDATETIME. Given any day how can I calculate how many hours are availablebetween the hours of 09.00 and 17.30 so a user can see at a glance how manyhours they have unbooked on a particular day (i.e. 8.5 hours less the timeof any bookings on that day), can this be done with a queryor do I have to work it out in my code?Thanks for your help
View 2 Replies
View Related
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
Dec 21, 2005
I have a data set like so:UTC_TIME Timestamp NodeID Message FlagLineStation11/19/2005 10:45:07 1132397107.91 1 3 5 1028103411/3/2005 21:05:35 1131051935.20 2 3 5 1009104311/25/2005 21:12:16 1132953136.59 3 3 5 10371049I added the UTC_TIME column in as aconversion of the unix timestamp inthe TIMESTAMP column.Keeping things simple and straightforward, I need to be able tocalculate the difference from one record to the next (ordered byTIMESTAMP or UTC_TIME) and output the result into another column in thetable.NODEID is the unique id.First, what is the function to do so if, say, I only wanted tocalculate the difference between 2 records as just a basic SELECTstatement. That way I can answer quick question based on any one or twoNODEID's.Second, how would I further that to continually calculate (as statedabove)?WOuld this be a stored procedure? A trigger? A cursor?I am learning as I go here. Any help is greatly appreciated.R.
View 4 Replies
View Related
Mar 25, 2014
I have Two Time fields in a table. Time(0). An "opening time" and a "closing time". They can hold any legit time.
I want to calculate in a SELECT Statement how many minutes within this range are within 9am to 5pm (which I'll convert to hours).
For example, here's an easy example:
OPEN: 9:00:00
CLOSE: 17:00:00
8 Hours/480 minutes
I could get this easy enough with a DATEDIFF function.
But what about:
OPEN: 08:00:00
CLOSE: 18:00:00
10 Hours total but only 8 of those 10 are within 9am-5pm.
Or what about:
OPEN: 10:00:00
CLOSE: 20:00:00
10 Hours total but only 7 are within 9am-5pm range.
I can calculate the total hours/minutes between the two times but not within that special range.
View 4 Replies
View Related
Dec 28, 2006
I am setting up a monitor to alert me if an SQL job has failed in the "last 20 minutes". This should run 24 hours a day, 7 days a week. My query looks something like this.
select * from TALMAIN.msdb.dbo.sysjobhistory where job_id = '7139D5D1-CD88-46E8-8324-5D5A0D8D3A27' and run_status <> 1 and
DATEPART(YYYY,GETDATE()) = substring(convert(char(8),run_date),1,4)and
DATEPART(MM,GETDATE()) = substring(convert(char(8),run_date),5,2) and
DATEPART(DD,GETDATE()) = substring(convert(char(8),run_date),7,2)and DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)) <= 20.
The run_date and run_time columns in msdb..sysjobhistory are stored as integers. Tried a couple of things, but I am unable to convert both of them to datetime data type. The last conditions in the above logic hold true for only "2 digit" hour and minute values.
DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)).
What about time values like 00:05 AM and single digit time values like 1:00 AM and 9:05 AM, for example?. I pasted some sample run_date and run_time values from sysjobhistory below.
run_date run_time
2006122821510 -- 02:15:10 AM (how to get the minute count?)
2006122821510 -- 02:15:10 AM (same as above)
20061227233014 -- 23:30:14 PM (this is strt forward)
20061227233014 -- 23:30:14 PM (same as above)
200612273016 -- 00:30:16 AM (how to get minute count?)
200612273015 -- 00:30:15 AM (how to get minute count?)
Is there a simpler logic to achieve this? Hope I was clear, else let me know. Please advise. Thank you.
View 5 Replies
View Related
Aug 15, 2006
Hi! I am totally new to the forum and to the SQL.
Need your advice. My report will run on Friday automatically and I need to retrieve the data from previous Friday until Today’s Friday.
I have an example from another developer’s code, but this one gets the data on the previous day. Can you please help me to right the expression?
Here is an example ‘on the previous day’
CAST(CONVERT(CHAR(10), tbl1.closeddate,120) as datetime) = CAST(CONVERT(CHAR(10), dateadd(day, -1,getdate()), 120) as datetime)
This is what I did (assuming today is Friday)
(tbl1.closedDate BETWEEN CAST(CONVERT(CHAR(10), DATEADD(day, - 8, GETDATE()), 120) AS datetime) AND CAST(CONVERT(CHAR(10),
DATEADD(day, - 1, GETDATE()), 120) AS datetime))
I don't get any records back.
Please help.
Thanks a lot.
View 11 Replies
View Related
Oct 10, 2013
i have a table
a1
-----
id t1
How can I calculate the difference between getdate() and t1 how many days.
View 3 Replies
View Related
Jun 15, 2015
Given the attached report, is there an easy way of calculating the difference between the Today and QTR Start column? Because of the Account Group, the report looks like the sample shown on the second image.
Sample report:
View 4 Replies
View Related
