Converting String -&&> UniqueIdentifier
Aug 6, 2007
I 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
View 3 Replies
ADVERTISEMENT
Jul 2, 2006
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
View 5 Replies
View Related
Apr 10, 2007
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?
View 3 Replies
View Related
Jul 21, 2007
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?
View 2 Replies
View Related
Dec 28, 2007
Hi,I'm new at asp .net and am having a problem. On a linkbutton click event, I want to insert into my db a row of data which includes two parameters. Param1 is the id of the logged in user, and Param2 is <%#DataBinder.Eval(Container.DataItem, 'UserId')%> which is the username of a user given through a datalist.When I execute the query: "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)"I get the error: Conversion failed when converting from a character string to uniqueidentifier.I am trying to insert these into my table aspnet_friendship into the columns userId and buddyId which are both uniqueidentifier fields. I tested the query using the same values (@Param1, @Param1) and (@Param2, @Param2) and found out that the problem is with Param2 because when I use Param1 the insert works perfectly. The value passed by Param2 is supposed to be the id of a user which I get from the datalist which selects * from aspnet_Users, so that <%#DataBinder.Eval(Container.DataItem, 'UserId')%> should also be an Id like Param1 is. Since both params are in the form of .toString(), I don't understand why one works but the other doesn't.As you can see in the code below, both of these parameters are dimmed as strings, so I don't understand why Param2 doesn't work. Can anyone please help? Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim MyConnection As SqlConnection MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") Dim MyCommand2 As SqlCommand Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() Dim add As String add = "<%#DataBinder.Eval(Container.DataItem, 'UserId')%>".ToString() Session("username") = User.Identity.Name Dim InsertCmd As String = "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)" MyCommand2 = New SqlCommand(InsertCmd, MyConnection) MyCommand2.Parameters.AddWithValue("@Param1", CurrentUser) MyCommand2.Parameters.AddWithValue("@Param2", add) MyCommand2.Connection.Open() MyCommand2.ExecuteNonQuery() MyCommand2.Connection.Close() End Sub Thank you.
View 3 Replies
View Related
Jan 5, 2008
Hello, Im Getting an error (below) at the login page. The only control on
that page is a Login control, adn the error below comes when I click login. Does anyone know what Im doing wrong? This is my login page:<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Login.aspx.cs" Inherits="Login" Title="myapp- Login" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentHeader" Runat="Server"> LOGIN</asp:Content><asp:Content ID="Content2" ContentPlaceHolderID="ContentBody" Runat="Server"> <table border="1" cellpadding="0" cellspacing="0" width="305"> <tr> <td class="bodyText" colspan="2" style="height: 20px; text-align: center;"> <p> <!-- TemplateBeginEditable name="mainText" --> <asp:Login ID="Login1" runat="server" TitleText="Existing Users Log In"> </asp:Login> </p> </td> </tr> <tr> <td style="text-align: right"> <asp:LinkButton ID="lbNewmembers" runat="server" OnClick="lbNewmembers_Click">New Member?</asp:LinkButton></td> <td> <asp:LinkButton ID="lbForgotPassword" runat="server" OnClick="lbForgotPassword_Click">Forgot Password?</asp:LinkButton></td> </tr> </table></asp:Content>Error:
Server Error in '/myApp' Application.
Conversion failed when converting from a character string to
uniqueidentifier. 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 from a
character string to uniqueidentifier.Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Conversion failed when converting from a character string to uniqueidentifier.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736198 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31 System.Data.SqlClient.SqlDataReader.get_MetaData() +62 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 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
Version Information: Microsoft .NET Framework Version:2.0.50727.312;
ASP.NET Version:2.0.50727.833
View 1 Replies
View Related
Apr 26, 2006
Hi all
In the database there is a table for customers and a table for the saleman,
the relationship between the 2 table is a uniqueidentifer field
customer table
name salesmanager
company 1 12545-854
salesmanager table
id salesmanager
12545-854 bob smith
I need to update the customer salesmanger field when someone leaves problem is that that field
is a uniqueidentifer and it will not allow me to update
Thanks in advance
rich
View 2 Replies
View Related
Mar 18, 2007
my environment: WXP pro, VisualStudio 2005, SQL express Adv. with SP1
When running below code I get following error on statement Nr:14.
Error:"Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query."
What would I like to achieve:In 'mr.memberid' (9) I correctly get the PrimaryKey as 'uniqueidentifier'. In another .aspx.vb file I later use 'Session("MemberGuid")' to fill correctly a GridView based on this value!
Now, on statements (11 - 14) I would like to insert a record where field 'Member' ('Type' is also 'uniqueidentifier') should be filled using this 'uniquidentifier, but as said, I get above msg.
When using (11, no String in 'mr.member' !):'SqlDSEnrolled.InsertParameters("Member").DefaultValue = mr.memberid' I get error msg: "Value of type 'System.Guid' cannot be converted to 'String'."
Can anybody help me on how to achieve this Conversion correctly and where to do the conversion? Thanks for your timeed1 Protected Sub EnrollButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
2 If Page.User.Identity.IsAuthenticated Then
3
4 Dim user As MembershipUser = Membership.GetUser()
5
6 Dim da As New DataSetTableAdapters.MemberInfoTableAdapter
7 Dim dt As DataSet.MemberInfoDataTable = da.GetMember(CType(user.ProviderUserKey, Guid))
8 Dim mr As DataSet.MemberInfoRow = dt(0)
9 Session("MemberGuid") = mr.memberid
10
11 SqlDSEnrolled.InsertParameters("Member").DefaultValue = mr.memberid.ToString
12 SqlDSEnrolled.InsertParameters("Event").DefaultValue = CStr(EventID)
13 'SqlDSEnrolled.InsertParameters("HcIndex").DefaultValue = "11.5"
14 SqlDSEnrolled.Insert()
15
16 Response.Redirect("Enroll_Event.aspx")
17 Else
18 Response.Redirect("Login.aspx")
19 End If
20 End Sub
View 2 Replies
View Related
Apr 7, 2006
I have a uniqueidentifier probleme when i want to execute my sql string. The "PortalID" is my uniqueidentifier passed on my StoredProc parameter. But i cant make it work. It said that i cant add a nvarchar with a uniqueidentifier.!!
what is the best way to do it!! i try to convert(nvarchar(40), @PortalID) but its not workingdeclare @sql nvarchar(4000)
set @sql = 'select
t.[PortalID],
t.[City],
t.[Name],
t.[Code],
ts.*
from [Hockey_Team_Statistics] ts inner join [Hockey_Teams] t on (t.[TeamID] = ts.[TeamID])
where ts.[SeasonYear] = '+CONVERT(nvarchar(10),@SeasonYear)+' and ts.[LeagueMode] = '+CONVERT(nvarchar(1),@LeagueMode)
+'t.[PortalID] = '+@PortalID+'
order by '+ @SortExpression +''
exec sp_executesql @sql
View 3 Replies
View Related
Mar 26, 2007
Hi,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 Related
Nov 7, 2007
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!
View 5 Replies
View Related
Jun 27, 2006
is there a way to query a varbinary column and print the readable char in query analyzer?
thanks in advance!
Ryan
View 3 Replies
View Related
May 3, 2007
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!!
View 1 Replies
View Related
Apr 20, 2008
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!!!!!!!
View 1 Replies
View Related
Jan 21, 2001
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...
View 1 Replies
View Related
Aug 12, 2005
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 Related
Jul 26, 2007
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
View 7 Replies
View Related
Jul 18, 2006
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.
View 6 Replies
View Related
Nov 19, 2006
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
View 4 Replies
View Related
Mar 31, 2008
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
View 7 Replies
View Related
Jun 6, 2008
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?
View 4 Replies
View Related
Jun 28, 2004
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
View 3 Replies
View Related
Jul 9, 2007
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.
View 2 Replies
View Related
Dec 2, 2004
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.
View 6 Replies
View Related
Nov 23, 2005
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,
View 5 Replies
View Related
Jul 19, 2014
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.
View 2 Replies
View Related
May 5, 2008
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.
View 3 Replies
View Related
Jun 12, 2008
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 Related
Sep 19, 2014
I 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
View 2 Replies
View Related
Jan 29, 2007
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 Related
Mar 20, 2007
Stuart 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 "
View 1 Replies
View Related
Aug 14, 2007
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...
View 7 Replies
View Related
Sep 24, 2007
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!!!
View 6 Replies
View Related
