Hi
there is something strange in the code below:
declare @a money
set @a=1.2365
print cast(@a as varchar(10))
when i run the code in the query analizer , the output will be rounded and always will be displayed with two decimal digits(=1.24)...why?
how can i move the whole number value as an string value to an string variable without this truncation?
Thanks.
kind Regards.
Hi, I'm working on a DB and I'm trying to write some select statements that will display an employee's pay using the CAST and CONVERT functions. However, I can never seem to get commas inserted into the results please help. Here's the SELECT statements, where am I going wrong?
SELECT
'$' + CAST(CONVERT(MONEY, SUM(PROJECT_BUDGET/1.00), 1) AS VARCHAR) AS 'Total Budget'
FROM
PROJECT;
Hi there,I have a table named Action. This table has a column InPrice with datatypenvarchar(12). I want to change its datatype from nvarchar(12) to money. Ibrowsed through the values and removed any dots. Th column now has onlynumeric values (and commas for decimal values such as 105,8). When I try tochange the datatype from nvarchar to money, following mesage is displayed:ADO error: Cannot convert a char value to money. The char value hasincorrect syntax.How can I solve this problem? I cannot figure out which values are causingthis error.Thanks in advance,Burak
View 4 Replies View RelatedI am trying to do some amount calculations.
The amount was declared as nvarchar data type.
I did the following coding. I want to get 2 places right of decimal
but am getting only one place right of decimal.
SELECT
(CAST(EQ_TotQuoteAmnt AS Float(7,2)) -
CAST(EQ_InsFee AS Float(7,2)) - 150) as Inforce_Premium
FROM Quoted
Can someone please help me with the syntax?
Thanks in advance.
M. Khan
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);
any thoughts???
Ok my last formatting question.How can I insert a money value as a padded string in another table?example $1.25 gets inserted to another table as 00000125I want 8 total characters and no decimalanother example would be 4,225.99 becomes 00422599can this be done?thank you!!
View 7 Replies View RelatedHi,We have stored proc name proc_test(str nvarchar(30)). So far this prochas been invoked from a .NET application assuming that only Englishcharacter strings will be passed to it. The calls are likeproc_test('XYZ')We now have a requirement for passing Chinese strings as well. Ratherthan changing the calls throughout the application, we would like tohandle it in the stored procedure so that it treats the string as aunicode string. Can we apply some function to the parameter to convertit to unicode so that we don't have to call with an N prefixed to thestring?Thanks,Yash
View 1 Replies View RelatedI have a special need in a view for a money column to look like money and still be a money datatype. So I need it to look like $100.00 (prefered) or 100.00(can make work).
If I convert like this '$' + CONVERT (NVARCHAR(12), dbo.tblpayments.Amount, 1) it is now a nvarchar and will not work for me.
How can I cast so it is still money? by default the entries look like 100.0000.
They must remain a money datatype.
I have a fixed-width flat file that I'm importing to a SQL table in a data flow task. The first field is an ID field, of length 12, that I'm told I can assume is an INT datatype. Obviously, it's a little silly to shove an integer that can be a max of 10 digits into a 12 digit field... but that's my situation.
I set the columns on the flat file connection manager to be inputColumnWidth of 12, and datatype of DT_I4. When I try to run the package, I get the error (understandably): The data conversion for column "ID" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
What are my options here? I'm going to assume that even though the width is 12 characters, only an integer will appear in those spaces. I attempted to use a derived column and a type cast, but to no avail. Help would be appreciated... thanks!
is there a way to query a varbinary column and print the readable char in query analyzer?
thanks in advance!
Ryan
Hi guys,I'm currently trying to insert image into my SQL db.
I have tried a number of methods that were posted online, and so far
with no luck.My current code reads: Dim conn As New Data.SqlClient.SqlConnection() conn.ConnectionString = ConfigurationManager.ConnectionStrings("MainDBConnection").ToString conn.Open() Dim cmd As New Data.SqlClient.SqlCommand("SP_SAVEImage", conn) cmd.CommandType = Data.CommandType.StoredProcedure Dim sImageName As New Data.SqlClient.SqlParameter("@sImageName", Data.SqlDbType.VarChar, 50) sImageName.Value = sImageName Dim sImageType As New Data.SqlClient.SqlParameter("@sImageType", Data.SqlDbType.VarChar, 50) sImageType.Value = fileType Dim sImageData As New Data.SqlClient.SqlParameter("@sImageData", Data.SqlDbType.Image, uploadedFile.Length) sImageData.Value = uploadedFile cmd.Parameters.Add(sImageName) cmd.Parameters.Add(sImageType) cmd.Parameters.Add(sImageData) Dim reader1 As Data.SqlClient.SqlDataReader reader1 = cmd.ExecuteReaderRunning
through debug, everything runs up until the last line, where an error
is caught saying : Failed to convert parameter value from a
SqlParameter to a String I reckon it's to do with the input sImageData being input as a byte array - but I can't seem to find a way around it. Any help greatly appreciated!!
I have been working on this all day and cannot find an answer
I need to convert a string to an object, a string to object of type Command
Is there anyway to do this?
Thank you all in advance!!!!!!!
I am writing a stored procedure that generates a computername for new workstations. Our naming standards, based on terminal ID's, are as follows:
'R' + X + Y + ZZZ, where 'R' is the letter 'R', X is a hex digit indication a locatoin, Y is a hex digit indicating business line, and ZZZ is a hexadecimal sequential number assigned within each location/busines line, for up to 4096. (This isn't a perfect naming standard, but is an improvement over the one that preceded it and is a requirement for our legacy applications).
I can do everything fine, and calculate the sequential number using an int. Once I have the new number, I am trying to convert the integer to a varbinary, then convert that to a string, then use RIGHT to get the rightmost three characters of the hex string.
set @SEQNUM = 4095
set @HEXSEQNUM = CONVERT(varbinary ,@SEQNUM)
PRINT CONVERT(varbinary ,@SEQNUM) -- displays 0x00000FFE etc.
PRINT 'HEXSEQNUM ' + @HEXSEQNUM -- displays 'HEXSEQNUM'
Any ideas? I am very new to TSQL...
Hello,I try to convert a pseudo datetime string into a date. In Oracle I can doto_date( MyDate, 'yyyymmddhh24miss' ); how I can do this with MS SQL ?thanks and regardsMark
View 2 Replies View RelatedI get the following error when I do a CONVERT(UNIQUEIDENTIFIER, 'guid'), where 'guid' is a properly formatted GUID created in VS2005, inside my INSERT statement:
The data was truncated while converting from one data type to another. [ Name of function(if known) = ]
The column I am inserting into has a datatype of uniqueIdentifier.
This is the query I am running:
INSERT INTO [Table] ([guid], [value1], [value2])
VALUES (CONVERT(UNIQUEIDENTIFIER, 'F067FE20-EC76-44C8-9859-FEF222FBC96D'), 'Test, 'Test')
What am I doing wrong?
Matt
Hi
What's wrong about
SELECT SUM(CASE WHEN [PO Date] BETWEEN CONVERT(Datetime, @FY + '/04/01')
AND CONVERT(Datetime, @FY -1 + '/03/31') THEN Quantity ELSE 0 END) AS Expr1,
[Item No_]
FROM table A
I am trying to write a function to convert a hex string to binary. I would like it in a function so I can use it on aggregate data in queries (instead of having to cursor through the data). So, I write my function:
CREATE FUNCTION HexToBinary (@hexstring char(16)) RETURNS binary(8)
AS
BEGIN
declare @b binary(8)
,@sql nvarchar(255)
SET @sql = N'SET @b = 0x' + @hexstring
EXEC sp_executesql @sql,N'@b binary(8) out',@b output
RETURN @b
END
Then, I try to call my function:
SELECT HexToBinary('E0')
...and I get:
Msg 195, Level 15, State 10, Line 1
'HexToBinary' is not a recognized built-in function name.
However, I can get it to work if I use a slightly different syntax:
declare @b binary(8)
exec @b = HexToBinary 'E0'
select @bAny thoughts as to what might be going on? Obviously, the lower syntax does not help me call this function in queries, which is really my goal.
Hi, I need to convert the values entered into textboxes by the users before I can insert into db.have tried the following. Dim eventnum As string 'tried using integer makes no difference what i declare the variable aseventnum = Convert.ToInt32(txteventnum.Text)This value needs to be inserted into the field event_number wich is datatype int I get the following error message when I try to insert. Conversion failed when converting the varchar value 'eventnum' to data type int.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'eventnum' to data type int.
Source Error:
Line 62: Line 63: ' Execute(query)Line 64: myCommand.ExecuteNonQuery()Line 65: Line 66: 'Close the connection
Source File: C:InetpubloansMemberPagesRequest.aspx.vb Line: 64
Stack Trace:
[SqlException (0x80131904): Conversion failed when converting the varchar value 'eventnum' to data type int.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857242 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734854 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 Request.Button1_Click(Object sender, EventArgs e) in C:InetpubloansMemberPagesRequest.aspx.vb:64 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Hi Gurus,
Here is my problem-:
I have a field in the SQL 2005 database with type varbinary(50). Now when i enter value to this field, I enter a value for example 111111101. The reason to enter such a value in varbinary(50) type is, that this value is meant to grow, as each 1 and 0 in this value is a permission or right my user would have to various features of the web application, I am designing. Now when I open the table to see the data in the database, it shows as <Binary data>. But that's okay, as binary fields are mostly meant to store pictures, files etc. But when I read this value into my ASP.Net code using c#, the value as returned as 069F6BBD. After some research I realized that for decimal value 111111101, the hex value is 069F6BBD.
All I need in my code is the same thing that I entered which is 111111101.
Can someone guide me a lil bit.
Thanks in advance..
VJ
I have a dropdown list thats boudn to a SqlDataSource. The DataSource looks like this:
<asp:SqlDataSource ID="dsProgramList" runat="server" ConnectionString="<%$ ConnectionStrings:csData %>"
SelectCommand="SELECT DISTINCT [Program_Name] +','+ [Begin_Date] AS NAMEandDATE, [Course_ID], [LOC] FROM [ThisTable] WHERE ([LOC] = @LOC)">
<SelectParameters>
Where the dropdownlists text = NAMEandDATE and its value = Course_ID
When I select the LOC from the LOCdropdownlist, the dropdownlist in question updates, and an error "Erro converting datetime from character string" happens?
Any suggestions?
Hi there,
I have the following code:
Dim CityTown As String = Ctype(Request.Querystring("CityTown"), String)
Dim Suburb As String = Ctype(Request.Querystring("Suburb"), String)
Dim SuburbValue As String = Ctype(Request.Querystring("Suburb"), String)
Dim Rooms As String = Ctype(Request.Querystring("Rooms"), String)
Dim Rent As String = Ctype(Request.Querystring("Rent"), String)
Dim DateToday = DateTime.Now
Dim mySQL AS String
If suburbValue = "- All -" Then
mySQL = "SELECT propListID, propListExpires, propBuildType, propRoomNumber, propRentPerWeek, propDescription, propCityTown, propSuburb FROM tblPropertyList WHERE [propCityTown]='" & CityTown & "' AND [propRentPerWeek]<= '" & Rent & "' AND [propRoomNumber]='" & Rooms & "'AND [propListExpires] >='" & DateToday & "' "
However I am having trouble getting the Date to work as part of my SQL String. I keep getting the following error:
Syntax error converting datetime from character string.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.
Source Error:
Line 30: MyDA = New SqlDataAdapter (mySQL, myConn)
Line 31: myDS = New DataSet()
Line 32: myDA.Fill(MyDS)
Line 33:
Line 34: dlPropertyListing.Datasource = MyDS.Tables(0)
Source File: K:detailsdetailspropertylisting.aspx Line: 32
Any ideas how to resolve this?? I do not want to use Parameters in my statement.
Thanks in advance,
TCM
Hi all, first time post so go easy.
I have been trying to find a way of creating a datetime comprised of getdate() and a time that i specify. I am trying to do this as shown below:
Declare @Test datetime
Declare @Test1 Datetime
Set @Test = getdate()
SET @Test1 = CONVERT(datetime, datepart(y, @Test) + datepart(m, @Test)+ datepart(d, @Test)+' 16:30:00',120)
So, i am building a string using datepart and then adding the time at the end. The 120 after the final comma is to define the style (as i am sure you will all know anyway).
When i run this i get the following error:
Msg 245, Level 16, State 1, Line 35
Syntax error converting the varchar value ' 16:30:00' to a column of data type int.
If i take out the colons it runs but brings back the wrong date, the whole date seems to be dependant on the time part at the end.
I am sure this is a really simple problem and i am sorry if i am wasting you time but its late and i just cant see it. Any help would be fantastic.
Can anyone tell me what this error message means and how I can correct it on my statement:
Code:
Server: Msg 241, Level 16, State 1, Line 1Syntax error converting datetime from character string.
Here's my query statement:
Code:
SELECT animalid AS "Animal ID", name AS "Name", categoryid AS "Category ID", DATENAME(MONTH, dateBorn) + ' ' + DATENAME(DAY, dateBorn) + ', ' + DATENAME(YEAR, dateBorn) AS "Date of Birth"FROM animalWHERE categoryid = 'Cat' AND dateBorn = 'May%'
I know it has something to do with: dateBorn = 'May%' because when I took out Quote: AND dateBorn = 'May%' I get results, but I need to narrow it down to list the cats born in May.
I'm looking for some good hints and tips for reprogrammin an old VB module I just found.
Basically what it does, is receive an input parameter (an int), does a select [name row] from Names where Name_id = [input parameter] and turns this into a string if multiplenames appear.
E.g. result set: John, Josh, Jock turns it into string "John Josh Jock".
So its piece of cake creating a stored procedure selecting data on the base of an input parameter. Select X from Y where Z = @input... the trick is, I don't know how to do arrays in TSQL as in VB.
In the VB edition I create an array, load the names into it, I do a count on how many row the select returns and then a simple for... next adding the names to the string.
Any good examples on how to do this in a sql-server stored proc?
Thanks,
Trin
P.S. This is what I have pieced together this far:
CREATE PROCEDURE findnames
@number int
AS
DECLARE @instrument varchar(50)
DECLARE @tempinstt varchar(10)
DECLARE medlemcursor CURSOR
FOR
SELECT [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @number
OPEN medlemcursor
FETCH NEXT FROM medlemcursor INTO @tempinstt
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @instrument = @instrument + @tempinstt + '-'
FETCH NEXT FROM medlemcursor INTO @tempinstt
END
CLOSE medlemcursor
DEALLOCATE medlemcursor
SELECT @instrument
GO
Just doesn't seem to work, returns NULL, even though I've checked that the cursor SELECT statement actually returns data,
I have a column which has 05MAY2006:04:34:00.000000 it is stored as varchar(25). I need to save it as datetime in the same column. I have tried using
update tablename
set columnname = (SUBSTRING(columnname,1,2) + '-' + SUBSTRING(columnname,3,3) + '-' +
SUBSTRING(columnname,6,4) + ' ' + SUBSTRING(columnname,11,8));
and then
alter table tablename
alter columnname datetime;
but later it shows up the error
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
How do I change it any other opinion or any modification for the above query.
I am trying to import a txt file that has a string for it's date. How do I change it over in the import wizard I've tried going to advance then changing from string to date or date time but I get an error.
View 14 Replies View RelatedI have a string like below
DECLARE @STR VARCHAR(100) = 'AAAAAA~KKKKK~LLLL~AAAA'
i want to convert the string into rows like
AAAAAA
KKKKK
LLLL
AAAA
I am very new to SSIS though I have written hundreds of SQL 2000 dts' with all kinds of active scripts doing all kinds of things. I am now trying to convert them to SSIS and since scripts don't come through the migrator I am doing them by hand once again. I have the package created, but one of the input fields on the text file is YYYYMMDD , but the database field it is going into is a datetime so I need to convert it to a valid date format so that the conversion will take place. I tried data conversion task but that doesn't work, now I'm assuming I need to use the derived column, but not sure and don't have a clue how to use that, can anyone help? Thanks bostonrose
View 2 Replies View RelatedStuart writes "Hi being new to this game
I have have an error when trying to inseet string into a table with datetime field.
the date is not that important its the time that I use in later steps
I am creating a global temp table and then inserting values into it
below is the code
-- create the temp table
Execute ( 'create table ##progsch0
([Time] [DateTime] , '
+ '[' + @day7 + '] [varchar](100) ,'
+ '[' + @day1 + '] [varchar](100) ,'
+ '[' + @day2 + '] [varchar](100) ,'
+ '[' + @day3 + '] [varchar](100) ,'
+ '[' + @day4 + '] [varchar](100) ,'
+ '[' + @day5 + '] [varchar](100) ,'
+ '[' + @day6 + '] [varchar](100) )')
set @Starttime = 'JUL 21,2006 5:30am'
I am doing the insert in this manor becuase the @Starttime
in code actually changes time and a new record in inserted into the temp table.
Set @SQL = 'Insert into ##progsch0 (Time)
Values(convert(varchar,Convert(datetime,'+ @Starttime +'),100))'
PRINT @SQL
execute sp_executesql @SQL
I may to doing this in the completely wrong manor.
Any help would be greatful "
I am trying to select all members from a SQL db who have a renewal date btw to dates inputted into two text fields, but I am now getting the error above. This is where the problem is coming in...
If strStartDate <> "" and strEndDate <> "" Then
If bParam = True Then
StrSQL = strSQL & " AND "
Else
StrSQL = strSQL & " WHERE deleted=0 AND "
End If
strSQL = strSQL & "renewal_due BETWEEN '%" & Replace(strStartDate,"'","''") & "%' AND '%" & Replace(strEndDate,"'","''") & "%' "
End If
Any help would be greatly appreciated!
Thank you...
Hello all,
New to the forums here. I'm not a beginner with SQL, but nor am I a SQL developer - network engineer who knows some scripting and the fundamentals of DB design/administration. There's the background.
I have a database of client information where a date is keyed in and stored as text. Because this is entered by end-users, the way it's entered varies - 1/1/2001, 01/01/2001, etc. Most use "1/1/2001" (note the date is not the same for each record)
I have 4,000 records to update and I need to try to convert the text string to the correct date - changing 1/1/2005 to the same date in proper date/time format. My database uses datetime as an integer calculating the number of days from 12/30/1800. Today's date would be 75508. Time is separated into different fields.
I can do the work to update the text to a single format to make the conversion easier, but I am having trouble locating the proper way to write a convert function to do this. I've searched online (which is how I came here) and have searched the forums without luck.
Any help would be greatly appreciated!!!
I try to accomplish the following:I have two tables which are connected via a third table (N:Nrelationship):Table 1 "Locations"LocationID (Primary Key)Table 2 "Specialists"SpecialistID (Primary Key)Name (varchar)Table 3 "SpecialistLocations"SpecialistID (Foreign Key)LocationID (Foreign Key)(both together are the primary key for this table)Issuing the following commandSELECTL.LocationID , S.[Name]FROMLocations AS LLEFT JOIN SpecialistLocations AS SL ON P.PlaceID = SL.LocationIDLEFT JOIN Specialists AS S ON SL.SpecialistID = S.SpecialistIDresults in the following table:LocationID | Name1Specialist 11Specialist 22Specialist 32Specialist 43Specialist 14Specialist 4Now my problem: I would like to have the following output:LocationID | Names1Specialist 1, Specialist 22Specialist 3, Specialist 43Specialist 14Specialist 4....which is grouping by LocationID and concatenating the specialistnames.Any idea on how to do this?Thank you very much,Dennis
View 5 Replies View RelatedHi,
I'm trying to import a text-file into SQL2005 by using SSIS. Unfortunately it doesn't seem to work in the way I thought. Because I'm using the german version of SQL2005 I need to translate the items of my toolbox, so dont wonder if their names are not exactly the same as in the english versions.
Try number1:
a) My datasource is a flatfile, in the connection manager I changed the DataType for the input column to DT_DECIMAL. In my destination table the data type of ths column is decimal(20,4). Name of the column is "NXT_PUT_PX".
b) Running this package results in this error:
"Fehler bei der Datenkonvertierung. Die Datenkonvertierung für die NXT_PUT_PX-Spalte gab den Statuswert '2' und den Statustext 'Der Wert konnte aufgrund eines möglichen Datenverlustes nicht konvertiert werden.' zurück. Fehlercode: 0xC0209084. "
Translation of this is something like:
Error by converting data. Converting data for column NXT_PUT_PX returned state '2' and text 'Value couldn't be converted because of a potential loss of data'.
Try number2:
a) same as try number1
b) Adding a datatransformation-task (Datenkonvertierung), and set NXT_PUT_PX to be converted to DT_DECIMAL wirh SCALE 20.
c) Assigning this converted column to the destination column.
d) Running the package results in the same error as described above.
Try number3:
Same as try number 2 but the input column for my flatfile is now DT_WSTR with width 50.
Running this results in the same error message as above, but different errorcode. Errorcode is 0xC020907F.
Any hints how to do this conversion correct?
Best regards,
Jan Wagner