How Can I Show One Decimal On Time (urgent)

Oct 15, 2007

I have a column in a table that has numbers. How can i convert my number from this 2.36 to 2.4
or
2.53 to 3.0

Thanks

Can't Get Decimal To Show Up!

Jul 23, 2005

Hey...I've been cracking head about this one all day, and I'm sure it'san easy answer, but here goes:I have a column entitled Sequ which is defined as a Decimal withPrecision 10 and Scale 5. In an ASP.NET page, I'm performing acalculation which is inserted into the Sequ column. I'm doing aresponse.write, which enables me to see that the numbers are in factbeing calculated correctly complete with a string of numbers after thedecimal, but when I pull the numbers out to use on the page or accessthe table, Sequ lists all numbers as integers. I'm using a storedprocedure in which I'm declaring the @calc parameter (the calculatedvalue which is being inserted) as a Decimal -- I don't know what I'mdoing wrong. Help appreciated...thanks.Erik

View 3 Replies View Related

Show The Decimal Part In Query

Jun 19, 2008

Hi,In the following query the calculator gives value 3.3 but the query returns 3.0 I need to get the decimal part also. i.e I need to get 3.3 as answer from the query. select cast(66/20 as decimal(6,2)) Need help.Thanks

View 3 Replies View Related

Round And Show To 2 Decimal Places?

May 16, 2008

Hi All,

I have a small question to ask. How to round a numeric field upto 2 decimal places, and also show it with 2 decimal places only

For example the following would return 255.88000000000
select round(255.87908765444,2)

How to get 255.88 only?

Pleas help.

Thanks,

Zee

View 3 Replies View Related

(urgent)how Should I Treat Signed Decimal Values In Sql Server

Jul 30, 2007

Hi,
I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly.
But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver that particular column in sql server shows it as 0.00762711864406778
So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778

any help will be appreciated.
Regards
Karen

View 2 Replies View Related

(urgent)how Should I Treat Signed Decimal Values In Sql Server

Jul 30, 2007

Hi,

I want to transfer the data from a excel spread sheet to sql server. I have used the oledb comand and it works fine, and i have also used the sqlbulkcopy and it transfers the data properly.

But in my excel spread sheet i have many columns with data as -0.76 or 0.76 or something like that but when it transfer it to sqlserver that particular column in sql server shows it as 0.00762711864406778

So how i can i tell sql server to display the data as 0.76 instead of 0.00762711864406778

any help will be appreciated.

Regards

Karen

View 7 Replies View Related

Converting Decimal To Time

Nov 18, 2005

how do you convert a numeric to time format if it shows hours but a decimal figure for Minutes. For example if I have hours in decimal format like this

28.5000

but I want to show it in this format:

28:30:00

where 28 = hours, 30 = minutes, 00 = seconds

Thanks.

View 4 Replies View Related

Converting Decimal Time To HH:MM

Apr 28, 2008

In Reporting Services, I have a decimal time field (18,2) in my report which I wish to display as HH:MM. Similarly, I req a sum of total hours and minutes as a summary. Whats the expression for doing this. I've googled far and wide and cannot find a definitve solution.

Thanks.

View 2 Replies View Related

Converting Decimal Value To Time

Mar 3, 2008

I have a field that currently is displaying time in decimal form. I would like to convert it to time format. For example: when it displays 6.46 I would like it to be converted to 00:06:27. Can I do this within reporting services?

View 8 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

SQL Tools :: Decimal To Time Conversion

Sep 7, 2015

How to convert decimal to time .

I am using below code

declare @hour decimal(18,4)='249.2644444444'
select RIGHT('00' + CONVERT(varchar(2),FLOOR(@hour)),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@hour-FLOOR(@hour))*60))),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@hour-FLOOR(@hour))*60)-FLOOR(((@hour-FLOOR(@hour))*60)))*60),2)

For example 1 it works fine but example 2 it throws arithmetic error. Looking for right code which accepts any value and converts to time.

Example 1 : 12.0763888889 as 12:04:00.
Example 2: 249.2644444444
error : Arithmetic overflow error converting numeric to data type varchar.

View 2 Replies View Related

Just Show Time In SQL

Aug 2, 2007

in my MSSQL database my eventTime field is reading: 01/01/1900 12:45:00

how do i remove the date from the start?

View 6 Replies View Related

URGENT:How To Show A Appended String To The Query Results?

Oct 31, 2007

Hello Frds,

This is my query

Select Assessment_Id,Question_Id,Question_short_description,

(isnull(Answer_description,' ') + ' ' + Answer_text) as Answer

from dbo.viewX where assessment_id ='xxxx'

Pleae Note: This query is assigned to a string and passed to the database layer. So, i need the modifications with in this query.
Answer_description and Answer_text are the two different fields,
i need to embed a ":" (colon) between the two data if Answer_text is having any data
else display only answer_description.

Thanks in advance..
Phani.

View 17 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

How To Show Last Time TABLE Was MODIFIED?

May 15, 2000

Hi all,

What is the best way to show when a table was last MODIFIED?

Thanks,
Stan

View 1 Replies View Related

Report To Show Last Time All SQL Reports Were Run

Oct 4, 2007

I was curious to know if there is a way to create a report that will show when the last time all reports on our server were run. We have a lot of reports on our server made by multiple people that may or may not be in use anymore and we're trying to clean this up a bit. I am currently running SQL Server 2000. I realize there is a history that each report has and I'm wondering if it's possible to pull from that if it is stored somewhere that is accessible. Any help or direction would be much appreciated as I am not even sure where to start.....

View 9 Replies View Related

How To Modify Trigger To Show Time Stamp.

Jun 24, 2004

Down below is my tables.
If I want to add time stamp in log_old_val table.
what value in log_old_val and cone in trigger have to be modified?

thanks

************************************************** *****

--main table
> create table test (manufacturer varchar(500), score int)
> insert into test values('Toyota', 1 )
> insert into test values('Toyota', 2)
>
> --logging table
>
> create table log_old_val(manufacturer varchar(500), score int, operation
> varchar(10))
>
> --trigger to log old value into log_old_val table.
> create trigger tr_man on test
> for update,delete
> as
> if @@rowcount = 0
> return
> if exists (select * from inserted)
> if exists (select * from deleted)
> insert into log_old_val
> select manufacturer,score, 'update' from deleted
>
> if exists (select * from deleted)
> if not exists (select * from inserted)
> insert into log_old_val
> select manufacturer,score, 'delete' from deleted
> go

View 1 Replies View Related

Query To Show Time Monday To Sunday?

Jul 17, 2014

I would like to show employee work hours daily from Monday to Sunday. I have managed to write a query to get the total hours.

This is the query giving total hours from and to date.

(select sum(t.timespent)/60 from timeitems t
where t.employee = e.code
and t.project = p.code
and t.ndate >= '2014-07-15'
AND t.ndate <= '2014-07-15') as Hours
from projemplink pl

View 1 Replies View Related

Reporting Services :: Converting Hours In Decimal To Time Format Using Expression

Nov 10, 2015

Is it possible to convert for the following SQL statement into SSRS Expression:

SELECT
RIGHT('00' + CONVERT(VARCHAR(2), FLOOR(SUM(Hours))), 2)
+ ':' + RIGHT('00' + CONVERT(VARCHAR(2), FLOOR((SUM(Hours) - FLOOR(SUM(Hours))) * 60)), 2) + ':' + RIGHT('00' + CONVERT(VARCHAR(2),
FLOOR((SUM(Hours) - FLOOR(SUM(Hours))) * 60 - FLOOR((SUM(Hours) - FLOOR(SUM(Hours))) * 60)) * 60), 2)
FROM TableTime

For example I need SSRS expression for converting 1.75 hours into 01:45:00.

View 3 Replies View Related

Query To Show Total By Shop On Time Range

Aug 28, 2014

How to edit this query to show the total by shop on time range ?

current result.(Time range from 9:00am-23:00pm)
Shop Time_slot cur Amt, yest Amt, Diff Amt, Sales Direction
Abc 10:59 $100 $50 +50 (+)
Abc 11:59 $100 $50 +150 (+)
Abc 12:59 $100 $50 +50 (+)
BBB 11:59 $100 $50 +150 (+)
BBB 12:59 $100 $50 +50 (+)
------------------------------------------------------------------

Desired Result .
Shop Time_slot cur Amt, yest Amt, Diff Amt, Sales Direction
Abc 10:59 $100 $50 +50 (+)
Abc 11:59 $100 $50 +150 (+)
Abc 12:59 $100 $50 +50 (+)
Total $300 $150 +$200 (+)

BBB 11:59 $10 $50 -40 (-)
BBB 12:59 $10 $50 -40 (-)
Total $20 $100 -80 (-)
-----------------------------------------------------------

select shop
,ltrim(str(datepart(hh,yourdatetimefield)))+':00 - '+ltrim(str(datepart(hh,yourdatetimefield)))+':59' as time_span
,sum(case
when datediff(dd,yourdatetimefield,getdate())=0

[Code] .....

View 1 Replies View Related

Table Set Of Records - Show Purchase Time When It Crossed 1000

Feb 10, 2014

I have a table set of records. Its contains some customerID,SportsGoods,Price in different datetime. I want to add customer spent. If crossed 1000 means i have to show purchase time when it is crossed 1000. I need query without while and looping.

Example:

Customer NameGoodsPriceDatePurchased
ABat2501/31/2014
ABall221/31/2014
BCarrom Board4752/2/2014
CTennis Ball502/1/2014
AFootball1502/2/2014
DBat2501/31/2014
BBall221/31/2014
AHockey Bat1252/4/2014
CChess552/4/2014
AVolley Ball552/4/2014

View 9 Replies View Related

Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!

Mar 28, 2007

Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC

Here is the page source

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
	FROM dbo.MAKES INNER JOIN
				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
	ORDER BY count(*) DESC
">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!

(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)

View 4 Replies View Related

Urgent !!!Processing Time

Jun 25, 2003

hii...

Is there anybody out there that can help me on how can I know the processing time taken for one transaction by using SQL Analyzer??

1)For example, I want to update using Analyzer and I would like to know time taken to do this update???

2) How to reduce processing time by using Store Procedures that using cursor?? I have add in some commit statement in my update statement.. Is there any other ways??

Please advise

View 14 Replies View Related

Query Is Abending Because Of Time--Very Urgent

Apr 25, 2000

Hi all,
I am executing query which is using clustered index,no conditions simple select from table...
Table is having so many records, so my query is abending ,pls suggest me what i have to do..it is very urgent..

--raj

View 13 Replies View Related

Point In Time Restore (Urgent Please)

Mar 2, 2002

Hello,

I have lost a table's contents and need to restore them urgently. I backed up the database. I selected the backup set in order to restore it, but the "Point in time restore " was disabled and I couldn't select it to set the time to which to restore.Could anyone tell me the reason behing that?
Another interesting thing is that when I backup a database, I only have two options for backing it up,
1. database complete
2. database differential

Thanks a lot,
Joe

View 2 Replies View Related

Date/Time Overlaps - Urgent.

Jul 20, 2005

Hi,What I have is a booking table, and when updating/inserting I need toensure that there are no date/time overlaps. The problem I'm having isthat while the following script works for events on the same day, itfails miserably when a booking starts on a previous day.I've just spent the last hour going through previous posts and justcan't seem to it right.My DB structure (Sql Server 2000):Table: CollateralBooking-- CBID - int, identity(1, 1)-- CBcPartNumber - varchar(50) (foreign key)-- CBdDateTimeFrom - smalldatetime-- CBdDateTimeTo - smalldatetime-- CBcAlias - varchar(50) (foreign key)My current script (in a stored proc):IF (SELECT COUNT(*) FROM CollateralBookingWHERE (((@CBdDateTimeFrom > CBdDateTimeFrom) AND (@CBdDateTimeFrom < CBdDateTimeTo))OR ((@CBdDateTimeTo > CBdDateTimeFrom) AND (@CBdDateTimeTo < CBdDateTimeTo)))AND (CBcPartNumber = @CBcPartNumber)) <> 0BEGIN-- Return an error.END-- ... Other checks & finally, the insert/update.--Posted via http://dbforums.com

View 2 Replies View Related

Sum Of Time Field In SQL Server 2000 - Very Urgent

Feb 13, 2007

Hello all. I have a nvarchar field in the format dd:hh:mm:ss I need to get a sum of these fields in the same format. So for example if the values are 01:23:00:00 and 00:01:00:00 then the sum of these fields should be 02:00:00:00. Can someone help me? Thanks.

View 12 Replies View Related

VERY VERY URGENT: Jobs Executing After Scheduled Time

Nov 3, 1999

Hi,

I have a very peculiar problem. I have few CommandExec jobs on SQL Server 7.0 which has been scheduled to execute at a specific time. The jobs are executing properly but, the problem is they stop after the completion. i.e after finishing the jobs they still flash a message "Executing the job" they don't flash the message "Not Running" this is happening only to commandExec jobs, I have also few TSQL jobs which are working properly. I tried even giving the duration (start and stop time) but no use. Has anyone encountered this problem? If you have a solution PLS pass it on to me. I am in a very desparate situation.

Thanks in advance.

Robert

View 1 Replies View Related

URGENT!!! Roll Back Db To Point Of Time

Aug 27, 2007

hey guys
i need help urgently
i just ran an update statement without a where statement by mistake
and i need to rollback this changes

i;m runnning sql express sp2
and the database is set to recovery model simple

i hace the mdf and ldf file
the mdf is 1.5 gb ledf is 65 mb and the last changed date is the same time i ran the update statement
so i think the changes are there in the ldf file but i just need to roll back to 1 minute b4 i run the update

plz helpppppppppppp

thx in advance

View 5 Replies View Related

Legacy Database Uses Decimal Data Types.--&> AutomobileTypeId (PK, Decimal(10,0), Not Null) Why Not Integers Instead ?

Sep 26, 2007

I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?

Example: AutomobileTypeId (PK, decimal(10,0), not null)

View 5 Replies View Related

Data Type With Decimal Point For Decimal Values But Not For Whole Integers

Dec 8, 2013

I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?

View 2 Replies View Related

Cast Or Convert Nvarchar With Comma As Decimal Separator To Decimal

Apr 29, 2008

Hello.

My database stores the decimals in Spanish format; "," (comma) as decimal separator.

I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.

Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:

Error converting data type varchar to numeric

Any knows how to resolve.

Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.

View 5 Replies View Related

Converting (casting) From Decimal(24,4) To Decimal(21,4) Data Type Problem

Jul 24, 2006

Hello!

I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?

Thanks for giving me any advice,

Ziga

View 6 Replies View Related