i'm get some strange behaviour with the money and float type. with a calculator divide 70 by 47 and multiply by 40 (70 / 47 * 40) and you get 59.5744 (rounded to nearest 4 decimal places). now try this in query analyser with the 70 as a money type and the result as a float:
I need to convert values in a float data type field to that of datetime. The float data type field currently contains values such as 20060927,20060928, etc. Any suggestions?
I have a statement that works in compatibility mode 8 of SQL Server2000 while it fails in 7:declare @P3 intexec sp_prepexec @P3 output, N'@P1 float', N'select custname fromcustomer where custnr = @P1', 12600034custnr is a varchar(15). As long as the float p1 is less than1,000,000 the query returns correct results, but as soon as p1 isequal or greater than 1,000,000 the resultset is empty. I couldfigure, that above 1 million, sp_exec converts the float intoexponential format and compares this format to the string containingfixed format values. As said above, in mode 8 the statement returnscorrect results above 1,000,000.Please don't ask me, why we use float variables - I don't have anyinfluence on the statement - we isolated the statement with theprofiler.We are also currently dependent on the compatibility mode 7.I would be very grateful if you have any ideas, help or other issuesfor me.Thank you,Peter
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
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!
hi all, I have writen a function in which I'm selecting max of varchar datatype column after converting it into integer datatype ,though the where condition of the select statement retrives only numbers which are in string format but I'm getting conversion error for a string value which doesn't satisfy the condition. can any one explain this behaviour? Thanks in advance.
have a SQL server table with a vairbale unitCost of type money and via a stored procedure I am trying to update it. Through asp I capture the value as a string and then ATTEMPT to convert it into a variable of type money before executing the stored procedure as follows.
SqlParameter parameterUnitCost= new SqlParameter("@unitCost", SqlDbType.Money,8); parameterUnitCost.Value = unitCost; myCommand.Parameters.Add(parameterUnitCost);
This gives me an input string error. On the other hand if I change that line to the following, the page works flawlessley:
SqlParameter parameterUnitCost= new SqlParameter("@unitCost", SqlDbType.Money,8); parameterUnitCost.Value = 25.45; myCommand.Parameters.Add(parameterUnitCost);
Hi, Ive imported a data set into SQL. Now on redefining a text field to datetime, most dates that are filled come through okay.
The issue is that there are some empty fields which I'd like for it to stay empty after conversion. Now what happens is that the empty field becomes '01/01/1900' - which is throwing off our queries as we need it to be compared to other date fields.
Is there a way to keep it empty even after the datatype is changed to datetime? Thanks a bunch!
I have a feeling I'm missing something obvious here, but being new toSQL Server is my only excuse!I have a table that holds a variety of data. I am writing a userreport where the user wants to be able to return all data that fallsbetween a start and end date that they enter.The query should therefore be along the lines ofSELECT [some columns]FROM [my_table]WHERE [column holding date] BETWEEN [start date] AND [end date];However, the column holding the date information has been defined, forreasons best known to the designer, as a VARCHAR(10) and the data isin the format dd/mm/yy.I have tried CAST, which simply gave an error. I then tried CONVERTin the form CONVERT(datetime, [column holding date], 3) and the queryruns without error but returns no data, even though I can see there isdata that would meet the criteria in the table.Can anyone help me with a solution to this?ThanksH
I am trying to convert some strings to number datatype. Some numbers are like this. 1,45.45- 21.21- 4,611.20- Is it possible to convert these type of strings to number datatype. Thank you.
Lets say I have a table named Projects with the data below.Y07001Y07002Y07003Y07010Y07011I want to pick up the last 3 numbers and add a one to it so. Declare @Count as varchar(3) SELECT Top 1 @Count = Right(Project, 3)+1 FROM Projects Order by ID desc SELECT @Count This is what I am using. It picks up the last entry, add a one to it and gives me 12 in return. What i want is 012, not 12
I am trying to insert data from a web form to a SQL Database. I am receiving the following error: {"String was not recognized as a valid Boolean."} I am also receiving a similar error for text boxes that have dates. Below is the code that I am using: <asp:SqlDataSource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:ConnMktProjReq %>" selectcommand="SELECT LoanRepName,Branch,CurrentDate,ReqDueDate,ProofByEmail,ProofByEmail,FaxNumber,ProjectExplanation,PrintQuantity,PDFDisc,PDFEmail,LoanRepEmail FROM MktProjReq" insertcommand="INSERT INTO MktProjReq(LoanRepName, Branch, CurrentDate, ReqDueDate, ProofByEmail, ProofByEmail, FaxNumber, ProjectExplanation, PrintQuantity, PDFDisc, PDFEmail, LoanRepEmail) VALUES (@RepName, @BranchName, @Date, @DueDate, @ByEmail, @ByFax, @Fax, @ProjExp, @PrintQty, @Disc, @Email, @RepEmail)"> <InsertParameters> <asp:FormParameter Name="RepName" FormField="LoanRepNameBox"/> <asp:FormParameter Name="BranchName" FormField="BranchList"/> <asp:FormParameter Name="Date" FormField="CurrentDateBox" Type="DateTime"/> <asp:FormParameter Name="DueDate" FormField="ReqDueDateBox" Type="DateTime"/> <asp:FormParameter Name="ByEmail" FormField="ProofByEmailCheckbox" Type="boolean"/> <asp:FormParameter Name="ByFax" FormField="ProofByFaxCheckbox" Type="boolean"/> <asp:FormParameter Name="Fax" FormField="FaxNumberBox"/> <asp:FormParameter Name="ProjExp" FormField="ProjectExplanationBox"/> <asp:FormParameter Name="PrintQty" FormField="PrintQuantityBox"/> <asp:FormParameter Name="Disc" FormField="PDFByDiscCheckbox" Type="boolean"/><asp:FormParameter Name="Email" FormField="PDFByFaxCheckbox" Type="boolean"/> <asp:FormParameter Name="RepEmail" FormField="LoanRepEmailBox"/> </InsertParameters> </asp:SqlDataSource>protected void Button1_Click(object sender, EventArgs e) { SqlDataSource1.Insert(); } I have been searching forums for parsing data, but I haven't found anything that works. Can anyone provide guidance. Thank you, Paul
I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.
i have a flat file with string column 20060616 . i am converting the string to date (Oracle) i am using the derived column with the following experssion .
while executing the package its failing and throwing the following error
[Derived Column [73]] Error: The "component "Derived Column" (73)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "JOINDDATE" (124)" specifies failure on error. An error occurred on the specified object of the specified component.
I have been trying to read a flat file which has a birthdate field. The field is string and the format is "011594". I have been trying to convert it to a date field in the following format "01/15/1994". Using the forum I was able to write the following expression but I am still keep getting error messages.
I also noticed few records are missing birthdate and few do not have the complete birthdate which means some birthdates are missing year and some are missing month. For example, some are completely missing and some are partially missing, like " " or "0312 ". Could this be the reason I was getting error message since the following expression does not include null and incomplete date of birth?
[Derived Column [1014]] Error: An error occurred while attempting to perform a type cast.
[Derived Column [1014]] Error: The "component "Derived Column" (1014)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "DOB" (2292)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The ProcessInput method on component "Derived Column" (1014) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.
Hi, I'm making a webapplication in C# with MSSQL as database. I've created a form to do an advanced search on books. The user can type in name, author, .... and he can also mark 2 dates from to Calendar objects (I made sure date one can not be bigger than date 2). I'm using smalldatetime as DBtype. The 2 selected values are DateTime in asp. The results are shown in a gridview. Since I added the feature I keep getting the same error and I can't find where it is. Here's some code: 1 public List GetBooks2(string invB,string titelB, string auteursB, string taalB, string uitgeverijB, string jaarB, string keywordsB, string categorieB, string standplaatsB, string ISBN,DateTime datum1, DateTime datum2, string sortExpression) 2 { //this is my method for the advanced search 3 using (SqlConnection oConn = new SqlConnection(_connectionString)) 4 { 5 string strSql = "select * FROM Boek where 1 = 1"; 6 if (!String.IsNullOrEmpty(invB)) strSql += " and (inventaris_nr like @invB)"; 7 if (!String.IsNullOrEmpty(titelB)) strSql += " and (titel like @titelB)"; 8 if (!String.IsNullOrEmpty(auteursB)) strSql += " and (auteurs like @auteursB)"; 9 if (!String.IsNullOrEmpty(uitgeverijB)) strSql += " and (uitgeverij like @uitgeverijB)"; 10 if (!String.IsNullOrEmpty(ISBN)) strSql += " and (ISBN10 like @ISBN or ISBN13 like @ISBN)"; 11 if (!String.IsNullOrEmpty(standplaatsB)) strSql += " and (standplaats like @standplaatsB)"; 12 if (!String.IsNullOrEmpty(jaarB)) strSql += " and (jaartal like @jaarB)"; 13 if (!String.IsNullOrEmpty(keywordsB)) strSql += " and (keywords like @keywordsB)"; 14 if (!String.IsNullOrEmpty(taalB)) 15 if (taalB == "Andere") 16 strSql += " and (taal NOT IN ('nederlands', 'frans', 'engels', 'spaans', 'italiaans', 'duits'))"; 17 if (taalB == "--Geen voorkeur--") 18 strSql += ""; 19 else 20 strSql += " and (taal like @taalB)"; 21 22 if (!String.IsNullOrEmpty(categorieB)) 23 if (categorieB == "--Selecteer een categorie--") 24 strSql += ""; 25 else 26 strSql += " and (categorie like @categorieB)"; 27 28 if (datum1 == null) 29 strSql += ""; 30 else 31 if (datum2 != null) 32 { 33 34 strSql+=" and datumB between @datum1 and @datum2"; 35 } 36 else strSql+=""; 37 if (!String.IsNullOrEmpty(sortBLOCKED EXPRESSION 38 strSql += " order by " + sortExpression; 39 else 40 strSql += " order by id"; 41 42 SqlCommand oCmd = new SqlCommand(strSql, oConn); 43 oCmd.Parameters.Add(new SqlParameter("@invB", "%" + invB + "%")); 44 oCmd.Parameters.Add(new SqlParameter("@titelB", "%" + titelB + "%")); 45 oCmd.Parameters.Add(new SqlParameter("@auteursB", "%" + auteursB + "%")); 46 oCmd.Parameters.Add(new SqlParameter("@taalB", "%" + taalB + "%")); 47 oCmd.Parameters.Add(new SqlParameter("@uitgeverijB", "%" + uitgeverijB + "%")); 48 oCmd.Parameters.Add(new SqlParameter("@jaarB", "%" + jaarB + "%")); 49 oCmd.Parameters.Add(new SqlParameter("@keywordsB", "%" + keywordsB + "%")); 50 oCmd.Parameters.Add(new SqlParameter("@categorieB", categorieB )); 51 oCmd.Parameters.Add(new SqlParameter("@standplaatsB", "%" + standplaatsB + "%")); 52 oCmd.Parameters.Add(new SqlParameter("@ISBN", "%" + ISBN + "%")); 53 oCmd.Parameters.Add(new SqlParameter("@datum1", "%" + datum1 + "%")); 54 oCmd.Parameters.Add(new SqlParameter("@datum2", "%" + datum2 + "%")); 55 56 57 oConn.Open(); 58 SqlDataReader oReader = oCmd.ExecuteReader(); 59 List boeken = GetBoekCollectionFromReader(oReader); 60 oReader.Close(); 61 return boeken; 62 } 63 }
I think that that method is correct, not sure though... The code for GetBoekCollectionFromReader(oReader) is this=1 protected List GetBoekCollectionFromReader(IDataReader oReader) 2 { 3 List boeken= new List(); 4 while (oReader.Read()) //THIS IS WHERE THE ERROR APPEARS 5 { 6 boeken.Add(GetBoekFromReader(oReader)); 7 8 } 9 return boeken; 10 }
That's the method where the error appears... Where should I place a breakpoint to get the exact location? To make sure all methods in this code are explained, here's the code for GetBoekFromReader(oReader))= 1 protected Boek GetBoekFromReader(IDataRecord oReader) 2 { 3 Boek boek = new Boek(); 4 boek.idB= (int)oReader["id"];//id auto generated dus verplicht 5 if(oReader["inventaris_nr"] != DBNull.Value) 6 boek.Inventaris_nrB = (string)oReader["inventaris_nr"]; 7 if (oReader["auteurs"] != DBNull.Value) 8 boek.AuteursB = (string)oReader["auteurs"]; 9 boek.TitelB = (string)oReader["titel"];//titel verplicht 10 if (oReader["taal"] != DBNull.Value) 11 boek.TaalB = (string)oReader["taal"]; 12 if (oReader["uitgeverij"] != DBNull.Value) 13 boek.UitgeverijB = (string)oReader["uitgeverij"]; 14 if (oReader["aantal_p"] != DBNull.Value) 15 boek.Aantal_pB = (string)oReader["aantal_p"]; 16 if (oReader["jaartal"] != DBNull.Value) 17 boek.JaartalB = (int)oReader["jaartal"]; 18 if (oReader["keywords"] != DBNull.Value) 19 boek.KeywordsB = (string)oReader["keywords"]; 20 if (oReader["categorie"] != DBNull.Value) 21 boek.CategorieB = (string)oReader["categorie"]; 22 if (oReader["standplaats"] != DBNull.Value) 23 boek.StandplaatsB = (string)oReader["standplaats"]; 24 if (oReader["ISBN13"] != DBNull.Value) 25 boek.ISBN13 = (string)oReader["ISBN13"]; 26 if (oReader["ISBN10"] != DBNull.Value) 27 boek.ISBN10 = (string)oReader["ISBN10"]; 28 if (oReader["URL"] != DBNull.Value) 29 boek.UrlB = (string)oReader["URL"]; 30 if (oReader["username"] != DBNull.Value) 31 boek.UsernameB = (string)oReader["username"]; 32 if (oReader["passwoord"] != DBNull.Value) 33 boek.PasswoordB = (string)oReader["passwoord"]; 34 if (oReader["datumB"] != DBNull.Value) 35 boek.DatumBoek = (DateTime)oReader["datumB"]; 36 if (oReader["status"] != DBNull.Value) 37 boek.StatusB = (string)oReader["status"]; 38 39 40 return boek; 41 }
Conversion failed when converting datetime from character string. That's the error I get by the way. It also appears when I do a search and I don't use the date function... for example when I only fill in the title textbox and I don't select any dates from the calendars... Sorry for the long post, but I think it's the only way to get a clear view on it...
I have a web page which executes a stored procedure with several parameters. On execution I get an error for the following piece of code. I will be passing null value to the stroed procedure for this parameter "ActiveDate". Dim parameterActivedate As SqlParameter = objCommand.Parameters.Add("@Activedate", SqlDbType.DateTime) parameterActivedate.Value = "" The error is: System.InvalidCastException: Conversion from string "" to type 'Date' is not valid. at Microsoft.VisualBasic.CompilerServices.Conversions.ToDate(String Value) at webpage.Do_Update(Object Sender, EventArgs e)
I am having an issue with a Report in SSRS against a Sybase db.
The dataset for the report is a Sybase stored proc which is passed a parameter (varchar). When I run the proc in Sybase it runs fine, when I run it in SSRS in the data tab it runs fine, but when I try to preview the report, or run from IE after deploying it I get the following error:
An error has occurred during report processing. (rsProcessingAborted) Get Online Help Query execution failed for data set 'Table_1'. (rsErrorExecutingCommand) Get Online Help The given type name was unrecognized
Looks like it is an error converting the string in SSRS to a varchar in Sybase, but I don't know how to fix it.
If I create a table with an identity (int) column and the varchar column, join this to the proc and pass the int as the parameter the report works fine!! (This is not an option in production though.)
I am using SybaseASE OLE DB Provider Version 02.70.0032
Conversion failed when converting datetime from character string. I am getting this error while inserting values in the DB from my application. I have done insertion before too , but i never receive this strange error but when I insert the same thing manually then i didn't get this error ...
The format I used is also yyyy-mm-dd then how to solve this error?????????
Trying to get this query to work, converting a binary version string to human readable output but somehow it doesn't work?
/* Version number binary from daily registy */ DECLARE @VERSION_STRING VARBINARY(16) = 0x4D5544532556564C5B504C552D675B; /* Inline Tally for parsing the binary string */ ;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N)) ,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
I'm very new to SSIS so please be patient and very detailed. Thank you in advance!
I have a SQL Table (tblImporData) with 2 columns: "Data" (varchar(40)) and "AccountNumber" (varchar(7)). I need to take the information in the "Data" column and parse it out into 3 different fields into a new table. I created the new table (tblExportData) with the following fields and data types: AcctNumber (varchar(7)), SiteName(nvarchar(10)), Balance (numeric(8,2)), and Active (nvarchar(1)).
Example of Data Field that I'm breaking up: 01Binford 001999Y
I've created a SSIS Package and in the dataflow I have an OLE DB Source going into a Derived Column. The derived column has the following information to split the data.
Derived Name Derived Column Expression Data Type Ln AcctNumber Replace 'AcctNumber' AcctNumber String[DT_STR] 7 SiteName <add as new column> SUBSTRING(Data,3,10) Unicode string 10 Balance <add as new column> SUBSTRING(Data,13,6) Unicode String 6 Active <add as new column> SUBSTRING(Data,19,1) Unicode String 1
So it should split my example into the following: AcctNumber SiteName Balance Active 12345 Binford 001999 Y
Everything works fine if the table I'm dumping the information to in SQL has varchar data types for all columns. But I need to convert my balance field from 001999 to 19.99. I tried putting a data conversion transform in that has the input column = Balance, Output Alias = Balance, Data Type = numeric [DT_NUMERIC], Precision = 8, and Scale = 2. I then changed the "Balance" data type to numeric(8,2) for the SQL table I'm dumping to, and added an OLE DB Destination to that table and mapped the columns.
When I try to run the package I'm getting an error at the Data Conversion that says the following:
[Data Conversion [698]] Error: Data conversion failed while converting column "Balance" (162) to column "Balance" (724). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.". [Data Conversion [698]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Balance" (724)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Balance" (724)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. [DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (698) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. [DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
So, what am I doing wrong? Is there a different way I should be doing this? As always, your help is appreciated more than you'll ever know!
I use the derived column to convert a string date from a flat file like this: "Jan 02 2005" into a datetime. I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4)) However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails. I have also tried inverting month and day, year/month/day but all with the same result:
Derived Column [73]] Error: The component "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "output column"...
I think the cast fails bacause of the month format. Therefore the only solution would be to code in in a lookup table Jan, 01 | Feb, 02 |... ????
This is probably a simple wuestion but i would appreciate some help I am trying to get and insert the userId into a table called "Orders" I got the user id as Dim CustomerId As Object = Membership.GetUser(User.Identity.Name).ProviderUserKey.ToString The button on click command is worldshop.ShoppingCart.CreateOrder(CustomerId,......................... The Function Has the parameter for Customer Id as follows Dim dbParam_CustomerId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_CustomerId.ParameterName = "@CustomerId" dbParam_CustomerId.Value = CustomerId dbParam_CustomerId.DbType = System.Data.DbType.String command.Parameters.Add(dbParam_CustomerId) When i run this I am getting the error message "Conversion failed when converting from a character string to uniqueidentifier." Can anyone tell me where I am going wrong many thanks Martin
Hi, i have a problem, i keep getting this Error. I want to insert an uniqueidentifier using a textbox, i use the following code to insert. SqlDataSource1.InsertParameters["RWID"] = new Parameter("RWID", TypeCode.String, RWID); SqlDataSource1.Insert(); The databasetype is an uniqueidentifier of that column. Anyone who can help me with this problem?
I am trying to store a unique identifier that is text into a field in a SQL DB that is type uniqueidentifier and I get the follow error message.
Conversion failed when converting from a character string to uniqueidentifier. My Code is shown below: comSQL.Parameters.AddWithValue("@PROPERTYID", Format(Request.QueryString("ID").ToString, "{0:########-####-####-####-############}")) This has worked before but isnt' anymore. Any ideas?
