Hi to all,
I want to make an sql query that will subtract two dates and the difference will be number in months.
i have this table TestTable with Fields ACost, Adate, AMonth
my formula is this X = (Now-Adate), should give me number in months
and Y = (ACost/AMonth)
i want to get the product of X and Y, (X*Y).
how can i make this in sql views?
can anyone help me with this?
thanks a lot!
best regards,
saldiboy
I would like to calculate difference between end_date and current date in Months.And also how we can calculate the sum of difference in months between start_date and end_date for each ID?
CREATE TABLE datedifference (
id INT
,start_date INT
,end_date INT
)
INSERT INTO datedifference VALUES (10,20091202,20100629)
INSERT INTO datedifference VALUES (20,20071202,20090330)
INSERT INTO datedifference VALUES (30,20051202,20101031)
Hi all,How to get the date from the 3 months ago? So, if user enter a date (10/10/2006), how can I get to 07/10/2006.Thanks in advance...
View 3 Replies View RelatedHi,
I need a select statement with the condition that the date (a timestamp with format yyyy-mm-dd) is either equal to or longer than 14 months from today's date.
I am using SQL Server
Can anyone point me in the right diretion?
Thanks
I have a simple query which returns a purchase date plus the number of months warranty an item has. What I need to do is add these months to the purchase date to get a warranty expiry date. What is the best way to do this? My query and results are below, I need an extra column in the results with that calculated warranty date:
select item, purchase_date, warranty_period
from inventory where item like '05MC%'
item purchase_date warranty_period
-------------------- ----------------------- ---------------
05MC0001 2014-07-22 00:00:00 36
05MC0002 2014-07-11 00:00:00 36
What is the correct code for an expression where my "datetime" field is greater then or equal to 3 months ago?
View 1 Replies View RelatedHello
I had a requirement to filter records for the current year only... I went back to the user and explained that the logic wasn't sound... eg if somebody did the search early in January then they would probably get no results back. My suggestion was to go back two months if the current date is in January or february.
So, this is what I have.......
Code Snippet
where cs.startdate > case when year(cs.startdate) = year(getdate()) and month(getdate()) > 2 then
convert(char(4),year(getdate()))+'-01-01' else dateadd(month,-2,getdate()) end
Is there a neater/better way of coding this?
Jon
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 RelatedI am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.
WITH CutomMonths
AS (
SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (
VALUES (1)
[Code] ...
Current output what i am getting:
Expected Output:
I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.
But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.
I need to find out All mondays / Tuesdays etc dates for next 6 months from the current date.
I have a table called DayOfWeek and the records are
1Sun
2Mon
3Tue
4Wed
5Thu
6Fri
7Sat
so if I pass "1" to the stored procedure, I need to find all Sunday dates for the next 6 months
And so if I pass "2" to the stored procedure, I need to find all Monday dates for the next 6 months...etc
I would like to display Car records to retrieve last Maintenance request for all customer who did not make request in past 9 months from current date.
MC refers to Maintenance.
MCLog data:
carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30
carId: SGX55661 (repeated)
mcDate: 2015-05-30
mcDate: 2015-06-15
[Code] .....
Here is my Table:
CREATE TABLE Car (
carId Char (20) NOT NULL,
model Char (20) NOT NULL,
importDate smalldatetime NOT NULL,
custId Varchar (50) NOT NULL,
CONSTRAINT Car_PK PRIMARY KEY (carId));
[code]....
Hi there,
if any body out there has done this before, can you please post the T-SQL script that calculate whatever last months of data based on a Date field.
Thanks very much,
Manny
I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1
I need to create a date dimension where the lowest level is month. I've seen examples which use the list function such as
Source = List.Dates(#date(2000, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2000,1,1)), #duration(1,0,0,0)),
The above increments by 1 day which is defined in the 1st argument of the #duration. My question is how can I dynamically change the value of this 1st argument such that its the number of days in the current month hence it will increment to only return the 1st date in the Month e.g
1/1/2000
1/2/2000
1/3/2000
etc..
I prefer to use an elegant approach if possible, the alternative would be return all dates, create a custom column from these dates which returns the month date - delete the dates column - get a distinct list of the month dates.
My data has 2 fields: Customer Telephone Number, Date of Visit.
Basically I want to add a field ([# of Visits]), which tells me what number of visit the current record is within 6 months.
Customer TN | Date of Visit | # of Visits (Within 6 month - 180 days)
1111 | 01-Jan-2015 | 1
1111 | 06-Jan-2015 | 2
1111 | 30-Jan-2015 | 3
1111 | 05-Apr-2015 | 4
1111 | 07-Jul-2015 | 3
As you can see, the last visit would counts as 3rd because 180 days from 07-Jul-2015 would be Jan-8-2015.
I have a ssrs report which has a parameter start date.i have made 12 columns acc to client req which i cannot change:
jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec
These columns are dynamic acc to start date for which i have used this exp for first cell:
=monthname(month(Parameters!Startdate.Value),true) and for next 11 cells accordingly.
These 12 columns are grouped quaterly which i have made statically again for clien req which i cannot change so they are grouped staticaly by me in report like:
q12015 |q2 2015 |q32015 |q42015
jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec
exp for quarterly is :
="Q" & DatePart(DateInterval.Quarter, Parameters!Startdate.Value) & year(Parameters!Startdate.Value).
Now when i select my start date of months jan,apr,july and oct report work fine.... but when i select my start date months of feb,may,aug and nov then reportis something like: (lets say i choose feb)
q12015 |q22015 |q32015 |q42015
feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec|jan.
What is happening is in q1 feb,mar and apr are coming and what i want in q1 is jan,feb and mar.
And in q4 nov ,dec and jan is coming and what is want in q4 is oct,nov and dec.
So generally what is am asking is how to display jan in this condition in my first cell.same apllies for rest of months..
Similarly when i select my start date months of mar,june,sep,dec i want previous two months to be displayed.
Hi I wanted to get difference between this two dates '12-31-2001','01-01-2002').
1 day 0 month 0 year.
Do we have any date function to get like this or we need write our own function stored proc?>
Any advice please?.
select datediff(ss,In_time,Out_time)
select datediff(ss,'23:22:11','00:40:50')
But out time is as 12/6/2007 00:40:50
and in time is as 11/6/2007 23:22:11
when iam trying to get the date diff it gives the negitive value as
Column
------------
-81681
how can i get the actual difference
Malathi Rao
Hi,
I have a problem with the following:
I have 2 dates in the following format mm/dd/yyyy hh:mm:ss AM.
I would like to measure the difference between 2 dates (Date1 and Date2).
When I use the following expression "DATEDIFF(d, Date1, Date2)" I get a value
without decimals. How can I have a value in days with decimals like e.g. 5.38? If you calculate this using Excel you get decimals behind the comma.
Please help!!!
Thanks!
DHL
Hi,
I and my friend are trying to solve problem :
I have a system where an employee makes a call. The call can be of type 1,2,3. I have to calculate:
a. the last time the employee called up for that particular call type.
b. Yesterday
c. Call Duration between the last call date and yesterday
Please see the expected output to get an idea.
I will be passing in EmpID and CallType to the procedure.
First a record gets inserted into CallDetail and then into the Call for each CallType.
SAMPLE SCRIPT:
DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)
INSERT @CallDetail
SELECT 12123, 1, '11/30/2007 10:41:34 AM' UNION ALL
SELECT 43555, 1, '12/1/2007 11:21:23 AM' UNION ALL
SELECT 65322, 1, '12/18/2007 04:12:34 AM'
-- 65322 is the current calldetail id
INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 2, 12123 UNION ALL
SELECT 113, 2, 12123 UNION ALL
SELECT 123, 1, 43555 UNION ALL
SELECT 134, 1, 43555 UNION ALL
SELECT 143, 1, 65322 UNION ALL
SELECT 145, 1, 65322 UNION ALL
SELECT 154, 2, 65322 UNION ALL
SELECT 185, 3, 65322
Select * from @Call
Select * from @CallDetail
EXPECTED OUTPUT-- For CallType 1 EmpID 1
LastCallDate Yesterday Duration
12/1/2007 12/17/2007 16 days
-- For CallType 2 EmpID 1
LastCallDate Yesterday Duration
11/30/2007 12/17/2007 17 days
-- For CallType 3 (There was no CallType 3 made earlier by Empid 1)
LastCallDate Yesterday Duration
- 12/17/2007 0 days
How to achieve it
thanks.
I have the following table
FeedBack Type Date
test2 positive 03/15/08
tes3 negative 03/01/08
.. ....
in my page i need to select the number of negative/positive comments within the last
1 month, 6 months, 12 months
How can I accomplish that?
thanks
Have a table that list item#, date the standard cost went into effect and the standard cost. How do I find the difference in StdCost on the last EffectiveDate and second to last EffectiveDate. 5.59 (01/05/2015) minus 5.81 (09/29/.014) = -.22.
Item# EffectiveDate StdCost
1152 01/01/2009 5.50
1152 09/29/2014 5.81
1152 04/04/2011 5.56
[code]....
I have a table containing following Columns plus other columns:
Start Date 04-01-2002(mm-dd-yyyy) varchar(10)
Start Time column 09:30:30(hh:mm:ss) varchar(8)
End Date 04-05-2002(mm-dd-yyyy) varchar(10)
End Time column 09:45:30(hh:mm:ss) varchar(8)
1]We want to create a view for Report Users So that they could just select
the other columns and difference in start Date/time to End Date /Time.
2]The Difference in time should be based on Business Day. i.e.
Saturday & Sunday should be excluded. &
3]Each business day is defined as 8.00 am to 5 .00pm
( 1 Business day = 9 hrs)
4]The time is stored as 24 hour clock.
For e.g.
Start Date Start TimeEnd Date End Time Desired Output
04/01/2002 10:00:0004/01/2002 3:30:00 0 Days 5:30 Hrs
04/01/2002 16:00:0004/02/2002 10:45:00 0 Days 3:45 Hrs
04/01/2002 09:00:0004/03/2002 10:45:00 2 Days 1:45 Hrs
04/05/2002 16:30:0004/08/2002 9:45:00 0 Days 2:15 Hrs******
******In last case as 04/06/2002 & 04/07/2002 were Saturday & Sunday ,hence
excluded.So the difference of time is just 2:15 hrs
Can you please give me a suggestion/pseudo code/logic on how I might do this?
We cannot use cursors as we want to make a view.This is for SQL Server 2000
Hi,
I have a BETWEEN query (at least I think that's what it will need), but with a difference.
Normally you would specific a field which was BETWEEN two set variables
ie. {fieldname} BETWEEN 1 AND 3
However I need mine the other way round.
I have a series of records which have a startdate and enddate held against them.
When a user submits a new record to the db, I need it to check that the starting and ending date range doesn't overlap any of the existing start-end date ranges that exist.
In order to do that I'm trying to build a query which takes in the incoming startdate variable and see if that is within any of the existing start-date-enddate dates ranges of the existing records, and then same for the incoming endate. I actually want the ones that are going to cause a problem to appear...
I;m sure there is a pretty easy way of coding this, but I'm struggling to get my head round it.
Anyone offer any advice?
how do you determine the date difference between the current record and the next record. i want to use the enddate on record 1 and calulate the time it took the next occurance to happen (start time) on record 2? and so forth.
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''?
I have here a query which delivers me the user data from the last month. The problem what I have is, if employee have more then one rows in this month, they will be also deliverd. But exactly this is not needed. I need only the last record from last month.
SELECT a.FIRMA,
a.PSNR,
a.FELDNR,
a.PFLFDNR,
a.INHALT AS FTE,
a.PFGLTAB,
[Code] ....
Result from Query at the moment:
FIRMAPSNRFELDNRPFLFDNRFTEPFGLTABPFGLTBISKSTNRPSPERSNR
1351022112013082820320101000084000895
14702220,912014100120320101000079000057
1166022112011010120320101000077000543
1364022112013100120150114000072000920
136402220,942015011520321231000072000920
As you can see, PSNR=364 has two rows and i need only the row from last month and last date.Maybe we can use Field PFLFDNR as counter. get only one row for every employee?
like this
FIRMAPSNRFELDNRPFLFDNRFTEPFGLTABPFGLTBISKSTNRPSPERSNR
1351022112013082820320101000084000895
14702220,912014100120320101000079000057
1166022112011010120320101000077000543
136402220,942015011520321231000072000920
How would you create a column which displays the difference of two other columns in SQL? For example, column C=column A – column B.
My idea is that when ever data enters column A and column B, column C should show the difference of the two automatically. A, B and C are datetime columns. I'm looking at setting up an 'after' trigger on the table. Is there a better approach? Thanks.
Hi,
I have a table VisitLog
pkey customer_id
pkey user_id
visit_date
visit_note
Given both id's, I need a report that will list the visit interval. Assuming the rows are entered in ordered.
the result should just display a single column with rows filled with date difference.
Date Format in example below is dd/MM/yyyy
1, 1, '01/01/07', null
1, 1, '03/01/07', null
1, 1, '08/01/07', null
The result should list difference in days.
2
5
Is it possible to do this in a SELECT Statement?
Thanks,
Max
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.
I am having a table where i have the following columns where the date format is dd/mm/yyyy
Purchase Description From_Date To_Date------------------------------- --------------- ----------------Desktop 2/2/2007 2/3/2007Mouse 2/1/2007 28/1/2007Laptop 5/1/2008 15/3/2008Speaker 4/1/2008 21/1/2008
My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure?
Please provide me with full stored procedure
Thanx in advance
I have two columns in my table. Both the columns contains datetime datatypes. I need to write a stored procedure which will calculate the date difference between the two columns and if it exceeds more than 5 days then that record should get deleted. How to do it?Thanx
View 2 Replies View RelatedThe 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 "