Days Hours Minutes

Apr 18, 2008

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 2 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Display Row As 2 Days Ago / 1 Hours 34 Minutes Ago / 11 Minutes Ago

Apr 21, 2015

My table as data as follow,

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
DROP TABLE [dbo].[table_Data]
GO
/****** Object: Table [dbo].[table_Data] Script Date: 04/21/2015 22:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))

[code].....

View 6 Replies View Related

Converting Value To Days,hours,minutes & Seconds

May 13, 2008

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

Returning Time Difference As Days/ Hours/ Minutes

Sep 16, 2005

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

Converting Decimal Hours To Hours And Minutes

May 13, 2008

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

Transact SQL :: To Display Days Hours Mins Format Based On Business Hours

Apr 22, 2015

I want to display Days Hours Mins Format.

I am Having two columns Like below,

Col1 (in days)    col2 (In Hours : Mins)
3days  4:5 

In this first have to  add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5

From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.

View 6 Replies View Related

Calculate Hours And Minutes

Nov 6, 2006

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

Hours And Minutes Question

Jun 19, 2007

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

Convert Minutes To Hours

Feb 24, 2006

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

Comparing Dates(Minutes, Hours Etc)

Jan 30, 2006

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

Calculate Total Hours / Minutes?

Jul 5, 2014

need to calculate the total hours mintues in sql server and asp.net

View 11 Replies View Related

Decimal Time To Hours Minutes

Feb 3, 2014

I have a series of times in decimal 15 min slots. The data type is float and the field is the followng:

10
10.25
10.5
10.75
11
and so on

I would like to convert that to the hour and 15 minute slot

10:00:00
10:15:00
10:30:00
10:45:00
11:00:00

View 4 Replies View Related

Right Data Type To Store Hours And Minutes

Mar 1, 2007

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

Adding Hours, Minutes, Seconds (SQL 2000)

Feb 21, 2007

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

Getting Date Difference Along With Day,hours,minutes,seconds

Dec 8, 2007

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

Transact SQL :: Getting Time Difference In Hours And Minutes?

Jul 10, 2015

Currently my script is using the below mentioned query to find the time difference.

DATEDIFF(HH,DATEADD(SS,hcreacion,fcreacion) ,DATEADD(SS,hcerrar,fcreacion))

If there is 1 hr 30 minutes time difference, I am getting 2 hours as output. But we need 1.30 as output. is there any way to achieve this?

View 14 Replies View Related

TimeAttributeType Error When Add Hours, Minutes And Seconds

May 26, 2008

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

Abandon Hours, Minutes Etc From Datetime Datatype

Dec 13, 2007



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

SQL Server 2012 :: How To Add Minutes To A Date Within Working Hours Only

Sep 4, 2014

I need to be able to add minutes to a datetime value, which only cover working hours.

I have a holiday table as below:

Examples: (dd/MM/yyyy hh:mm)
Date..........Description
01/01/2015 New Years Day
26/12/2014 Boxing Day
25/12/2014 Christmas Day
25/08/2014 August Bank Holiday

Our Business hours are 08:00-18:00 Mon-Fri (unless the day is in the holiday table)

Start Date............Minute to Add......Expected outcome
01/09/2014 10:00........30...................01/09/2014 10:30
01/09/2014 17:00........65...................02/09/2014 08:05
29/08/2014 17:00........65...................01/09/2014 08:05
22/08/2014 17:00........65...................26/08/2014 08:05
31/08/2014 02:30........65...................01/08/2014 09:05
01/09/2014 19:00........65...................02/08/2014 09:05
01/09/2014 10:00........3005...............08/09/2014 10:05
22/08/2014 17:00........3005...............01/09/2014 17:05

I have tried to create a function to do this (fn_pp_AddMinutesWithinWorkingHours(@StartDate,@Minutes)) but I am unable to come up with a solution which factors in everything correctly.

View 1 Replies View Related

Reporting Services Formatting Fields From Whole Minutes To Hours

Jun 6, 2007

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

Format An Date Diff Expression To Hours And Minutes

Apr 4, 2008

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

Transact SQL :: Convert Time In Hours And Minutes To Decimal

Jul 30, 2008

I am trying to convert hours and minutes to decimal and arrive at a sum of time taken. The column TotalTimeSpent is the diff in hours/mins between the Started and Ended times.

SELECT DATENAME(weekday, Started) AS Day,        C.Category,     ClientCode,Description,    dbo.FormatDateTime(Started, 'HH:MMS 12') as Started,    dbo.FormatDateTime(Ended, 'HH:MMS 12') as Ended, CONVERT(varchar,TimeTaken,108) AS TotalTimeSpentFROM dbo.Journal JLEFT OUTER JOIN dbo.Categories C ON J.CategoryID = C.CategoryIDWHERE--DATENAME(weekday, Started) =@Weekday  AND Started >= @StartDate ORDER BY Day, Category, Started

View 6 Replies View Related

DateDiff - Obtain Total Duration In Hours / Minutes And Seconds

Mar 2, 2014

Given the two datetimes below, what's the best way to obtain the total duration in hours, minutes and seconds (HH:mm:ss)?

Start Time: 2014-03-02 20:55:00.000
End Time: 2014-03-03 07:00:00.000

Duration = 10:05:00

View 6 Replies View Related

Changing Seconds To Hours And Minutes And Rounding To The Nearest 15 Minute.

Dec 6, 2007

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

Hours Days And Months

Oct 4, 2007

This code displays records by the the month they where recorded and displays the months as names (January...)
How can I do the Same for Days (Mon, Tues) and Hours (9.00,10.00)

Select DATENAME(mm,dDate) as Month, count(iStatID) as Total_Po
from ListingStats
Group by DATENAME(mm,dDate)
Order by DATENAME(mm,dDate)

View 8 Replies View Related

Calculate Total Working Hours Between Days

Nov 15, 2013

I have to calculate the total working hours between days, there hours must get automatically round off to nearest value example:

Date :12-05-2013 time : 4:15 will change to 4.00 and if Date :13-05-2013 time: 4:25 then needs to 4.30 and sum the above total hours and results Total : 8.30 hrs.

View 3 Replies View Related

Transact SQL :: Date Difference In The Form Of Days And Hours?

May 21, 2015

declare @siva1 datetime;
declare @siva2 datetime;
set @siva1='2014-03-10 05:02:11'
set @siva2='2014-03-12 23:52:11'

i want output like this 2.18 means 2day 18hours difference how using query

View 6 Replies View Related

Transact SQL :: How To Convert Hours To Days And Months Correctly

Oct 13, 2015

I get a column in hours in a table and when I try to convert it to months and days its giving me incorrect results.

My timehours column in my table has hours recorded timehours value is 5832 and I get results as 11 months and 27 days.

This query is giving me wrong result -select months=(timehours %365)/30,days= (timehours % 365)%30 

View 13 Replies View Related

Transact SQL :: Making Hours Into Months And Days In 2008 R2?

Aug 5, 2015

I have hours which can be like 32.5 and would like to have them in 1 month 2 Days format.

View 2 Replies View Related

SQl Server Backup Takes 2 Hours To Run But 12 Minutes After A Server Reboot(?)

Sep 19, 2007

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

Convert Seconds To Hours:minutes:seconds

Jul 23, 2005

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

Breaking Down Total Hours Worked Into Day And Evening Hours

Sep 21, 2006

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

Remove Weekends And Non Working Days When Calculating Days Difference Between Two Dates

Jan 7, 2014

I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:

ce.enquiry_time represents when the enquiry was logged

(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))

It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved