Syntax Error Converting Varchar Value '/' To A Column Of Datatype Int
Aug 31, 2007
Hi,
Can anyone please help me with the syntax for this query please. Error is "syntax error converting varchar value '/' to a column of datatype int"
Query:
Code:
select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D,
iCalls_Status.Status_Label,((select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ) + ' /' + (
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0)) as Countrec from
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on
iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID
ALTER TRIGGER DeleteTriggerC_Middleware_Exception ON dbo.C_Middleware_Exception AFTER DELETE AS BEGIN DECLARE @RowCount AS VARCHAR SET @RowCount = @@ROWCOUNT
DECLARE @TableName_Deleted AS VARCHAR(50) DECLARE @ErrorMsg_Deleted AS VARCHAR(255) DECLARE @AddlErrMsg_Deleted AS VARCHAR(255) DECLARE @SubjectArea_Deleted AS VARCHAR(25) DECLARE @CHKPT_Deleted AS VARCHAR(10)
SELECT @TableName_Deleted = table_name , @ErrorMsg_Deleted = cast(error_msg AS varchar(255)) , @AddlErrMsg_Deleted = cast(addl_error_msg AS varchar(255)), @SubjectArea_Deleted = cast(SUBJECT_AREA AS varchar(25)), @CHKPT_Deleted = cast(CHKPT AS varchar(255))FROM DELETED --where subject_area = 'inventory'
UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE Table_Name = @TableName_Deleted IF @@ROWCOUNT = 0 BEGIN UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE @ErrorMsg_Deleted like Error_Msg and Subject_Area IS NULL and ChkPoint IS NULL END IF @@ROWCOUNT = 0 BEGIN UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE @AddlErrMsg_Deleted like Addl_Error_Msg END IF @@ROWCOUNT = 0 BEGIN UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE @ErrorMsg_Deleted like Error_Msg and Subject_Area = @SubjectArea_Deleted and ChkPoint = @CHKPT_Deleted END
END
when i am executing the follwing query i am gettin systax error.
Query :delete from dbo.c_middleware_exception where subject_area = 'eap_room'
Error : Server: Msg 245, Level 16, State 1, Procedure DeleteTriggerC_Middleware_Exception, Line 17 Syntax error converting the varchar value '*' to a column of data type int.
I have an inventory database that Im trying to create a report out ofthe IP address are a lookup on a seperat table but I keep getting theabove error can I change the table row to something to fix this orwhat.SELECT i.INVENTORY_ITEM_ID AS [IP Address],i.HOST_NAME AS [ServerName], '' AS Flag, i.MEMO AS Comments, 'Seattle' AS City, 'Washington'AS State,CASE WHEN fv.value = 'EL EET1410' THEN '1111 3rdAve.' WHEN fv.value = 'EL WFL17' THEN '999 3rd Ave.' ELSE '' END AS[Address 1],' ' AS [Address 2], CASE WHEN fv.value = 'ELEET1410' THEN '1111' WHEN fv.value = 'EL WFL17' THEN '2222' ELSE ''END AS [Building ID],fv.VALUE AS Building, '98101' AS Zip,CASE WHEN fv.value = 'EL EET1410' THEN '14' WHENfv.value = 'EL WFL17' THEN '17' ELSE '' END AS [Computing FacilityLevel],CASE WHEN fv.value = 'EL EET1410' THEN '14' WHENfv.value = 'EL WFL17' THEN '17' ELSE '' END AS [Bldg Floor],CASE WHEN fv.value = 'EL EET1410' THEN 'C028'WHEN fv.value = 'EL WFL17' THEN 'C123' ELSE '' END AS WSPID,i.LOCATION_IN_FACILITY AS Location,i.SERIAL_NUMBER AS [Serial Number], i.ASSET_TAG AS [Asset Tag], ' ' AS[Second Asset Tag(s)],vv.VALUE AS Manufacture, mv.VALUE AS Model,'Yes' AS [Rack Mountable], rv.VALUE AS [Rack Units], 'DEV' AS [ServerEnviroment],lv.VALUE AS [Sever Type], ov.VALUE AS OS,CASE WHEN lv.value = 'Server - Intel Blade' THEN'Windows' WHEN lv.value = 'Server - Intel' THEN 'Windows' WHENlv.value = 'Server - Unix' THEN 'Unix'ELSE '' END AS [OS Type], '19x28' AS Footprint,ev.VALUE AS [Technical Owner of Server], 'N/A' AS [SLA Category],i.ON_BOARD_NIC_PORT_COUNT AS [NW Connectionquantity], 'Devlopment/Test Machine' AS [Application(s) Name],ev.VALUE AS [PW Contact],'N/A' AS RTO, 'N/A' AS RPO, 'No' AS [Is BoxClustered?], 'N/A' AS [Buisness Critical],CASE WHEN mv.value = 'Proliant DL580G1' THEN'90lbs' WHEN mv.value = 'Proliant DL360G2' THEN '85lbs' WHEN mv.value= 'Enterprise 220R' THEN'45lbs' ELSE '' END AS Weight,i.LAST_MODIFIED_DATE AS [Date of Last Install], 'No' AS [DedicatedCircuit]FROM dbo.INVENTORY_ITEM i LEFT OUTER JOINdbo.IP_TO_INVENTORY pv ON pv.IP_ADDRESS = i.INVENTORY_ITEM_IDLEFT OUTER JOINdbo.LOOKUP_VALUE lv ON lv.LOOKUP_VALUE_ID =i.DEVICE_TYPE_ID LEFT OUTER JOINdbo.LOOKUP_VALUE fv ON fv.LOOKUP_VALUE_ID =i.FACILITY_ID LEFT OUTER JOINdbo.LOOKUP_VALUE vv ON vv.LOOKUP_VALUE_ID =i.VENDOR_ID LEFT OUTER JOINdbo.LOOKUP_VALUE mv ON mv.LOOKUP_VALUE_ID =i.MODEL_ID LEFT OUTER JOINdbo.LOOKUP_VALUE ov ON ov.LOOKUP_VALUE_ID =i.SOFTWARE_VERSION_ID LEFT OUTER JOINdbo.LOOKUP_VALUE ev ON ev.LOOKUP_VALUE_ID =i.CHECKED_OUT_BY_ID LEFT OUTER JOINdbo.LOOKUP_VALUE rv ON rv.LOOKUP_VALUE_ID =i.U_HEIGHT_ID
I have a table(tab1) with a column(col1) of type varchar. I insert a row with an integer value(1). And when i query the table using the sql, select col1 from tab1 where col1 = 1, it works fine.
But after i insert a varchar, say 'a' and then do the same query, i get an error message saying, "Syntax error converting the varchar value 'a' to a column of data type int.". Why is this so? Please reply.
Well I am doing a simple insert through a stored procedure..., but I am getting this error. In my table I have the UserID as a VARCHAR 100. I am storing from a textbox that will have number because its there email. But I am not converting anything. Do I need to convert in this scenario?? Server Error in '/pickem' Application.
Syntax error converting the varchar value 'johndoe@hotmail.com' to a column of 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: Syntax error converting the varchar value 'johndoe@hotmail.com' to a column of data type int.Source Error:
Line 113: Line 114: dbConnection.Open Line 115: Dim Dr As System.Data.IDataReader = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection) Line 116: Line 117: Dim rowsAffected As Integer = 0 Here is the code: Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("connString")) Dim myCommand as New SqlCommand("prcAddTeam", dbConnection) myCommand.CommandType = CommandType.StoredProcedure myCommand.Parameters.Add("@UserID", SqlDbType.Varchar, 100).Value = TRIM(emailBox.Text) myCommand.Parameters.Add("@TeamName", SqlDbType.Varchar, 40).Value = TRIM(teamBox.Text) myCommand.Parameters.Add("@Address1", SqlDbType.Varchar, 50).Value = TRIM(addressBox.Text) myCommand.Parameters.Add("@Address2", SqlDbType.Varchar, 50).Value = TRIM(address2Box.Text) myCommand.Parameters.Add("@City", SqlDbType.Varchar, 50).Value = TRIM(cityBox.Text) myCommand.Parameters.Add("@State", SqlDbType.Varchar, 30).Value = stateList1.SelectedItem.Value myCommand.Parameters.Add("@Zip", SqlDbType.Varchar, 11).Value = TRIM(zipBox.Text) myCommand.Parameters.Add("@FirstName", SqlDbType.Varchar, 50).Value = TRIM(firstNameBox.Text) myCommand.Parameters.Add("@LastName", SqlDbType.Varchar, 50).Value = TRIM(lastnameBox.Text) dbConnection.Open Dim Dr As System.Data.IDataReader = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection) Dim rowsAffected As Integer = 0 dbConnection.OpenStored procedure:CREATE PROCEDURE prcAddTeam @UserID VARCHAR(100) ,@TeamName VARCHAR(40) ,@Address1 VARCHAR(50) ,@Address2 VARCHAR(50) ,@City VARCHAR(50) ,@State VARCHAR(30) ,@Zip VARCHAR(11) ,@FirstName VARCHAR(50) ,@LastName VARCHAR(50) ASSET NOCOUNT ONDECLARE @ID INTSET @ID = 0SELECT @ID = ISNULL((SELECT UserID from tblUserInfo where UserID = @UserID),0)IF @ID = 0 BEGIN INSERT INTO tblUserInfo (UserID,TeamName,Address1,Address2,City,State,Zip,FirstName,LastName,CreateDate) VALUES(@UserID,@TeamName,@Address1,@Address2,@City,@State,@Zip,@FirstName,@LastName,GetDate()) SET @ID = @@IDENTITY END
I am using a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure; SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@opwd", SqlDbType.NVarChar);SqlParameter p3 = com.Parameters.Add("@role", SqlDbType.NVarChar);p3.Direction = ParameterDirection.ReturnValue;p1.Value = username.Text.Trim();p2.Value = password.Text.Trim();com.ExecuteReader();lblerror2.Text = (string)(com.Parameters["@role"].Value); can your figure out what is the error ? Is it a coding error or error of the databse
Getting error Converting DataType forn Varchar to smalldatetime when running a job (in SQL Server 2005 ) which calls a SP in the step... this worked fine in SQL Server 7
Hey gurus! I've got a stored proc I call to obtain user info from a table. I've used the same format (but is slightly different) for another stored proc which works. But this one gives me "Error: System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'SELECT User_UserName AS Username, User_LastLogin AS LastLogin, User_DateCreated AS Created FROM Community_Users WITH(nolock) WHERE (User_CommunityID = '' to a column of data type int. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at ASP.Recent_Logins_ascx.BindGrid() at ASP.Recent_Logins_ascx.Page_Load(Object Src, EventArgs e) :"
Why is this stored proc converting my SQLStatement to type int???
Here's my stored proc:
CREATE PROCEDURE Community_RecentLogins ( @CommunityID int, @SortField nvarchar(75) ) AS
-- Create a variable @SQLStatement DECLARE @SQLStatement varchar(255) SELECT @SQLStatement = 'SELECT User_UserName AS Username, User_LastLogin AS LastLogin, User_DateCreated AS Created FROM Community_Users WITH(nolock) WHERE (User_CommunityID = ''' + @CommunityID + ''') ORDER BY ' + @SortField
-- Execute the SQL statement EXEC(@SQLStatement)
And here's the (relevant) code in the ascx:
Sub BindGrid() Dim conPortal as SqlConnection = New SqlConnection(CommunityGlobals.ConnectionString) Dim cmdGet as SqlCommand = new SqlCommand("Community_RecentLogins", conPortal) cmdGet.CommandType = CommandType.StoredProcedure cmdGet.Parameters.Add("@CommunityID", CommunityGlobals.CommunityID) If SortField = String.Empty Then cmdGet.Parameters.Add("@SortField", "User_LastLogin DESC") Else cmdGet.Parameters.Add("@SortField", SortField & " DESC") End If conPortal.Open() Dim dr as SqlDataReader = cmdGet.ExecuteReader() LoginGrid.DataSource = dr LoginGrid.DataBind() conPortal.Close() End Sub
Property SortField() As String Get Dim o As Object = ViewState("SortField") If o Is Nothing Then Return String.Empty End If Return CStr(o) End Get Set(ByVal Value As String) ViewState("SortField") = Value End Set End Property
Need get data from table2 using PATH data from table1 select * from table2 where id in ( select Substring(replace(path,'.',','),2,Len(path)-2) from table1) ERROR: Syntax error converting the varchar value '166,184,185,186' to a column of data type int.
Hi, all I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code. First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]"> </asp:SqlDataSource> And the dropdown list's code:<asp:DropDownList ID="ddlPayrollCompany" runat="server" AutoPostBack="True" DataSourceID="sqlDataSourcePayrollCompany" DataTextField="DisplayName" DataValueField="PayrollCompanyID"> </asp:DropDownList> Then, I use the selectedindexchanged event to bind the data to the datagrid. Here's that code: 1 Sub BindData() 2 3 Dim ds As New DataSet 4 Dim sda As SqlClient.SqlDataAdapter 5 Dim strSQL As String 6 Dim strCon As String 7 8 strSQL = "SELECT [SocialSecurityNumber], [Prefix], [FirstName], [LastName], [HireDate], [PayrollCostPercent], " & _ 9 "[Phone], [BadgeNumber], [IsSupervisor], [SupervisorID], [IsUser], [IsScout] FROM [rsrc_Personnel] " & _ 10 "WHERE ([PayrollCompanyID] = @PayrollCompanyID)" 11 12 strCon = "Data Source=DATASOURCE;Initial Catalog=DATABASE;User ID=USERID;Password=PASSWORD" 13 14 sda = New SqlClient.SqlDataAdapter(strSQL, strCon) 15 16 sda.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@PayrollCompanyID", Me.ddlPayrollCompany.SelectedItem.ToString())) 17 18 sda.Fill(ds, "rsrc_Personnel") 19 20 dgPersonnel.DataSource = ds.Tables("rsrc_Personnel") 21 dgPersonnel.DataBind() 22 23 End Sub 24
I'm assuming my problem lies in line 16 of the above code. I've tried SelectedItemIndex, SelectedItemValue too and get errors for those, as well. What am I missing? Thanks for anyone's help! Cappela07
I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:
Msg 8114, Level 16, State 5, Line 6 Error converting data type varchar to numeric.
My problem is that Line 6 is:
set @brn_pk = '0D4BDE66347C440F'
so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?
Hi I have a varchar(8000) and currently XML files are stored in varchar(8000).Some times when i am doing manuplactions in my varchar column i am getting with special characters error. so now i want to keep my column varchar(MAX) and when i am doing calculations i will convert my varchar datatype to xml datatype. By doing this i hope there wont be any special character problems. When i am doing calculations with the wellformed xml i am getting error for both convert and cast methods as below I am trying to do convert(xml,MyVarcharColumn) Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query. Also i tried with casting and getting same problme. is there any way to convert
i have a huge stored procedure abt 500 lines..and i am calling this sp from an asp.net page...thn i got this error - error converting varchar to numeric - and am trying to debug...is there any way we can find out where the error is coming from...like aproxly which line number..etcor do i have to go through each line manually and see where i am doing the conversion....
Im running a DTS package that converts data in a fixed width text file to SQL table. The package runs successfully, but when I go to dump 60,000+ records in the table I get this error:
"Error Converting data type varchar to numeric."
Is there a way that I can isolate where the offending values are located so I can manually correct them?
I am trying to change a value in a table. For instance the value is now 3. If I try to change the value to say 2 or 4 I get and error converting data type varchar to numeric. So wazzagoingon?
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
can anyone see as to why I would get this error with the following SP? ALTER PROCEDURE [dbo].[SP] @ID int = 0, @emailFrom VARCHAR(50) = Null, @emailDate VARCHAR(50) = Null, @emailSubj VARCHAR(50) = Null, @emailTxtBody VARCHAR(1000) = Null, @emailHtmlBody VARCHAR(1000) = Null
AS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
DECLARE @Notes VARCHAR (8000) DECLARE @TicketID INT DECLARE @emailBody VARCHAR (1000) DECLARE @Length Int
SET @Notes = '' SET @Length = LEN(@emailSubj)
-- insert a new entry INSERT INTO PopEmail ( emailFrom, emailDate, emailSubj, emailTxtBody, emailHtmlBody ) VALUES ( @emailFrom, @emailDate, @emailSubj, @emailTxtBody, @emailHtmlBody ) -- get the new ID SET @ID = @@identity
If ( @ID <> 0 ) AND ( ISNUMERIC(@emailSubj) = 1 )
Begin IF @emailTxtBody IS NULL BEGIN Set @emailBody = @emailHtmlBody PRINT '@emailHtmlBody: ' + @emailBody END
ELSE
BEGIN SET @emailBody = @emailTxtBody PRINT '@emailTxtBody: ' + @emailBody END
SET @TicketID = CAST( @emailSubj AS int ) SET @Notes = @emailFrom + ', ' + @emailDate + ', ' + @emailBody Select @ID = ID From TicketDetails Where TicketDetails.TicketID = @TicketID
Exec differentSP @ID, @TicketID, @Notes
PRINT 'Subject: [' + @emailSubj + ']' print 'length: ' + CAST(@Length as varchar (10)) Print 'emailSubj: ' + CAST( @emailSubj AS int ) PRINT 'ID: ' + Cast( @ID as varchar ( 10 ) ) PRINT 'TicketID: ' + Cast( @TicketID AS Varchar ( 10 ) ) PRINT 'Notes: ' + @Notes PRINT 'ID: ' + Cast( @ID as varchar ( 10 ) ) END ELSE
BEGIN Print 'ID: ' + CAST(@ID AS VarChar(10)) PRINT 'ISNUMERIC: ' + CAST(ISNUMERIC(@emailSubj) AS VarChar (10)) PRINT 'Subject: [' + @emailSubj + ']' END
I have a table with a column (locationID) of int data type, I want to pass a list of locationID's to a stored procedure using the IN operator (e.g. WHERE LocationID IN (list of locationID's)). What can I do to achieve this????
My stored procedure looks like this:
CREATE PROCEDURE [sp_test] @sLocIDs varchar(30) AS select count(distinct TimeZoneID) As timezones, from dimLocation WHERE LocationID IN (@sLocIDs)
When I test it: exec sp_test @sLocIDs = '1,2,10' I get "Error converting data type varchar to int"
Hi All, can someone help me, i've created a stored procedure to make a report by calling it from a website. I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me. The code i use is:
CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime as set dateformat dmy go declare @startdate as varchar declare @enddate as varchar
--print "query aan het uitvoeren"
select sum(moneyout) / sum(moneyin)*100 as cashbox from dbo.total where machineID = '@mID' and njdate between '@startdate' and '@enddate' GO
Hi, It is not exactly what I stated in the subject - It's an outcome - exception thrown while executing non-query command. I get this exception when I try to execute my stored procedure that takes datetime as one of its parameters. I am using dataset designer to create table adapters and build queries. Then I simply use objectdatasource component that uses one of the table adapters and bind it to for example a detailsview control. When I run this in debug mode and trace the objects everything looks perfect including these datetime parameters. It is sql server that throws the exception. I ran the sql profiler to see what exactly is going on, and I captured the command that is sent by ADO - it's broken into several lines right in the middle of my datetime parameters... this is the source of the problem. Everything is working fine when I take this command and execute it as a single line in the sql management studio. Is there anything about ADO that I do not know?
I have the following query which has been giving me headaches for days : SELECT SCCode [Service Catalog Code], FileName [File Name], FullName [Full Name], FileExtension [Extension], FileSize [Size], Author [Author], CONVERT(VARCHAR(50),CAST(LastModified AS SMALLDATETIME), 120) [Last Modified], CONVERT(VARCHAR(50),CAST(LastAccessed AS SMALLDATETIME), 120) [Last Accessed], TimesAccessed [Downloads], UploadedBy [Uploaded By], CONVERT(VARCHAR(50),CAST(UploadedAt AS SMALLDATETIME), 120) [Uploaded At] FROM #TempTable WHERE id > @firstitem AND id < @lastitem ORDER BY CASE WHEN @sortcolumn = 'Service Catalog Code' THEN SCCode WHEN @sortcolumn = 'File Name' THEN FileName WHEN @sortcolumn = 'Extension' THEN FileExtension WHEN @sortcolumn = 'Size' THEN FileSize WHEN @sortcolumn = 'Author' THEN Author WHEN @sortcolumn = 'Last Modified' THEN CONVERT(VARCHAR(50),CAST(LastModified AS SMALLDATETIME), 120) WHEN @sortcolumn = 'Last Accessed' THEN CONVERT(VARCHAR(50),CAST(LastAccessed AS SMALLDATETIME), 120) WHEN @sortcolumn = 'Downloads' THEN TimesAccessed WHEN @sortcolumn = 'Uploaded By' THEN UploadedBy WHEN @sortcolumn = 'Uploaded At' THEN CONVERT(VARCHAR(50),CAST(UploadedAt AS SMALLDATETIME), 120) END ASCWhen my @sortcolumn parameter is either FileSize (BIGINT) or TimesAccessed (BIGINT) then the query returns the data without any problem. However, if I use a different @sortcolumn value such as "Author" then I keep getting "Error converting data type varchar to bigint."A solution that seems to work is to drop CASE and use lots of IF statements instead. I don't prefer to do this because it requires me to repeat the same select statement for each and every possible @sortcolumn value!Does anyone know how to solve this? Help!
DB is SQL 2000 and I'm trying to convert a field datatype from varchar to numeric where the data is hours:minutes:seconds (05:52:12), but I run into errors with EM and scripts that the db cannot convert those datatypes. I've tried the cast function with same results ... any solutions?? I need to run SUM(), AVG(), etc, against the data and can't with VARCHAR type. Thanks all -Greg
set @Beg_tentdte = convert(varchar(10), @b_trn_tendte_Beg_Date, 112) set @End_tentdte = convert(varchar(10), @b_trn_tendte_End_Date, 112) set @Beg_tentdte1 = convert(varchar(10), @b_trn_tendte_Beg_Date1, 112) set @Beg_tentdte2 = convert(varchar(10), @b_trn_tendte_End_Date2, 112)
/* this query looks for customers with posted transactions but none prior to the date range in question */
Select distinct b_cus_cname from bar_cus_db_rec ,bar_trn_db_rec b where b.b_trn_instid = '' and b.b_trn_instid = b_cus_instid and b.b_trn_actid = b_cus_cusid and convert(varchar(10), b.b_trn_tentdte) between @Beg_tentdte and @End_tentdte and not exists (select c.b_trn_actid from bar_trn_db_rec c where c.b_trn_instid = b.b_trn_instid --and c.b_trn_tentdte between 19970901 and 20030229 and convert(varchar(10), c.b_trn_tentdte) between @Beg_tentdte1 and @Beg_tentdte2 and c.b_trn_actid = b.b_trn_actid)
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
every time I run the query, I get this error message: Error converting data type varchar to datetime.
After removing the double ' around the date and time parts i get the following error when running in QA: Msg 8114, Level 16, State 5, Line 0 Error converting data type varchar to datetime.
I have a sql code that I am having some difficulty with. All I'm trying to do is get each department and sum the amount by month based on the date in the table. I am getting "error converting data type varchar to numeric".
Code: select DEPARTMENT, CASE WHEN ((DATE >= '06/01/2014') AND (DATE <= '06/30/2014')) THEN (cast(sum(Amount) as decimal(10,2))) ELSE '' END AS 'JUNE', CASE WHEN ((DATE >= '07/01/2014') AND (DATE <= '07/30/2014')) THEN (cast(sum(Amount) as decimal(10,2))) ELSE '' END AS 'JULY' from L27_PHAROS_DETAIL_DATA WHERE Department = 'TECHNOLOGY SERVICES' GROUP BY DEPARTMENT, DATE ORDER BY DEPARTMENT
