Finding Difference Between Two Date Columns And Deleting It

Apr 14, 2008

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?

View 2 Replies


Deleting Records Based On The Date Difference Using SP

Mar 27, 2008

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

View 4 Replies View Related

Deleting Records Based On Date Difference

Mar 27, 2008

I am having a table where i have the following columns where the date format is dd/mm/yyyy

Purchase DescriptionFrom_DateTo_Date
------------------------- --------

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


View 1 Replies View Related

Finding Max Of 3 Max Date Columns?

May 13, 2014

how I would work out the max value of 3 max date values. Note the max date values may be null.

Here is my code :

SELECT TD.sro_num, SRO.description, SRO.cust_num,, SRO.sro_type, TD.whse, TD.CostCode, TD.stat_code, TD.[No of days in Status],
SRO.total_cost_lbr, SRO.total_cost_matl, SRO.total_cost_misc, ((SRO.total_cost_lbr) + (SRO.total_cost_matl) + (SRO.total_cost_misc)) AS total,
convert(varchar,MAX(mt.trans_date), 103)as max_matl_transdate, convert(varchar,MAX(lbr.trans_date), 103)as max_lbr_transdate, convert(varchar,MAX(misc.trans_date), 103) as max_misc_transdate


View 2 Replies View Related

Finding Time Difference

Jan 15, 2003

I have a start_datetime column and an end_datetime column that records when a process we run starts and stops. I am trying to write a query that can calculate the difference between the start time and the end time and display it as hh:mm:ss but cannot seem to come up with a solution. Any ideas?

View 2 Replies View Related

Finding The Difference Between Two Column From Two Different Datasets

Nov 21, 2007


I always seem to come back to dataset issues. Nonetheless, here is my problem:

I am retrieving data using a stored procedure that lists the number of bookings per hour and the revenue that generates also per hour. So, you enter a single date in the stored procedure, for example 2001-10-01 and it will bring back 24 rows corresponding to the 24 hours (obviously) and two columns, one displaying the number of bookings and one displaying how much revenue.

Pretty simple so far.

I have a report using SQL Reporting Services that allows users to enter two dates and therefore you get two tables with the relevant dates data. For example, table one has date 1's data and table 2 has as you'd expect date 2's data. Both tables use different datasets.

The reason why I am using different tables and different datasets is that the stored procedure I use only allows the use of one date at a time therefore, I use a second dataset to generate the second date's data.

The problem occurs when I want to calculate a difference between the two date's data, for example have another table that shows the differrence between table one and table two for bookings for each corresponding hour.

Anyone have any ideas as to how I could achieve this?

View 1 Replies View Related

SQL Server 2008 :: Finding Beginning Date From Multiple Lines Of Date Ranges?

Mar 20, 2015

I am trying to find a beginning date from multiple date ranges, for example:

RowNumberidBegin dtEnd Dt

For this id: 0793319, my beginning date is 2011-09-06

408203492011-12-122012-07-03--not a continuous date range

For this id: 0793319, my beginning date is 2012-09-04


For this id: 0820349, my beginning date is 2014-09-02

To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.

View 9 Replies View Related

Finding Most Recent Date From Current Date?

Oct 21, 2013

the requirements are to return person ID with the most recent leaving date from a scheduled dept, who has previously arrived at an unscheduled dept within 7 days, but just now my query shows all the previous leaving dates within 7 days of that unscheduled dept arrival, I only want the last leaving date before the arrival at the unscheduled dept:

So for instance looking at a copy of one person's date below I have:

PersonID Last Dept Arrival Date To Last Dept Leaving Date From Last Dept Next Arrival Date to Unscheduled Dept
======== ================= ========================= =========================== =====================================
0106573268Dept 5 2013-03-01 2013-03-03 2013-03-05
0106573268Dept 6 2013-02-27 2013-02-27 2013-03-05
0106573268dept 2 2013-02-26 2013-02-26 2013-03-05

In the data above I only want to return the first row, which is the most recent leaving date before arrival at an unscheduled dept.

My query is much the same as before except my inline view is looking at the data for last scheduled leaves from depts in my inline view and also the outer query returning all arrivals to the unscheduled dept:

SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept"
,b.leaving_Date AS "leaving Date From Last dept",
a.[arrival Date] as "Next arrival Date to AREA_GH"
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
FROM scheduled_Leaves
where [leaving date] is not null) b
ON a.Person_ID = b.Person_ID


View 11 Replies View Related

Finding Where My Date Falls In Date Range

Oct 25, 2007


We received a Payment from a customer on '10/10/2007 10:30:00'. i am trying to calculate the commission we would receive from that payment. the commission rate can be edited. so i have to find what the commission rate was when that payment was received.

I have a CommisionAudit table that tracks changes in commission rate with the following values.

ID | Commission Change | UpdatedOn
1 | Change from 20->25 | 03/07/2007 09:00:00
2 | Change from 25->35 | 10/09/2007 17:00:00
3 | Change from 35->20 | 01/10/2007 16:00:00
4 | Change from 20->26 | 11/10/2007 10:00:00

with this payment, as the commission rate had been changed on 01/10/2007 it would obviously be 20%(ID 3). But I need to write sql to cover all eventualities i.e. Before the first and after the last. any help would be most welcome.

View 11 Replies View Related

Transact SQL :: Outputting Date Difference Between Two Date

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

Deleting Columns From Table

May 26, 2006

in sql server 2000, I have a table that I have to write a script for to delete several columns in this table.  I am finding that I have to use alter or drop keywords or a combination of the two but not sure because I have not done this before.  I am googling this but finding all kinds of other information that I dont' need to know.
I dont have rights on this table so I cannot do this manually.  I have to create the script and send it on to someone else.
If anyone can provide a good script example that I can use to delete unwanted columns it would be a great thing.  Thanks.

View 1 Replies View Related

Finding Two Columns In All Tables

Oct 29, 2013

I have several tables that have the POLICY_NUMBER and POLICY_DATE_TIME in them.. All the tables with these two columns should have a POLICY_ NUMBER and a corresponding POLICY_DATE_TIME.I would like to find all tables that have POLICY_NUMBER = 123456 but do not have the corresponding POLICY_DATE_TIME..

View 1 Replies View Related

Calculating Difference Between Two Columns

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

T-SQL (SS2K8) :: Finding Maximum Value Out Of 5 Different Columns?

Jun 2, 2011

How can we find maximum value on column level? Suppose we have table A with four columns col1,col2,col3,col4, Now my query look likes this:

Select col1, col2,col3,col4,
(col1 + col2) as addcol1,
(col2 + col3) as addcol2,
(col3 + col4) as addcol3,
Max(addcol1,addcol2,addcol3) as maxvalue
from Table A

I am getting error as max accepts one argument, I cannot use case statement as table is already bulky and it will make my query more expensive.

View 9 Replies View Related

Finding Nullable Columns In All The Tables

Nov 21, 2013

I have 4 particular columns (crt_dt,upd_dt,entity_active and user_idn) in many of the tables in my database. Now i have to find all the tables having four columns mentioned above and cases are

1) if the column is nullable., then it should result 'Y'
2) if the column is not nullable., then it should result 'N'
3)if column is not present., then it should display '-'

View 5 Replies View Related

Find Records Where There Is Difference Between 2 Columns

Jan 24, 2014

I am trying to produce a report in Application express. I want to find records where there is a difference between 2 columns eg

Debt_amount - billed_amount

where debt >billed amount

I only want to see records where the debt is more than the amount billed

View 1 Replies View Related

Difference Between Xtype And Type Columns In ....

Feb 6, 2007


Difference between xtype and type columns in sysobjects table?

Thanks in advance...


View 2 Replies View Related

Finding Columns That Are Intended For Foreign Keys

Apr 24, 2006

Hi Friends,

me again. I am trying to find out if all of my intended foreign keys are actually set as foreign keys programmatically.

Ive stuffed something up, please find my error

SELECT DISTINCT 'Alter table '+ table_name +
' ADD CONSTRAINT DF_'+ table_name + '_' + column_name +
' DEFAULT ' + ''''' FOR '+ column_name FROM Information_schema.columns
WHERE ((column_name Like '%fk%') AND (FROM Information_schema.type = F))


View 9 Replies View Related

Compare 2 Rows To Find The Difference In Columns?

Aug 14, 2014

How would I compare 2 rows to find the difference in the columns?

ID Column1 Column2 Column3
1 Text1 Text4
2 Text1 Text2 Text3

Column1: Text2 New
Column3: Text4 Old Text3 New

View 6 Replies View Related

Difference Between 2 Columns Key And A Third-party Column With Identity

Jul 22, 2007

Hi all,

Just a silly wonder I had a few days ago:

I have a table named 'CustomerOrders' and I ponder between the posibilities of PrimaryKey(s) i can set :

1. CustomerId + OrderID - a two columns key providing exactly what i need (representing the actual relation).

2. CustOrderId - one column key with identity insert (so i could directly delete/update the record).

Which one should I choose and why? Would i gain anything by choosing one over the other?

Thanks in advance,


View 6 Replies View Related

Transact SQL :: Pivot Columns Into Rows With Difference

Sep 22, 2015

Here is the table:

empid lastname firstname title titleofcourtesy ModifiedOROriginal

1 Davis1 Sara CEO Ms. Modified
1 Davis Sara CEO Ms. Original

We need an output like this:







View 2 Replies View Related

Date Finding

Aug 11, 2006

I have a tutorial in which there is a question to find the person who will be celebrating their Birthday within a week.

The table contains : name, dob, sex


Knowledge grows when shared...

View 3 Replies View Related

Difference Of Two Datetime Columns Caused Overflow At Runtime.

Mar 30, 2007

I used this query to get a result

select round(cast(DateDiff(ss, convert(datetime,rf.RECVD_DTTM), convert(datetime,con.ARRIVED_DTTM))/60 as float)/60,2) as LengthOfTime

from customer rf

but i am getting an error ?

"Difference of two datetime columns caused overflow at runtime."

Any idea ?

View 10 Replies View Related

Date Difference

Aug 22, 2003

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

View 1 Replies View Related

Date Difference

Jul 30, 2007

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

how can i get the actual difference

Malathi Rao

View 1 Replies View Related

Date Difference

Oct 1, 2007


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



View 6 Replies View Related

Date Difference

Dec 18, 2007

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.


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

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

View 2 Replies View Related

Finding The Expiration Date

Jul 26, 2006

I have a table containing several date fields in which certain events happened, and I need to be able to find the expiration date for renewal. The expiration dates are at the end of the 6th, 12th, or 24th month after the event occurred, depending on the event.

Is there a way to return in a query the last day of month x following a date field? For instance, if the date '3/12/2006' is stored in the field for a 12-month expiration, I need to return '3/31/2007'.

Any ideas?

View 1 Replies View Related

Finding Earliest Date

Nov 26, 2013

I want to find out the earliest [First_Post_Date] for any parentdid

My query (See below)
Produces the following results
FROM [FDMS].[dbo].[Dim_Outlet]
where ParentID = '878595212886'
Order by ParentID desc


View 1 Replies View Related

Finding Last Day Date Code

Aug 1, 2014

I wanted to determine the date of the last Saturday with respect to the current date so that I could create a temp table with date ranges numbered 1-4 e.g. up to that date, post link below: URL....I now want to make this function that any time I pass it a day name (mon-sun) it will calculate the date of the previous (mon-sun) with respect to the current day. This date will then be the last date in my range.

So for example I run the function and pass it 'Friday' then I want last date in my range to be the 25th of July (today being Friday so need last Friday), I pass it Thursday I want it to give me the 31st of July etc. I need last date of that date but if I call Friday and today is Friday then need last friday; I think this is the trickier part of it.

This calculation I would image would need to use a combination of datediff dateadd etc. but really don't know how to do this dynamically.Currently I have a function that I pass the last date for the date range I want for my weeks range, I also give it the numbere of weeks I want in my range and also pass the date from the query that I want to know what week range it is in.

ALTER FUNCTION [dbo].[daterangeweeknumber]
-- Add the parameters for the function here
@refDate datetime,
@Weeks INT,
@lastdate datetime


But now I want just to be able to pass it the day name - in this instance would be 'Wednesday' and want it to return date ranges with last date as Wed the 30th of July. Reason I want to use day name is that people will be running these reports on a certain day - for instance on Wednesday and last day to be Monday - and this will mean they do not have to keep changing the date in the call to function.

View 2 Replies View Related

Help - Finding The Newest Date Without Using Max()

Sep 18, 2006

Hi,Can anyone help please?select notefield, modifiedonFROM Table1WHERE id = '100426' and(statusfield like '%criteria1%' ORstatusfield like '%criteria2%')Produces a list of records based upon the criteria. I would like to beable to only show the newest dated record, from the modifiedon field.I've tried max(modified) on, but as I am using an aggregate function inthe query I have to use GROUP BY, which notefield does not like as thisis a ntext field.(I get:Server: Msg 306, Level 16, State 2, Line 1The text, ntext, and image data types cannot be compared or sorted,except when using IS NULL or LIKE operator.)Any ideas please?

View 1 Replies View Related

Error : Difference Of Two Datetime Columns Caused Overflow At Runtime.

Sep 23, 2005

At my job is a dts package that is failing in SQL 2005. I am not a SQLexpert. I am just trying to fix. I put the query in Query Analyzerand get this error:(4322 row(s) affected)Server: Msg 535, Level 16, State 1, Line 1Difference of two datetime columns caused overflow at runtime.I am just trying to understand what this means, what I should belooking for and what could be wrong. Here is the query:SELECT i.SerialNumber, '' AS mac_number, DATEDIFF([second], 'Jan 1,1970', s.DateOrdered) AS Support_StartDt, DATEDIFF([second], 'Jan 1,1970',s.Warranty_Enddate) AS Support_EndDt,DATEDIFF([second], 'Jan 1, 1970', c.Registration_Date) ASRegistration_Date, c.FirstName AS enduser_fname,c.LastName AS enduser_lname, c.CompanyName ASenduser_companyname, c.ContactEmail AS enduser_email, c.Address ASenduser_address1,c.Address2 AS enduser_address2, c.City ASenduser_city, c.State AS enduser_state, c.Zip AS enduser_zip,c.WorkPhone AS enduser_phone,c.Fax AS enduser_fax, d.DealerName ASdealer_companyname, d.ContactFirstName AS dealer_fname,d.ContactLastName AS dealer_name,d.Address1 AS dealer_address, d.City ASdealer_city, d.State AS dealer_state, d.Zip AS dealer_zip,d.ContactPhone AS dealer_phone,d.ContactFax AS dealer_fax,ISNULL(SUBSTRING(p.ProductName, 11, LEN(p.ProductName) - 10), 'unknownIWP product') AS product_type, '' AS extra1,'' AS extra2, '' AS extra3, '' AS extra4, '' ASextra5, '' AS extra6, '' AS extra7FROM tblInventory i full outer JOINtblDealers d ON i.DealerID = d.DealerID fullOUTER JOINtblSupport s ON i.InventoryID = s.InventoryIDfull outer JOINtblCustomers c ON s.InventoryID = c.InventoryIDLEFT OUTER JOINtblProducts p ON LEFT(i.SerialNumber,PATINDEX('%-%', i.SerialNumber)) = p.SerialPrefixWHERE i.SerialNumber <> ''Any ideas would be greatly appreciated.

View 2 Replies View Related

Reporting Services :: Calculating Difference Between Columns Within Column Group

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

Copyrights 2005-15, All rights reserved