Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Finding Difference Between Two Date Columns And Deleting It


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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Deleting Records Based On The Date Difference Using SP
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 Replies !   View Related
Deleting Records Based On Date Difference
I am having a table where i have the following columns where the date format is dd/mm/yyyy

Purchase DescriptionFrom_DateTo_Date
------------------------- --------
Desktop2/2/20072/3/2007
Mouse2/1/200728/1/2007
Laptop5/1/200815/3/2008
Speaker4/1/200821/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

C.R.P RAJAN

View Replies !   View Related
Finding Time Difference
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 Replies !   View Related
Finding The Difference Between Two Column From Two Different Datasets
Hi

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 Replies !   View Related
Finding Where My Date Falls In Date Range
Hi;

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 Replies !   View Related
Deleting Columns From Table
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 Replies !   View Related
Deleting Columns Easily
All I want to do is delete 1 column from a table. However I do not want to re-create the table without the deleted column and then drop the previous table and then re-name the new one etc. etc.

In other kinds of SQL (EG. Access) you can use :

ALTER TABLE x DROP COLUMN y

Is there an easy way of doing this, as I am sure the long-winded method would result in slow performance?

View Replies !   View Related
Difference Between Xtype And Type Columns In ....
Hi

Difference between xtype and type columns in sysobjects table?

Thanks in advance...

Regards
Magesh

View Replies !   View Related
Finding Columns That Are Intended For Foreign Keys
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

USE RIQDB1
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))
go

thanks
Cm

View Replies !   View Related
Difference Between 2 Columns Key And A Third-party Column With Identity
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,

iTaY.

View Replies !   View Related
Difference Of Two Datetime Columns Caused Overflow At Runtime.
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 Replies !   View Related
Error : Difference Of Two Datetime Columns Caused Overflow At Runtime.
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 Replies !   View Related
Date Difference
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 Replies !   View Related
Date Difference
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

View Replies !   View Related
Date Difference
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

View Replies !   View Related
Date Finding
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

Thanks


Knowledge grows when shared...

View Replies !   View Related
Date Difference
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.

View Replies !   View Related
Identity Columns And Date Columns On Transactional Replication
Hi,

I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.

How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?

It is very important for me to have a mirror image of data (without using clustering servers).

Any help would be appreciated.

Thanks,

-Niraj

 

View Replies !   View Related
Help - Finding The Newest Date Without Using Max()
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 Replies !   View Related
Date Difference In Months
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

View Replies !   View Related
Finding The Expiration Date
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 Replies !   View Related
Finding First And Last Date From Datepart
I'm kinda stumped....
How would one find the first and and last date in a date range defined by a DATEPART, regardless of the parameter?

For Example...
DATEPART(wk,7/31/2000) would return 7/30/2000 and 8/5/2000
DATEPART(qq,7/31/2000) would return 7/1/2000 and 9/30/2000
DATEPART(yy,7/31/2000) would return 1/1/2000 and 12/31/2000

TIA,
Charles

View Replies !   View Related
Date/Time Difference
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

View Replies !   View Related
Date Difference After Midnight
Hello,

I have following four datetime fields in my table:

Time_shipment_arrived, Time_shipment_unloaded, Time_shipment_logged, Time_shipment_finished.

During dataentry, the application only enters the "TIME" part in this field. That is, the fields look like this: "1900-01-01 20:55:48.000".

For one of my audit for 'operation time', i have to find out how long it took from the time the shipment arrived to the time the shipment was finished in minutes. I am using:

datediff(minute, time_shipment_arrived, time_shipment_finished). I get the correct output.

The problem i have is when in this 'operation time', the time passed midnight. i.e.
time_shipment_arrived = 1900-01-01 20:55:48.000
time_shipment_unloaded = 1900-01-01 23:55:48.000
time_shipment_logged = 1900-01-01 00:35:00.000
time_shipment_finished = 1900-01-01 03:35:00.000

Now, when i use above datediff, i get a wrong output.

How can i avoid this issue?

Please help.

Thanks in advance.

J

View Replies !   View Related
Date Difference Between 2 Different Records
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.
 

View Replies !   View Related
Date BETWEEN Query With A Difference?
 

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?

View Replies !   View Related
Finding Designation Of An Employee On A Given Date
Hi all,I have two tablesCREATE TABLE [JEMP] ([EMPID] [int] NOT NULL ,[DESIGID] [int] NULL , -- CURRENT DESIGNATION OF EMPLOYEE[DOB] [smalldatetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [JPRO] ([PromoID] [int] IDENTITY (1, 1) NOT NULL ,[EmpID] [int] NOT NULL ,[EffectiveDate] [smalldatetime] NOT NULL ,[NewDesigID] [int] NOT NULL , -- PROMOTED TO DESIGNATION[DesigID] [int] NULL -- PROMOTED FROM DESIGNATION) ON [PRIMARY]GOINSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(1,1,'1962-03-11 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(2,25,'1980-10-7 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(3,8,'1978-04-05 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(4,7,'1962-07-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(5,22,'1973-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(6,55,'1971-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(7,11,'1973-09-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(8,22,'1975-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(9,22,'1977-02-12 00:00:00')INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(10,23,'1984-07-11 00:00:00')INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(3,'2002-15-11 00:00:00',7,20)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(3,'2003-03-01 00:00:00',8,7)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(4,'2002-01-04 00:00:00',20,22)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(4,'2005-05-01 00:00:00',7,20)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(5,'2001-10-01 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(6,'2001-08-01 00:00:00',55,NULL)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(7,'2003-10-01 00:00:00',11,8)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(8,'2001-09-01 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(9,'2002-01-05 00:00:00',22,23)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(10,'2002-11-01 00:00:00',24,25)INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)VALUES(10,'2003-11-15 00:00:00',23,24)--I wish to find the designation of employee on given date by usingpromotion and master table . I am using the following query to get theresultselect isnull( ( select top 1 newdesigid from JPRO whereempid=1 and effectivedate<'anygivendate' order by effectivedate desc ), (select desigid from empmast where empid=1) )It did give the result but looking for better method to solve this.With regardsJatinder

View Replies !   View Related
Finding Table Modification Date
Is there a way to find out when the last modification or change to data in a table occured ? How about the last change of a view ?

We'd like to be able to extract data to another table based on a view. Then, at a later date, check to see if the first table or view has changed since the last extract. If not, then we don't need to re-extract the data, we know it hasn't changed.

View Replies !   View Related
Show Date Difference Automatically
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.

View Replies !   View Related
How To Calculate A Date Difference In Days
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 Replies !   View Related
Date Difference From A Single Table
 

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

View Replies !   View Related
Finding Last Updated/modified Date
 

Hi,
I am using SQL Server 2000 and have the following questions:
 
1. How do I know the last updated (data) date using system objects or any other method?
2. How do I know the last modified date of a table using system objects or any other method?
3. How do I know when a table is last accessed

 
Any help is appreciated
 
Thanks for your time and help in advance
 
Kumar

View Replies !   View Related
Date Difference Measurement And Weekends / Holidays
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 Replies !   View Related
Date File Difference In Dos Command Script ?
we are creating script file like below on everyday and I need to find difference between previous script and current script. I can use dos(FC ) command to diffentiate between two file. But How I can differentiate for date itenary file which are generating everyday.

e:scriptdes.proc.123000
e:scriptdes.function.123000
e:scriptdes.table.123000
e:scriptdes.proc.122900
e:scriptdes.function.122900
e:scriptdes.table.122900

Anybody has any suugestion for this issue?.
Thanks,
Ravi

View Replies !   View Related
Finding &#39;null&#39; Date Fields With Query?
I know I am missing something basic, here.
I have a date field in a table. The field is 'allowed Nulls'. When a certain thing happens in the program, the date is filled in.
At various times, I need to do a query to find all the rows that have no dates entered.

What do I use in my where clause? SQL server does not like 'where date = null.'

Thanks,
Judith

View Replies !   View Related
Getting Date Difference Along With Day,hours,minutes,seconds
Hi All,
         I want to get the date Difference with Day,hours,minutes,seconds of the given 2 dates.(Say the difference of

12/6/2007 7:00:00 AM, 12/8/2007 8:00:00 AM  as 2 days 1:00:00)
 
 Is there any inbuilt function in SSRS is available to implement this. Or any other way to do this.Please help me with this .Thanks in advance.
 
With Thanks
M.Mahendra
            
 

 

View Replies !   View Related
How To Get Date Difference In Terms Of Year,month,days
 

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 Replies !   View Related
Default Date Format Difference In Enterprise Manager Vs. Query Analyzer
Not sure why SQL Server displays different date formats for the same date in Enterprise Manager query window vs. SQL Query Analyzer.
 

When I see table rows through enterprise manager the date field looks like 3/18/2008 12:30:00 PM

Whereas when I run a query a query in Query Analyser/or through any other application the date is shown in a different format: 2008-03-18 12:30:00.000
 

Is there a setting in SQL Server that can remove the date format inconsistency?

View Replies !   View Related
Deleting Multiple Columns From Multiple Objects
Is there a way to delete from multiple tables/views a column with a specificname? For example, a database has 50 tables and 25 views all have a columnnamed ColumnA. Is it possible to write a simple script that will deleteevery column named ColumnA from the database?Seems to be it would be possible and I can somewhat vision it usingsysobjects but without wanting to spend too much time generating the script(when I could in shorter time manually delete) thought I'd pose the question.Thanks.

View Replies !   View Related
Deleting The Master Table Withour Deleting The Child Tables
Hi
i have to delete the master table data without deleting the child table records,is there any solution for this,  parent table has relation with the child table.
regards
vinod.t.v

View Replies !   View Related
Selecting The Latest Date From Different Columns
I have 6 columns, all with dates within them, i.e.Proposed Start Date 1Proposed Start Date 2Proposed Start Date 3Proposed Finish Date 1Proposed Finish Date 2Proposed Finish Date 3What I need to do is narrow this down into two fields:Start DateFinish DateSo I need to find the newest value from the columns. i.e.If PS1 filled PS2 and PS3 empty, then Start Date = PS1If PS3 empty and PS2 filled, then Start Date = PS2If PS3 filled then PS3and similarly for Proposed Finish Dates.Anyone knew how I can do this.(Maybe the following will help for the programmers out there:If PS3 <> nullThen StartDate = PS3Else if PS2 <> nullThen StartDate = PS2ElseStartDate = PS1End IFJagdip

View Replies !   View Related
Combine 2 Columns To Get Date Time
adate atime
08-21-2007 11:09
08-20-2007 16:49
08-03-2007 00:39

I would like to combine adate with atime to get adatetime

View Replies !   View Related
Average Date (Multiple Columns)
Hi

SQL Server 2000
I have four columns of date in a table say Date1, Date2, Date3, Date4 in table Tab_A.
I want to get average date of four columns for each row.
Please advise how?

Thanks
J a w a d

View Replies !   View Related
Select Specify Date In Btwn Two Columns......
First of all I wants to "Thank You" all of you has been trying to helps me solve some of the problems.

Now, I come across trying to get data records from 3 columns but two columns "SeasonalStartsuppressiondate" & "SeasonalEndSuppressionDate" both contain no year last four digits. How would I select a query have some records that customer is not fall in between vacation time given a particular months Mar/02/ and Nov/04/? I only want record with “Y� fields not in those months. Notice don't have "YEAR" at the end and so, I can't determine it year of 2005 or 2006.

Here's my table and query statement:

Table - Column fields
SeasonalFlag, SeasonalStartSuppDate, SeasonalEndSuppDate
Y ,04/02,11/04
N ,....,....

Query Statement
SELECT SeasonalFlag, SeasonalStartSuppDate, SeasonalEndSuppDate

FROM advodb ----<<<<my sample query>>>>>-----
WHERE (seasonalflag='y'
AND
convert(datetime,seasonalstartsuppdate + '/2007')< getdate()
AND
convert(datetime,seasonalendsuppdate + '/2007') > getdate())


thank you



RV

View Replies !   View Related
Discovering AGE Based On Date Columns
Hello.

I have three INT columns in a table that record the users birth year, month, and day.

BDAY_DAY (INT)
BDAY_YEAR (INT)
BDAY_MONTH (INT)

I'd like to include a function in my query that will return their Age in years based on these three columns.



I found this function on the internets, but I'm not sure how to build a DATETIME object using the three int date columns to pass to the function. If you could help me there it'd be most appriciated.


Create FUNCTION dbo.GetAge (@DOB datetime, @Today Datetime) RETURNS Int
AS
Begin
Declare @Age As Int
Set @Age = Year(@Today) - Year(@DOB)
If Month(@Today) < Month(@DOB)
Set @Age = @Age -1
If Month(@Today) = Month(@DOB) and Day(@Today) < Day(@DOB)
Set @Age = @Age - 1
Return @AGE
End

Usage (how do i pass the three columns into this function??)

SELECT Last_Name, First_Name, ssn, dob
FROM Employee_Data e (nolock)
WHERE Cust_Id = 'Customer1'
and dbo.GetAge(e.Date_Of_Birth, getdate()) >= 21

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved