SQL Server 2008 :: Float Value Converting Before Concatenate Get Rounded?

Mar 30, 2015

In My table formula(varchar) and value(Float) column.

Example

formula --> (A+B)/C

I am trying to create same expression with value But my below example showing value gets rounded if it is big.

I am trying to do,

Declare @a float = 123456.235
SELECT CONVERT( varchar , @a) Result=>123456

any way to keep same value as It is?

View 5 Replies


ADVERTISEMENT

SQL Server 2008 :: Converting SAS Dates In A Float Format To Datetime Values

Mar 17, 2015

I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.

View 0 Replies View Related

SQL Server 2008 :: How To Concatenate Column Value

Jul 29, 2015

I have a column name Classname and I would like to to Concatenate value.

ID ClassName
1 Class A
2 Class B
3 Class C
4 Class D

I need a output in ssrs report with title like (Class A, Class B, Class C, Class D) .

Can I do in SSRS as well ?

I tried join function in ssrs and I am getting #error join(Field!classname,",")

View 5 Replies View Related

SQL Server 2012 :: Float Value Converting To Exponential While Inserting To Varchar Field?

Aug 6, 2015

Am converting varchar field to float and summing using group by and next inserting to varchar field(table).

while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726

My question is why it is converting while inserting ? and how to avoid it.

select query : SUM(CONVERT(float,(rtrim(REPLACE(REPLACE( column1, CHAR(13), ' '), CHAR(10), ' '))))) as AggregateValue

View 4 Replies View Related

SQL Server 2008 :: Concatenate Rows With For XML Logic

Feb 24, 2015

I am not able to understand why just appending blank string in below code removes '<item>' from xml result.Is it converting to varchar datatype ?

This question is just for my understanding

CREATE TABLE #tbl
(id INT IDENTITY(1,1),
item varchar(100))

INSERT #tbl
SELECT 'This'
UNION ALL

[Code] .....

View 4 Replies View Related

SQL Server 2008 :: Concatenate Column Name With Values

Sep 17, 2015

I want to concatenate the column_names with the respective values.

Create table #test1 (Id int,Name varchar(10),Country varchar(10))

insert into #test1 values ( 1,'JOHN','USA'),
(2,'SAM','CANADA'),
(3,'HO','CHINA'),
(4,'RAM','INDIA')

select * from #test1

I have temp table with columns (Id,Name, Country). I want to concatenate column_name with their respective values; means i want column_header with every value for the column and then i want to concatenate multiple columns. I am looking for something like below:

ID values
1,NAME-john-COUNTRY-USA
2,NAME-SAM-COUNTRY-CANADA
3,NAME-HO-COUNTRY-CHINA
4,NAME-RAM-COUNTRY-INDIA

Note: This is just a sample .i am looking something dynamic because i have around 50 tables.i can do with query below but since i don't have static columns and table i am looking for something dynamic

select ID, (a+'-'+B) as Value from (
select ID,'NAME'+'-'+NAME as a,'Country'+'-'+Country as b from #test1

View 2 Replies View Related

SQL Server 2012 :: Moving Identical Data From One Table To Another - Error On Converting To Float

Jun 11, 2014

So I have two tables with similar columns etc, unfortunately the Insert to the History table fails. Noto sure how to format.

Source Table.
CREATE TABLE [dbo].[SKUCURRENT](
[UID] [int] IDENTITY(1,1) NOT NULL,
[SKU] [nvarchar](100) NOT NULL,
[WHSELOC] [nvarchar](50) NOT NULL,
[WEIGHT] [nvarchar](50) NOT NULL,

[Code] ....

View 1 Replies View Related

SQL Server 2008 :: Difference Between Money And (Float Or Decimal) Datatype

Jan 16, 2013

What is the difference between Money and (Float or Decimal) Datatype. If we use Float or Decimal instead of Money, will we loose any functions..?

View 4 Replies View Related

Converting SQLMoney To C# Float ?

Dec 23, 2006

I'm using c# 2.0.
I have a datareader that reads an invoice line item from a table in my SQL Server database. The UnitPrice field is a Money field in SQL server. At the same time I have an invoice class in my application that has a UnitPrice property which is a float.
How do I convert the SQLMoney to a float using my datareader?
Right now I have:
cm.CommandText = "SELECT ItemID, Quantity, UnitPrice, Discount FROM tblInvoiceLineItems";
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
LineItem li = new LineItem();
li.UnitPrice = (float)(double)dr.GetFloat(3);  // cast error here.
lineItems.Add(li);
}
}

View 1 Replies View Related

Converting Varchar To Float

Aug 22, 2007

Hi all,

I'm trying to update a table as follows:
Where in the table i got.
OldCol; a varchar
NewCol; Float


Update tbl set newCol = cast(float,oldcol)
Where isNumeric(oldcol) =1

But it give me the follwong error:
Error converting data type from Varchar to Float.

Any suggestion?

View 12 Replies View Related

Error Converting Varcharto Float

Jul 11, 2002

Declare @FLine varchar(8000)
Declare @FFloat float(8)
value of @FLine =7 Dir(s) 1,023,817,728 bytes free


select @FLine = rtrim(ltrim((select textline from BCPDiskSpaceTempTable where charindex('bytes free', textline) > 0)))

But I get error saying Error Converting Varchar to Float in the above line can anyone tell me why it is so

View 1 Replies View Related

Problem Converting Float Into Nvarchar

Mar 21, 2006

I have a problem converting a float filed into nvarchar.
select cast(sold as varchar(50))
where sold=431597.15 results in 431597 and is ignoring always my decimals.
Do you have any idea how to fix this?
ty

View 1 Replies View Related

Help Needed Converting C Float Value To DBNUMERIC ...

Jul 20, 2005

Hello all,I need to return back to TSQL two numeric values from anExtended Stored Procedure developed in C. As result my Cdll program produces two float values, the TSQL side expectsto have exactly: numeric(10, 5) and numeric.Given that the structure to fill-up is DBNUMERIC definedas:typedef struct dbnumeric // Numeric (and decimal){BYTE precision; // PrecisionBYTE scale; // ScaleBYTE sign; // 1 = Positive, 0 = NegativeBYTE val[MAXNUMERICLEN]; // Padded little endian value} DBNUMERIC;I do not have any clue how to convert the float C datatypeto DBNUMERIC, specifically how to convert the float to anarray of bytes "BYTE val[MAXNUMERICLEN]".Thanks in advance,Best Regards,Giovanni

View 1 Replies View Related

Help With Error Converting Data Type Varchar To Float

Oct 17, 2007

Error converting data type varchar to float.


I am getting this error. Any one there to guide me

View 4 Replies View Related

Error Converting Data Type Varchar To Float.

Mar 25, 2008

hello anyone... i got this message "Error converting data type varchar to float" when i was trying to insert values into table using instead of trigger...

below is my table ClimateData

quote:USE [PVMC Database]
GO
/****** Object: Table [dbo].[ClimateData] Script Date: 03/26/2008 03:04:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ClimateData](
[Climate_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClimateData_Climate_application_id] DEFAULT (newid()),
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[Altitude] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[Climate_type] [varchar](100) COLLATE Latin1_General_CI_AI NULL,
[PV_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_ClimateData_1] PRIMARY KEY CLUSTERED
(
[Climate_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [PVMC Database]
GO
ALTER TABLE [dbo].[ClimateData] WITH CHECK ADD CONSTRAINT [FK_ClimateData_Photovoltaic] FOREIGN KEY([PV_application_id])
REFERENCES [dbo].[Photovoltaic] ([PV_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE


Below is photovoltaic table

quote:USE [PVMC Database]
GO
/****** Object: Table [dbo].[Photovoltaic] Script Date: 03/26/2008 03:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Photovoltaic](
[PV_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Photovoltaic_PV_application_id] DEFAULT (newid()),
[PV_site] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_state] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_type_of_system] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_nominal_power] [float] NULL,
[PV_module] [varchar](150) COLLATE Latin1_General_CI_AI NULL,
[PV_mounting] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_building_type] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_topology] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_new_or_retrofit] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_design] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_construction] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_commissioning_date] [datetime] NULL CONSTRAINT [DF_Photovoltaic_PV_commissioning_date] DEFAULT (getdate()),
[PV_site_photo] [varbinary](max) NULL,
[PV_peak_nominal_rating] [float] NULL,
[User_application_id] [uniqueidentifier] NULL,
[Org_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_Photovoltaic_1] PRIMARY KEY CLUSTERED
(
[PV_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [PVMC Database]
GO
ALTER TABLE [dbo].[Photovoltaic] WITH CHECK ADD CONSTRAINT [FK_Photovoltaic_OrganizationDetail] FOREIGN KEY([Org_application_id])
REFERENCES [dbo].[OrganizationDetail] ([Org_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Photovoltaic] WITH CHECK ADD CONSTRAINT [FK_Photovoltaic_Users] FOREIGN KEY([User_application_id])
REFERENCES [dbo].[Users] ([User_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE

Below also my command for instead of trigger


quote:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE trigger [tr_v_PhotovoltaicClimateData] on [dbo].[v_PhotovoltaicClimateData] instead of insert as
BEGIN

insert Photovoltaic (PV_site, PV_state, PV_type_of_system, PV_nominal_power, PV_module,
PV_mounting)
select distinct inserted.PV_site, inserted.PV_state, inserted.PV_type_of_system,
inserted.PV_nominal_power, inserted.PV_module, inserted.PV_mounting
from inserted left join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
and inserted.PV_state = Photovoltaic.PV_state
and inserted.PV_type_of_system = Photovoltaic.PV_type_of_system
and inserted.PV_nominal_power = Photovoltaic.PV_nominal_power
and inserted.PV_nominal_power = Photovoltaic.PV_module
and inserted.PV_nominal_power = Photovoltaic.PV_mounting
where Photovoltaic.PV_site IS NULL /*** Exclude Organization Detail already in the table ***/

insert ClimateData (Latitude, Longitude, Altitude, Climate_type, PV_application_id)
select distinct inserted.Latitude, inserted.Longitude, inserted.Altitude, inserted.Climate_type,
Photovoltaic.PV_application_id
from inserted inner join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
left join ClimateData on inserted.Latitude = ClimateData.Latitude
and inserted.Longitude = ClimateData.Longitude
and inserted.Altitude = ClimateData.Altitude
and inserted.Climate_type = ClimateData.Climate_type
where ClimateData.Latitude IS NULL /*** Exclude Organization Types already in the table ***/

END -- trigger def




and finally, i hav tried using this command to insert into table v_PhotovoltaicClimateData.. this is the command to insert

quote:insert into v_PhotovoltaicClimateData(PV_site, PV_state, PV_type_of_system, PV_nominal_power,
PV_module, PV_mounting, Latitude, Longitude, Altitude, Climate_type)
values ('CETDEM', 'Sequ Inspection', 'Cheras', 34.44, 'ew', 'ewew',
34343.44, 323.32, 'ewew', 'ewewe')

and i got this error...

quote:Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.



so anyone... plzz help me...

View 19 Replies View Related

Error Converting Data Type Varchar To Float

Mar 28, 2008

Hello everyone... i have some problem with instead of trigger... after insert values into v_PhotovoltaicClimateData, i got this error, Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.

below is my DDL for Photovoltaic table

quote:CREATE TABLE [dbo].[Photovoltaic](
[PV_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Photovoltaic_PV_application_id] DEFAULT (newid()),
[PV_site] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_state] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_type_of_system] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_nominal_power] [float] NULL,
[PV_module] [varchar](150) COLLATE Latin1_General_CI_AI NULL,
[PV_mounting] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_building_type] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_topology] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_new_or_retrofit] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_design] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_construction] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_commissioning_date] [datetime] NULL CONSTRAINT [DF_Photovoltaic_PV_commissioning_date] DEFAULT (getdate()),
[PV_site_photo] [varbinary](max) NULL,
[PV_peak_nominal_rating] [float] NULL,
[User_application_id] [uniqueidentifier] NULL,
[Org_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_Photovoltaic_1] PRIMARY KEY CLUSTERED
(
[PV_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Below is my DDL for ClimateData table

quote:CREATE TABLE [dbo].[ClimateData](
[Climate_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClimateData_Climate_application_id] DEFAULT (newid()),
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[Altitude] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[Climate_type] [varchar](100) COLLATE Latin1_General_CI_AI NULL,
[PV_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_ClimateData_1] PRIMARY KEY CLUSTERED
(
[Climate_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



below is my v_PhotovoltaicClimateData

quote:CREATE VIEW [dbo].[v_PhotovoltaicClimateData]
AS
SELECT dbo.Photovoltaic.PV_site, dbo.Photovoltaic.PV_state, dbo.Photovoltaic.PV_type_of_system, dbo.Photovoltaic.PV_nominal_power,
dbo.Photovoltaic.PV_module, dbo.Photovoltaic.PV_mounting, dbo.ClimateData.Latitude, dbo.ClimateData.Longitude, dbo.ClimateData.Altitude,
dbo.ClimateData.Climate_type
FROM dbo.ClimateData INNER JOIN
dbo.Photovoltaic ON dbo.ClimateData.PV_application_id = dbo.Photovoltaic.PV_application_id

below is my instead of trigger command...

quote:CREATE trigger [tr_v_PhotovoltaicClimateData] on [dbo].[v_PhotovoltaicClimateData] instead of insert as
BEGIN

insert Photovoltaic (PV_site, PV_state, PV_type_of_system, PV_nominal_power, PV_module,
PV_mounting)
select distinct inserted.PV_site, inserted.PV_state, inserted.PV_type_of_system,
inserted.PV_nominal_power, inserted.PV_module, inserted.PV_mounting
from inserted left join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
and inserted.PV_state = Photovoltaic.PV_state
and inserted.PV_type_of_system = Photovoltaic.PV_type_of_system
and inserted.PV_nominal_power = Photovoltaic.PV_nominal_power
and inserted.PV_nominal_power = Photovoltaic.PV_module
and inserted.PV_nominal_power = Photovoltaic.PV_mounting
where Photovoltaic.PV_site IS NULL /*** Exclude Photovoltaic already in the table ***/

insert ClimateData (Latitude, Longitude, Altitude, Climate_type, PV_application_id)
select distinct inserted.Latitude, inserted.Longitude, inserted.Altitude, inserted.Climate_type,
Photovoltaic.PV_application_id
from inserted inner join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
left join ClimateData on inserted.Latitude = ClimateData.Latitude
and inserted.Longitude = ClimateData.Longitude
and inserted.Altitude = ClimateData.Altitude
and inserted.Climate_type = ClimateData.Climate_type
where ClimateData.Latitude IS NULL /*** Exclude Climate Data already in the table ***/

END -- trigger def

this is my commad insert values using instead of trigger that i've created...

quote:insert into v_PhotovoltaicClimateData(PV_site, PV_state, PV_type_of_system, PV_nominal_power,
PV_module, PV_mounting, Latitude, Longitude, Altitude, Climate_type)
values ('CETDEM', 'Sequ Inspection', 'Cheras', 34.44 , 'ew', 'ewew',
34343.44, 323.32, 'ewew', 'ewewe')

after execute this commad, i got this error..
quote:Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.

what should i do??? anyone help me.

View 3 Replies View Related

Error Converting Data Type Varchar To Float

Jul 23, 2005

I have created a stored procedure that contain this field (below) inorder to meet certain criteria. But my problem is when I try to runthe stored procedure I encounter an error "Error converting data typevarchar to float".CASE Final WHEN 0 THEN '--' ELSE Final END AS FinalGradeThe Final field is a float data type.Could anyone teach me how to fix this problem?

View 1 Replies View Related

Transact SQL :: Error Converting Data Type Nvarchar To Float

Sep 24, 2015

In the following code I want to compare 2 values: AccessVal and SQLVal. The values are stored as nvarchars, so I'm isolating the numeric values in a subquery. Notice I'm only selecting 1 row. The commented line, where I compare the values, is throwing the error.

SELECT QA_AutoID, AccessVal, SQLVal
,ROUND(ABS(CONVERT(float, AccessVal,1)),0) as AccessFloat
,ROUND(ABS(CONVERT(float, SQLVal,1)),0) as SQLFloat
FROM QA
WHERE QA_AutoID in (
SELECT TOP 1 QA_AutoID
FROM QA
WHERE ISNUMERIC(SQLVal) = 1 AND ISNUMERIC(AccessVal) = 1
)
--AND ROUND(ABS(CONVERT(float, AccessVal,1)),0) <> ROUND(ABS(CONVERT(float, SQLVal,1)),0)
ORDER BY ROUND(ABS(CONVERT(float, AccessVal,1)),0) DESC
,ROUND(ABS(CONVERT(float, SQLVal,1)),0) DESC

Here is the output with the comparison commented out...

Here's what I get with the comparison line activated:

I've tried converting to numeric, int and bigint instead of float. I've tried CAST instead of CONVERT. Nothing works.

View 13 Replies View Related

Concatenate Column Value In 2008 R2

Oct 7, 2014

Table Item.ItemCategory:

CategoryID Category BelongsTo

35 Electronics 0

39 Digital Camera 35

40 DSLR Lens 39

41 Canon 40

Expected Output:

CategoryID Category BelongsTo

41 canon->Dslr Lens->Digital camera->Electronics 40

Explanation:

As i need to concatenate column value with dependent belongings just like if i pass canon categoryid in where conidition,so desired result should be canon->Dslr Lens->Digital camera->Electronics because As 'canon' belongs to categoryid- 40(DSLR Lens) then 40 belongs to categoryid-39(Digital camera) then 39 belongs to categoryid-35(Electronics) in single select statement...

View 1 Replies View Related

SQL Server 2008 :: Date Format Not Converting To Dd / Mm / Yy

Sep 1, 2015

I have a date field stored in a character type field ( this field is a spare one in an ERP package )I am trying to validate and convert to a date format using the following;

[code="CASE WHEN isdate( arc.UserField1 )= 1 then
CAST( arc.UserField1 as DateTime )
ELSE
CAST( '01/01/1900' as Datetime )
END"] [/code]

The results are ;

Date Result
29/09/08 1900-01-01 00:00:00.000
09/06/15 2015-09-06 00:00:00.000
18/03/13 1900-01-01 00:00:00.000
09/10/14 2014-09-10 00:00:00.000

as you can see - the date 29/09/08 is a valid UK date format - however it fails the validation. 09/06/15 converts to 6th September 2015 ( mm/dd/yy ) format...What do I need to do to force this to identify as dd/mm/yy date format ?

View 9 Replies View Related

SQL Server 2008 :: Converting Datatype Of A Computed Column?

Mar 10, 2015

I have a computed column that I want to cast as decimal.

The two columns it calculates from are both varchar.

Why can I cast the column as INT, but when I try to cast as decimal, I get the following error?

Arithmetic overflow error converting varchar to data type numeric.

what this error means and why I get it only when I want to cast to decimal.

View 8 Replies View Related

SQL Server 2008 :: Conversion Failed When Converting Nvarchar Value To INT

Apr 9, 2015

Declare @specificDB nvarchar(max) = 'AdventureWorksDW2012'
,@tablename nvarchar(max) = 'DimAccount'

declare @localtab INT
SET @localtab = (SELECT Convert(INT,('select count(*) from ' + @specificDB+'.'+'INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = ''BASE TABLE'' AND Table_name = ' + @tablename)))
Print @localtab
Print @localtab

----

Msg 245, Level 16, State 1, Line 8
Conversion failed when converting the nvarchar value 'select count(*) from AdventureWorksDW2012.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE' AND Table_name = DimAccount' to data type int.

View 2 Replies View Related

SQL Server 2008 :: Converting Inline Query Into Join

Oct 16, 2015

I have an inline query that I am trying to convert it into JOIN, results are not coming out the same:

Original query:

SELECT distinct
(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '3') As DcountMy query:

[code]....

View 8 Replies View Related

SQL Server 2008 :: Converting Adjacency List To Hierarchy Table

Feb 10, 2015

I have been trying to convert an existing table that used adjacency list model (parentid,childid) to a table that use hierarchy Id type. So early on, I notice my original data does contains multiple roots. So I took a step to create dummy nodes to ensure all nodes fall into a single root structure. Another important fact is that each child node can have multiple parents.

My original source table contains 22461 records, when running the query below step 2 produces explosive number of records around 175,000+ records. I spent hours study the result and couldn't understand what actually causing this, I ran it against small set of test data I didn't seem the issue caused by child with multiple parents.

select * from SourceTable -- produces 22461 records

--step 1: first, get row number of child records in each parent

SELECT ChildID,ParentID, ROW_NUMBER() OVER (PARTITION BY PARENTID ORDER BY PARENTID) as Num
INTO #RelationshipTmp
FROM SourceTable;

[Code] ....

View 1 Replies View Related

SQL Server 2008 :: Converting Job Duration From Job History And Use It On Email Notifications?

Apr 20, 2015

I am trying to convert the @runtime to hh/mm/ss format as it's currently printing milliseconds.

Declare @runtime varchar(100)
Select @runtime = sum(run_duration)
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
where j.name = 'Accounts_From_App03'
and run_date = CONVERT(varchar(8), getdate(), 112)
Declare @Body varchar(100)
Set @Body = 'The Job completed successfully. Duration:' + @runtime

exec master.dbo.sp_SQLNotify 'App02@email.com', 'team@email.com', 'Scheduled Job Success' ,@Body

View 9 Replies View Related

SQL Server 2008 :: Conversion Failed When Converting Varchar Value - Error

Sep 24, 2015

I am getting the following message when trying to run the below sql

Msg 245, Level 16, State 1, Line 15

Conversion failed when converting the varchar value 'Select COUNT(*) From Adventures.dbo.tblLibrary' to data type int.

Declare @Datestart as Date
Declare @CntBefore as int
Declare @CntNow as int
Declare @Database as varchar(30)
Declare @TableName as varchar(60)

[Code] ....

View 9 Replies View Related

SQL Server 2008 :: Error Converting Data Type Varchar To Numeric

Jun 17, 2015

As per my requirment using below script to convert from varchar to numeric.

declare @stg varchar(50)
set @stg='12,000,00'
select CONVERT(NUMERIC(17,2),@stg)

Getting error : Error converting data type varchar to numeric

View 6 Replies View Related

SQL Server 2008 :: Arithmetic Overflow Error Converting Nvarchar To Data Type Numeric

Oct 13, 2012

When I run the following sql query:

"update table set price = price * 1.1 "..

I get the following error : "Msg 8115, Level 16, State 8, Line 1.. Arithmetic overflow error converting nvarchar to data type numeric. The statement has been terminated."

The table is set to nvarchar, and i am just trying to make the prices go up 10%.

View 9 Replies View Related

SQL Server 2008 :: SSIS Derived Column Transformation Failing On Converting Blank Rows

Jul 30, 2015

I have flat file source from which data is imported to a Sql table.The target column is int and input column is string .The column has some numeric values and some blank values.when I tried to convert into int values it fails.

View 7 Replies View Related

Convert A Binary Float To FLOAT Datatype

Apr 9, 2007

I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.

All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little


Some test codeDECLARE@SomeNumber FLOAT,
@BinFloat BINARY(8)

SELECT@SomeNumber = -185.6125,
@BinFloat = CAST(@SomeNumber AS BINARY(8))

SELECT@SomeNumber AS [Original],
CAST(@SomeNumber AS BINARY(8)) AS [Binary],
dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted],
@SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]

And here is the code for the function.CREATE FUNCTION dbo.fnBinaryFloat2Float
(
@BinaryFloat BINARY(8)
)
RETURNS FLOAT
AS
BEGIN
DECLARE@Part TINYINT,
@PartValue TINYINT,
@Mask TINYINT,
@Mantissa FLOAT,
@Exponent SMALLINT,
@Bit TINYINT,
@Ln2 FLOAT,
@BigValue BIGINT

SELECT@Part = 1,
@Mantissa = 1,
@Bit = 1,
@Ln2 = LOG(2),
@BigValue = CAST(@BinaryFloat AS BIGINT),
@Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)

WHILE @Part <= 8
BEGIN
SELECT@Part = @Part + 1,
@PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
@Mask =CASE WHEN @Part = 2 THEN 8 ELSE 128 END

WHILE @Mask > 0
BEGIN
IF @PartValue & @Mask > 0
SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)

SELECT@Bit = @Bit + 1,
@Mask = @Mask / 2
END
END

RETURNSIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)
END
Thanks again Jeff!


Peter Larsson
Helsingborg, Sweden

View 3 Replies View Related

Converting A Data Type Double To A Type Float Using A Data Adapter &&amp; SSCE 3.5

Feb 13, 2008

Hi,

I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:


this.court0TableAdapter1.Update(this.mycourtsDataSet1.Court0);

this.mycourtsDataSet1.AcceptChanges();

this.court0TableAdapter1.Fill(this.mycourtsDataSet1.Court0);


This did not happen with VS2005/CE3.01.

I have tried changing all references to decimal (or money in CE3.5) without luck.

I'm beginning to think that string may be the way to go!!!!!!!

Can someone shed some light on my problem.

Thanks,

Later:

It's necessary to update the datatable adapter as the 3.01 and 3.5 CE are not compatible.

View 4 Replies View Related

My Decimal Value Is Being Rounded Of When It Gets To The Db

Jan 28, 2004

Hi

I'm inserting a decimal value into my db but it keeps round it of to a single figure. Its the result of time between two times. ie between 1:00 and 2:30 equals 2.5. But it ends up as 2 in the db even though the db field is also decimal. I know by looking at the watch in the bugger that its actually a decimal value just prior to insertion which only leads me to suspect that sql server is doing the rounding of, maybe this is the default behaviour but i need it to store the values as decimal.

View 9 Replies View Related

Decimal Value Is Getting Rounded

Oct 14, 2005

I am writing a simple shopping cart app and the price field is being rounded when inserted into the database.There is a textbox for the user to enter the price.  A stored procedure is used to insert the line item info to the data base table.  The field in the table is formatted as datatype decimal.  Below is the stored procedure and the code that adds the value to the parameter.  Any suggestions on where I am going wrong?<Code>CREATE PROCEDURE [sp_insert_CartDetail] (  @ReferenceNum  [int],  @Item  [varchar](26),  @Desc1  [varchar](27),  @Desc2  [varchar](20),  @Desc3  [varchar](30),  @Desc4  [varchar](30),  @Note  [text],  @Quantity  [int],  @DateOrdered  [datetime],  @SalesPrice  [decimal],  @DistCost  [decimal],  @SalesTaxable  [smallint],  @RequiredDate  [datetime],  @User1  [varchar](12),  @User2  [varchar](12),  @User3  [varchar](12),  @User4  [varchar](12),  @User5  [varchar](12),  @User6  [varchar](12))
AS INSERT INTO [ToolCrib].[dbo].[CartDetail]   (   [ReferenceNum],  [Item],  [Desc1],  [Desc2],  [Desc3],  [Desc4],  [Note],  [Quantity],  [DateOrdered],  [SalesPrice],  [DistCost],  [SalesTaxable],  [RequiredDate],  [User1],  [User2],  [User3],  [User4],  [User5],  [User6])  VALUES  (   @ReferenceNum,  @Item,  @Desc1,  @Desc2,  @Desc3,  @Desc4,  @Note,  @Quantity,  @DateOrdered,  @SalesPrice,  @DistCost,  @SalesTaxable,  @RequiredDate,  @User1,  @User2,  @User3,  @User4,  @User5,  @User6)SqlCmdInsetCartDetail.Parameters("@SalesPrice").Value = CType(txtCost.Text, Decimal)
</code>

View 5 Replies View Related







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