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.







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

Related Messages:
Convert Seconds To Hours:minutes:seconds
Hi all.If I've got a query which has a field with seconds in it... how will I usethe Convert function to get my field converted to the format: HH:MM:SS ?The field with the seconds in is called: "Diff"Thanks alotRudi

View Replies !   View Related
Adding Hours, Minutes, Seconds (SQL 2000)
Hi There,

I would like to find the sum of a column with a date format of '01:10:10' which is the hours:minutes:seconds from multiple rows.

For instance, "01:50:10" + "01:20:5" = "3:10:15"

Any ideas?

Using SQL 2000

View Replies !   View Related
Converting Value To Days,hours,minutes & Seconds
Hi Guys,

I have data in minutes which is integer value ex 14454.I wanto convert it to days,hours,minutes & seconds.
Is there any simple way?

Regards.

View Replies !   View Related
TimeAttributeType Error When Add Hours, Minutes And Seconds
In BIDS, I created a time dimension using the wizard. Unfortunately, the template used by the wizard does not contain Hours, Minutes and Seconds; so, I added them using the designer. Now, when I try to build, deploy and process I get the error: Unable to find matching TimeAttributeType. Cannot anyone explain why this is happening and what can be done about? Is there a template available somewhere that does create time dimensions with time and not just dates?

View Replies !   View Related
Changing Seconds To Hours And Minutes And Rounding To The Nearest 15 Minute.
I found in another forum that if I take the seconds and divide them by 15 then round up and multiply them by 4 I can get this done, but I can't figure out how to work it into my select statement.  Anyhelp would be greatly appreciated.  dbo.SLPTRANS.TimeSpent is the field I am trying to convert.

SELECT     dbo.SLPTRANS.ClientID, SUM(dbo.SLPTRANS.TransValue) AS Expr1, dbo.SLPTRANS.TimeSpent AS Expr2
FROM         dbo.SLPTRANS INNER JOIN
                      dbo.INVOICE ON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordID
GROUP BY dbo.SLPTRANS.ClientID
HAVING      (dbo.SLPTRANS.ClientID = 405)

View Replies !   View Related
Returning Time Difference As Days/ Hours/ Minutes
I have written a function that returns the number of Days, Hours and minutes from a given number of minutes. On testinf the results are close but not quite there. Can anyone see where I have gone wrong or is there an easier way of doing this? Code is as follows:

CREATE FUNCTION dbo.GetTimeBetweenLong
(@StartTime DateTime, @EndTime DateTime, @CurrentDate DateTime)
RETURNS VarChar(50) AS
BEGIN
DECLARE @TotalTime Numeric
DECLARE @Minutes Numeric
DECLARE @Hours Numeric
DECLARE @Days Numeric
DECLARE @MinutesInDays Numeric

IF @EndTime IS NULL
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @CurrentDate)
SET @Hours = DATEDIFF(Hour, @StartTime, @CurrentDate) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @CurrentDate) - ((@Days * 24)*60) - (@Hours * 60)
END
ELSE
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @EndTime)
SET @Hours = DATEDIFF(Hour, @StartTime, @EndTime) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @EndTime) - ((@Days * 24)*60) - (@Hours * 60)
END

IF(@Days <0)
BEGIN
SET @Days = @Days - @Days - @Days
END

IF (@Hours < 0)
BEGIN
SET @Hours = @Hours - @Hours - @Hours
END

IF (@Minutes <0)
BEGIN
SET @Minutes = @Minutes - @Minutes - @Minutes
END

RETURN CONVERT(nVarChar(10),@Days) + ' Days, ' + CONVERT(nVarChar(5), @Hours) + ' Hours, ' + CONVERT(nVarCHar(5), @Minutes) + ' Mins'

END

View Replies !   View Related
Format An Date Diff Expression To Hours And Minutes
Hello,
I have this expression DATEDIFF(HOUR, startdate,enddate)  which only shows the hours. I need to show the hours and minutes too , exp. 9.17. Any way to convert the expression to do this. The startdate and enddate fields are mm/dd/yyy hh:mms. I am using report builder.

View Replies !   View Related
Converting Decimal Hours To Hours And Minutes
I have a float variable that holds a decimal number of hours.
 
So 1.5 equals 1 hour 30 minutes.
 
I need to change this to the format  1:30
 
Any idea how to do this?

View Replies !   View Related
Got Seconds But Need Minutes
I have a result that comes out in number of seconds, but need to see it converted to minutes and hours and seconds. Is there a convert function that would do this?

Thanks,
Dan

View Replies !   View Related
Convert Seconds To Minutes
I need to convert 300.876 to 5:00.876 in classic asp. Can anyone help?

View Replies !   View Related
Rounding Seconds Up To The Nearest 15 Minutes
I have a field with seconds in it and I need to disply it in hours which I can do by dividing it by 3600, but I am trying to figure out how to round it up to the nearest 15 minutes.  I have tried a couple of things with ROUND and CEILING, but am not getting the right numbers back.  Any help would be greatly appreciated.

View Replies !   View Related
Converting Nvarchar To Minutes:Seconds In MS SQL?
I have a field in nvarchar type. It contains data like 0, :23, 1:57, ... all in minutes and seconds. Now, I need to convert it to MM:SS using query and get the Average of this column. How can I do it? I have tried Avg(Convert(nvarchar(20), [Calling Time], 108)) .. but I got error : The average aggregate operation cannot take a nvarchar data type as an argument.Help!!!! :( 

View Replies !   View Related
Format Expressions(converting Seconds To Minutes)
can any one tell me how to change  seconds to minutes by using if condition in rss...

for example i have value  of  seconds=50, minutes should come like this 50/60=0.833333.... i got  the answer  ... but the problem  is how to roundoff or truncate the minutes to 0.83 or 0.84 from 0.8333333...

Thanks in anticipation

 

raj

 

 

 

View Replies !   View Related
Convert Seconds To Hour And Minutes Format
I have a column of data that is the number of seconds.  I need to format this column into the HH:MMS format.  If there are 130 seconds the second column should read 00:02:10.  How can I accomplish this in report builder or is it even possible in Report builder?  I can use the following formula in SQL but is there a way to do it in Report Builder?
 
 

CONVERT(varchar(6), talktime / 3600) + ':' + RIGHT('0' + CONVERT(varchar(2),

talktime % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), talktime % 60), 2)
 
Thanks

View Replies !   View Related
Days Hours Minutes
I have two field and I need to find the sum I guess of them. One is called the clm_dout (process date) and the other one is clm_rcvd (received Date). These are both date and time fields and it looks like this 2006-03-17 00:00:00.000. I need to create a formula that wiil i guess give me the difference and create it into hour and minutes because I an trying to create a turn around report. this is what I had...

TimeValue({clm_doubt}-{clm_rcvd})

This is not working for me. Can someone give me a suggestion please.

Thanks!

View Replies !   View Related
Hours And Minutes Question
Hi guys,

how to get the Hours and Minutes ago?

I have a column name "DateCreated" which is a DateTime type. I need to get the Hours and Minutes ago

Thank you.

View Replies !   View Related
Calculate Hours And Minutes
Hi
Can anyone help me convert a number to give the result in hours and minutes? For example 195 as 3:15 or 210 as 3:30. We are trying to create a report showing hours and minutes worked without having to export to Excel.

I've had a look around the net and this seems to be quite a difficult function in SQL Server.

Any guidance much appreciated.

View Replies !   View Related
Convert Minutes To Hours
hi,

I want to convert minutes to hours. for example field_minutes=130minutes to 2:10 hours...

select field_minutes from table---> how can I do?

 

View Replies !   View Related
Comparing Dates(Minutes, Hours Etc)
guys - is this a decent query to pull all columns (dateCreate)
that have a timestamp less than five minutes?
i know its simple, but i've never done a date compare with minutes or hours
in sql server
thanks
rik:o



select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,getdate()) <=5



select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,current_timestamp) <=5

View Replies !   View Related
Right Data Type To Store Hours And Minutes
Hello friends
what is the right datatype to store the hours and minutes part in the database?
i found some info which says we have to convert the duration(hrs and min) into minutes and then store
is it the right approach?
Regards
Sara

View Replies !   View Related
Abandon Hours, Minutes Etc From Datetime Datatype
 

Dear all,
 
I need to ignore the hours minutes and seconds elements of a datetime field - I've got a way of doing this in my select statement but it seems to be this can't be the most efficient way!
 
CONVERT(datetime, CONVERT(nchar(10), db.tab.field, 101)) AS date_key

 
Is there a better way?

View Replies !   View Related
Reporting Services Formatting Fields From Whole Minutes To Hours
Dear All,

I have a problem formatting a field in Reporting Services (minutes to hours).

I have a field called duration which stores time in whole minutes only. I can format this into hours within mssql using the following:

cast(sn.duration/60 as varchar(5)) + ':' + RIGHT('0' + cast(sn.duration%60 as varchar(2)), 2)

But I need to have totals and average columns in my report, which means that the data must come through to RS in the minutes format so I can perform the calculations there.

I have the first part (I think!!):

=string.format("{0:0}",Fields!SalesTime.Value / 60) + ":"

But I cannot get the minutes part working!

 

Any help would be gratefully received.

 

Dan

View Replies !   View Related
Custom Code/function To Format Seconds To Hh:mm:ss With Ability To Go Over 24 Hours
 

Hello,
 
I am trying to get this to work - but it only returns minutes & seconds:
 
Function Seconds2mmss(ByVal seconds As Integer) As String
    Dim ss As Integer = seconds Mod 60
    Dim mm As Integer = (seconds - ss) / 60
    Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
 
Can anyone help me out? I am not that familiar with VB.
 
Thanks,
Deb
 

View Replies !   View Related
SQl Server Backup Takes 2 Hours To Run But 12 Minutes After A Server Reboot(?)
Sorry i think i may have posted this in the incorrect forum before - if i have done it again here can someone tell me where i should post this please, thanks:
 
Hi,
we are having problems with a server Intel RZeon 3ghz, 3gb ram running 2003 service pack 2 with a 70gb drive and and 400 gb drive all with adequate free space. There are 6 hard disks in total and i assume operating at least RAID 5. We have SQL2000 server with a few standard sized databases and a connection to one other server.
 
A few months ago the back up of SQL server databases started taking 4- 5 hours when before it took 20 minutes. We had actually lost one of our disks in the RAID array and it before this was spotted by our engineers we reindexed the sql databases and defragged both 70gbC: and D: 400gb drives hoping to correct this slow down. Unfortunately the new disk had not been correctly seated and this was why it was taking 4-5 hours. After fixing the disk the backups took 12 minutes again but then started taking 2-3 hours after a few days. 
 
The reindex/defrag did seem to improve the speed of the backups to 12 minutes (from 20 minutes) when the backup did function correctly (also the sql databases' performance improved). However the backups only take 12 minutes after a server reboot - this can last from only 2, up to 5 backups(days) in a row before a slow down to 2-3 hours and again only a reboot will sort out this problem.
 
NB this intermittent slowdown only occurred after the disk failure.
 
We have tried monitoring SQL server and can find no CPU/RAM intensive clashes or long running jobs interferring with the back up. Does anyone know what might be going on here? and if there are any server monitoring tools that may help us discover what is causing this problem ?
 
thanks for your help
best regards
kevin

View Replies !   View Related
Date - 15 Minutes
how can i get my date minus 15 minutes in sql?
is there a dateadd function?

View Replies !   View Related
How To Subtract Minutes From The Date?
I want to fetch all the records which are 2 minutes older
How can I do it?

View Replies !   View Related
EPOC? Seconds Since 1970 Int Conversion To Date
I have an application that stores dates in what I believe is called EPOC, which is the seconds since Jan. 1st 1970. I am trying to write date related queries to the system and need to convert those dates into dates SQL server recognizes. This is on a 7.0 server and the current dates are stored as integers and are 10 spaces. DOes anyoner have experience writing queries to a system like this? Any help is appreciated. I've searched the site but came up blank...thanks

View Replies !   View Related
Change To &"Total Hours&" Difference
How can I change the output into the total hours between 2 smalldatetime data? TIF


DECLARE @iDAY AS SmallDatetime
SET @iDAY = '2004-08-12 10:05:00'


PRINT @iDAY
PRINT GETDATE()

PRINT GETDATE()-@iDAY
+++++++++++++++
Aug 12 2004 10:05AM
Aug 13 2004 10:05AM
Jan 2 1900 12:00AM <--- I want to get the total hours (i.e. 24)

View Replies !   View Related
Current Date - 72 Hours Function?
Can anyone help me with this, I need to search for all records from the current date - 72 hours. How can this be done?

Thanks,

www.myspace.com/drew22299

View Replies !   View Related
Obtaining Business Hours Based On Start And End Date
I 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 Replies !   View Related
Breaking Down Total Hours Worked Into Day And Evening Hours
I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'

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 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
Why Query Takes 0 Seconds And Stored Procedure Takes 16 Seconds Sql Server 2000
 

I have a Stored Procedure that has a query in it and it take 0 second and then a stored procedure that takes 16 seconds.  From what I can tell they shoul be the same.
 
It doesn't recompile when i run the stored procedure, I checked that.
 

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

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