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

Converting Nvarchar To Minutes:Seconds In MS SQL?

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

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

Convert Seconds To Hour And Minutes Format

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

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

Got Seconds But Need Minutes

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

Convert Seconds To Minutes

Oct 28, 2005

I need to convert 300.876 to 5:00.876 in classic asp. Can anyone help?

View 1 Replies View Related

Rounding Seconds Up To The Nearest 15 Minutes

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

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

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

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

How Can I Use Format As HH:MM:SS From Seconds Within The SQL Query

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

Converting Access Qrys With Logical Expressions

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

SQL Function To Display A Seconds Counter In Datetime Format

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

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

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

Converting Csv Files From One Format To Another Format With Differing Columns

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

SQL Server 2012 :: Add 15 Minutes To A Time In Char Format

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

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

Data Access :: Date Formatting (Convert Minutes Into HH:MM Format)

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

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

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

Valid Expressions Are Constants, Constant Expressions, And (in Some Contexts) Variables. Column Names Are Not Permitted.

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

Converting Time Format

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

Converting Date To Other Format?

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

Need Help Converting Date Format.

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

Converting Data Format

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

Converting The Value To Data Format

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

Converting Data From MS SQL Format

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

Why Query Takes 0 Seconds And Stored Procedure Takes 16 Seconds Sql Server 2000

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

Converting DateTime To SqlDateTime Format

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

Converting Integer To Datetime Format

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







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