Format Expressions(converting Seconds To Minutes)
Mar 20, 2007
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 1 Replies
ADVERTISEMENT
Oct 18, 2006
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 1 Replies
View Related
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
Feb 15, 2008
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 3 Replies
View Related
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
Apr 16, 2004
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 3 Replies
View Related
Oct 28, 2005
I need to convert 300.876 to 5:00.876 in classic asp. Can anyone help?
View 1 Replies
View Related
Dec 6, 2007
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 3 Replies
View Related
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
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
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
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
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
Nov 8, 2007
I have the following SQL query where i want thease to be populate to GridView, but the Duration field is in Second format, I want it would be in HH:MM:SS format.
cmd = "select subscriber_id as Subscriber_no,,amount,duration from MyTable" ;
Please help me how to format this within the Query to display in GridView.
View 3 Replies
View Related
Jul 20, 2005
I'm upsizing an Access database. Got the data converted, working onthe front end, converting queries to views, but having troubleconverting queries that use logical expressions like the following:SELECT OrderId,Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItemsFROM OrderDetailsINNER JOIN ItemsON (OrderDetails.ClientId = Items.ClientId)AND (OrderDetails.ItemId = Items.ItemId)WHERE (NOT (SitesCustomerTypeId = 2AND ExpressBackorder =TrueAND OrderUrgency = 1 ))GROUP BY OrderId;Can someone suggest a strategy to achieve the same result, ieOrderId,ReadBackOrderItems that I can use in further joins?Thanks in anticipationTerry Bell
View 5 Replies
View Related
Aug 28, 2007
I'm querying a database table that creates a time stamp in seconds only. I have a starting time of
1099725928 = 11/6/2004 12:25:28 AM. So that if another entry is made 1 second later the time stamp value entered into the table is 1099725929. The front end application does the converstion from the seconds counter to the datetime format. The query I am writing calls information from this table for a different application that does not have the conversion capability. If I know the starting point (I don't want to create a conversion table) which is 1099725928 = 11/6/2004 12:25:28 AM, is it possible to write into my query script a function to convert the seconds value to the correct datetime format? It would need to accurately account for leapyear and even/odd months.
************************************************************************************************
Select Case_ID_, Region, Assigned_To_Group_, Assigned_To_Individual_,
Status, Priority, Category, Type, Item, Affiliate, Hours_to_resolve, Resolved_Time, Assign_Time, Create_Time
From HPD_HelpDesk
Where Region = 'Central Valley'
and Assigned_To_Group_ = 'CVSA Desktop Support'
and Status In (1, 2, 3, 4, 5)
and Priority In (0, 1, 2, 3)
Order by Case_ID_ Desc************************************************************************************************
View 6 Replies
View Related
Aug 4, 2007
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 2 Replies
View Related
Dec 19, 2007
Hi,
I have a set of csv files and a set of Format Specification files for each of the csv files. I need to convert the csv files into another format of csv files as specified in the Format Specification files. All the columns of the input csv files do not have a mapping with the columns of the output csv files. How can I achieve this using SSIS ? This is an urgent requirement. Please reply asap. Thanks.
View 1 Replies
View Related
Mar 10, 2015
I am looking to be able to add 15 minutes to a time value that is in character format. here is sample data:
0530
0545
0600
0615
0630
0645
0700
0715
0730
0745
Whenever there is a leading zero, I need to preserve that as well. Here is an example of what I an looking for:
0545 + 15 = 0600
0600 + 15 = 0615
1345 + 15 = 1400
View 9 Replies
View Related
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
Jun 20, 2015
Please find below my query and result , how to display [Total Service Time ] in HH:Min format (Currently values in minutes)
Query:
SELECT DISTINCT dbo.sectn_dept.sectn_sc AS Customer,
MONTH(dbo.incident.date_logged) AS Month_Number, DATENAME(month,
dbo.incident.date_logged) AS Month, YEAR(dbo.incident.date_logged) AS Year,
dbo.incident.incident_ref PM_ref,
dbo.product.product_n "Product",
[Code] ....
Result:
Need to Display [Total Service Time] in below Format:
But Some values are repeating ....
View 2 Replies
View Related
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
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
Dec 11, 2007
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user))
BEGIN
DROP PROCEDURE InsertTimeCard
END
go
/*********************************************************************************************************
** PROC NAME : InsertTimeCardHoursWorked
**
** AUTHOR : Demetrius Powers
**
** TODO/ISSUES
** ------------------------------------------------------------------------------------
**
**
** MODIFICATIONS
** ------------------------------------------------------------------------------------
** Name Date Comment
** ------------------------------------------------------------------------------------
** Powers 12/11/2007 -Initial Creation
*********************************************************************************************************/
CREATE PROCEDURE InsertTimeCard
@DateCreated DateTime,
@EmployeeID int,
@DateEntered DateTime,
@SerializedXML text,
@Result int output
as
declare @NewTimeCardID int
select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards
-- proc settings
SET NOCOUNT ON
-- local variables
DECLARE @intDoc int
DECLARE @bolOpen bit
SET @bolOpen = 0
--Prepare the XML document to be loaded
EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--The document was prepared so set the boolean indicator so we know to close it if an error occurs.
SET @bolOpen = 1
--Create temp variable to store values inthe XML document
DECLARE @tempXMLTimeCardExpense TABLE
(
TimeCardExpenseID int not null identity(1,1),
TimeCardID int,
ExpenseRptID int,
ExpenseDate datetime,
ProjectID int,
ExpenseDescription nvarchar(510),
ExpenseAmount money,
ExpenseCodeID int,
AttachedRct bit,
SubmittoExpRep bit
)
DECLARE @tempXMLTimeCardWorked TABLE
(
TimeCardDetailID int not null identity(1,1),
TimeCardID int,
DateWorked DateTime,
ProjectID int,
WorkDescription nvarchar(510),
BillableHours float,
BillingRate money,
WorkCodeID int,
Location nvarchar(50)
)
-- begin trans
BEGIN TRANSACTION
insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid)
values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0)
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML
INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2)
WITH ( ExpenseRptID int 'ExpenseRptID',
ExpenseDate datetime 'ExpenseDate',
ProjectID int 'ProjectID',
ExpenseDescription nvarchar(510) 'ExpenseDescription',
ExpenseAmount money 'ExpenseAmount',
ExpenseCodeID int 'ExpenseCodeID',
AttachedRct bit 'AttachedRct',
SubmittoExpRep bit 'SubmittoExpRep')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardExpenses(TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
Values(@NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
select @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
from @tempXMLTimeCardExpense
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- For time worked...
INSERT @tempXMLTimeCardWorked(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
SELECT @NewTimeCardID, DateWorked, ProjectID, WorkDescription, BilliableHours, BillingRate, WorkCodeID, Location
FROM OPENXML(@intDoc, '/ArrayOfTimeCardWorked/TimeCardWorked', 2)
WITH ( DateWorked DateTime 'DateWorked',
ProjectID datetime 'ProjectID',
WorkDescription nvarchar(max) 'WorkDescription',
BilliableHours float 'BilliableHours',
BillingRate money 'BillingRate',
WorkCodeID int 'WorkCodeID',
Location nvarchar(50)'Location')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardHours(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
Values(@NewTimeCardID,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
select @NewTimeCardID ,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location
from @tempXMLTimeCardWorked
-- commit transaction, and exit
COMMIT TRANSACTION
set @Result = @NewTimeCardID
RETURN 0
-- Error Handler
ErrorHandler:
-- see if transaction is open
IF @@TRANCOUNT > 0
BEGIN
-- rollback tran
ROLLBACK TRANSACTION
END
-- set failure values
SET @Result = -1
RETURN -1
go
View 1 Replies
View Related
Jun 14, 2007
Dear all,
i have one table that colum is
164020 ---- that on convert into hh:mm:ss like 16:40:20
advance thanks
View 2 Replies
View Related
Jun 24, 2013
I am using SQL server 2008 R2. I have a query where I have dates in different columns pulled from my database among other things. The client is requiring that the date be formatted as mmddyyyy. The database returns the date value as varchar (8) and formatted as yyyymmdd. I have tried several things to get this to be converted but haven't had any luck. My query is as follows.
select appointments.last_name as 'patient last name', appointments.first_name as 'patient first name', appointments.address_line_1, appointments.address_line_2, appointments.city,
appointments.state, appointments.zip, appointments.home_phone, appointments.appt_nbr, appointments.appt_date, person.date_of_birth,
person.sex, patient.med_rec_nbr, provider_mstr.national_provider_id, provider_mstr.first_name,
[code]....
View 7 Replies
View Related
Jan 23, 2004
Hi and thanx for reading my post..
I have a reg_date field in my MSSQL DB which is formatted like this :
dd.mm.yy tt:mm:ss (eks. 24.12.03 18:00:03)
What i want to do is get the 8 first chars from this string so i end up with only : 24.12.03
Have tried different variations of : convert(char(8) but not sure how i do this really..
Have already searched the net for a solution but had to post it since i didn't find anything useful..
Hope someone can help me out
Best regards
Mirador-/
View 5 Replies
View Related
Nov 16, 2007
hi guys how can I change this format
date|amount
02/25/07| 00.00
02/26/07| 00.00
To
date|amount
02/25/07| 0.00
02/26/07| 0.00
thanks!
View 3 Replies
View Related
Aug 14, 2007
Hello Everyone,
Please guide me in converting the value to date format.
from source i'm getting the value (which is acchally a data value) '20070730'.
I need this value to be in date format '2007/07/30'
Please help me in getting it done.
thank you
View 2 Replies
View Related
May 1, 2008
We will be getting a disk from a company that we have been working with that will have all our data on it in
MS SQL format. The problem is, that we do not have SQL anything here to be able to utilize our data!
Are there any good programs on how to convert this data to something we could use? What programs can we convert this data to?
Any insight appreciated. I'm fairly computer literate - but not with SQL at all.
View 1 Replies
View Related
Sep 21, 2007
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 8 Replies
View Related
Mar 30, 2006
Hi,
I have a function that generates a range of DateTimes, which I then cast to SqlDateTime to compare with SqlDateTime values in a database.
The problem is my converted DateTimes come out in this type of format "6/2/2006 12:00:00 AM"
wheras my SqlDateTimes in the database are in this format "2006-01-18T00:00:00.0000000-12:00"
Any ideas how I can convert the DateTime values to SqlDateTime correctly so that I can compare them? As I said I tried creating a new SqlDateTime object with the DateTime value ie
DateTime dt = new DateTime("");
SqlDateTime sdt = new SqlDateTime(dt);
But that doesn't work correctly, its still not in the format that is in the database.
View 1 Replies
View Related
May 2, 2006
Hello Everyone,
I need help with conversion type. I'm using Visual Basic 6.0 as my frontend and SQL Server 2000 as my backend. There has been existing data in the database. I would like to know how to convert an integer to datetime format. For example:
This is the actual value from the database.
1087912290
1087912327
I'd like to know how to convert it datetime format.
Any help would be greatly appreciated.
Thanks,
Dennis
View 14 Replies
View Related