I am in the middle of creating an editable DatGrid:
Sub AccessoryGrid_EditCommand(source As Object, e As MxDataGridCommandEventArgs)
AccessoryGrid.EditItemIndex = e.Item.ItemIndex
End Sub
Sub AccessoryGrid_BeforeUpdate(source As Object, e As MxDataGridUpdateEventArgs)
e.NewValues.Add("@AccessoryID",
AccessoryGrid.DataSource.DataSource.Tables(0).Rows(e.Item.DataSetIndex)
("AccessoryID"))
e.NewValues.Add("@AccessoryName", CType(e.Item.Cells(1).Controls(0),TextBox).Text)
e.NewValues.Add("@AccessoryPrice", CType(e.Item.Cells(2).Controls(0),TextBox).Text)
e.NewValues.Add("@AccessorySold", CType(e.Item.Cells(3).Controls(0),TextBox).Text)
e.NewValues.Add("@AccessoryDesc", CType(e.Item.Cells(4).Controls(0),TextBox).Text)
e.NewValues.Add("@AccessoryImage", CType(e.Item.Cells(5).Controls(0),TextBox).Text)
End Sub
For some reason, I get an error message like this:
Server Error in '/' Application.
Disallowed
implicit conversion from data type nvarchar to data type smallmoney,
table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT
function to run this query.
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:
Disallowed implicit conversion from data type nvarchar to data type
smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use
the CONVERT function to run this query.
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: Disallowed implicit conversion from data type nvarchar to data type smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT function to run this query.] System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.PerformSqlCommand(SqlCommand command) +82 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.Update(String listName, IDictionary selectionFilters, IDictionary newValues) +114 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnUpdateCommand(MxDataGridUpdateEventArgs e) +869 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnBubbleEvent(Object source, EventArgs e) +546 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 Microsoft.Saturn.Framework.Web.UI.MxDataGridItem.OnBubbleEvent(Object source, EventArgs e) +86 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +95 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277
My main question is, how can I convert my column 'AccessoryPrice' to smallmoney?
I have been trying to get rid of this error by trying to change the
field type within my database with no success, I keep on getting the
same error either way.
Select DATEPART(year, OrderDate) As Years,SO.TotalRevenue From Sales.Orders S Cross Apply (Select Sum(SD.Qty*SD.unitprice-SD.discount) As TotalRevenue From Sales.OrderDetails SD where S.orderid = SD.orderid ) SO Group by DATEPART(year, OrderDate),SO.TotalRevenue
Hello, i currently have a gridview that is populated with data from a SQLServer datasource. I have put an output mask in the select statement, so the date and time attributes are displayed in the format i prefer them to be in. SELECT PatientNo, ConsultantName, HospitalName, CONVERT (varchar, Date, 101), CONVERT (varchar, Time, 8) FROM [Appointment]; However when i click the 'edit' link for a record in the gridview, i am unable to edit the date/time attributes and when i click update to confirm any changes to the other attributes, the values in the date/time attributes are emptied. How can i solve this update problem. I'm guessing i need to configure my SQL UPDATE statement, but bit stuck how i do this. Please help! Thanks, James
Hey im trying to store a category name and the date into a database. For some reason i keep getting this error Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query. This error is the error im getting back from the database. the datetime field in the database is a datatype (DateTime) so what exactly is going on ?protected void InsertNewCat_Click(object sender, EventArgs e) { string insertSql = "INSERT into Category (CategoryName,Date) VALUES (@Category, @Date)"; string conString = WebConfigurationManager.ConnectionStrings["ProCo"].ConnectionString; SqlConnection con = new SqlConnection(conString); SqlCommand cmd = new SqlCommand(insertSql, con); cmd.Parameters.AddWithValue("@Category", NewCat.Text); cmd.Parameters.AddWithValue("@Date",DateTime.Now);
Hello Guys,Have been getting this error( Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query. ) when running on the live environment but it was fine when run locally. If anyone has similar problem please let me know the fix you have done. Thank you.
Im getting this error below when I try to do an insert into my database. I have no idea why this is happening, please help!this is my sqldatasource:<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" DeleteCommand="DELETE FROM [tblDiaryEntries] WHERE [DiaryEntryID] = @DiaryEntryID" InsertCommand="INSERT INTO [tblDiaryEntries] ([DiaryEntry], [Subject], [EntryDate], [UserId]) VALUES (@DiaryEntry, @Subject, @EntryDate, @UserId)" SelectCommand="SELECT [DiaryEntry], [Subject], [EntryDate], [DiaryEntryID], [UserId] FROM [tblDiaryEntries]" UpdateCommand="UPDATE [tblDiaryEntries] SET [DiaryEntry] = @DiaryEntry, [Subject] = @Subject, [EntryDate] = @EntryDate, [UserId] = @UserId WHERE [DiaryEntryID] = @DiaryEntryID"> <DeleteParameters> <asp:Parameter Name="DiaryEntryID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="DiaryEntry" Type="String" /> <asp:Parameter Name="Subject" Type="String" /> <asp:Parameter Name="EntryDate" Type="String" /> <asp:Parameter Name="UserId" Type="Object" /> <asp:Parameter Name="DiaryEntryID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="DiaryEntry" Type="String" /> <asp:Parameter Name="Subject" Type="String" /> <asp:Parameter Name="EntryDate" Type="String" /> <asp:Parameter Name="UserId" Type="Object" /> </InsertParameters> </asp:SqlDataSource> Am I doing something wrong? Server Error in '/mine' Application.
Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query. 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: Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.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:
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
I have the following query for my site which was functioning great using the OLEDB driver. Unfortunately, my client's web host forced us to use ODBC and it is no longer working Any suggestions for what I should do?
Here is the query: SELECT t.* FROM (SELECT TOP 10 itemnum, itemnum FROM ( SELECT TOP 100 itemnum, itemnum FROM(CatView) WHERE [CATALOG PAGE] LIKE 'SM%' ORDER BY itemnum ASC) AS foo ORDER BY itemnum DESC) AS bar INNER JOIN CatView AS t ON bar.itemnum = t.itemnum ORDER BY bar.itemnum;
Here is the error i'm now receiving:
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The specified field 'itemnum' could refer to more than one table listed in the FROM clause of your SQL statement. I know it was working great before, so i'm not sure what to do.
i created a query and when i run it like this i get data but when i add a value in the 2ed case for '2%' i get error. Select a.email, case when a.reportnumber like '1%' then (select b.Reportnumber from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as Reportnumber, case when a.Reportnumber like '1%' then (select b.stonebreakdown from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as Measurement, case when a.Reportnumber like '1%' then (select b.reportcarddate from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as ijasDate, case when a.reportnumber like '2%' then (select c.Reportnumber from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as imacsRepNo from t_RegisterInfoTemp a Query works fine like this but when i add this (the one marked bold i get error) case when a.reportnumber like '2%' then (select c.Reportnumber from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as imacsRepNo,case when a.reportnumber like '2%' then (select c.Measurement from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as Measurement2
This is the error. Server: Msg 4414, Level 16, State 1, Line 1Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.
Hi i got errro mess "Invalid length parameter passed to the substring function" from this below. Anyone how can give me a hint what cause this, and how i can solve it? if i remove whats whitin thoose [] it works, i dont use [] in the code :) colums: VLF_InfectionDestination is nvarchar 254
SELECT TOP 10 tb_AVVirusLog.VLF_VirusName, COUNT(tb_AVVirusLog.VLF_VirusName) AS number FROM tb_AVVirusLog INNER JOIN __CustomerMachines002 ON tb_AVVirusLog.CLF_ComputerName = __CustomerMachines002.FalseName WHERE (CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND (__CustomerMachines002.folder_id = @folderId) [OR (CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND (tb_AVVirusLog.VLF_InfectionDestination LIKE N'%@%')] GROUP BY tb_AVVirusLog.VLF_VirusName HAVING (NOT (tb_AVVirusLog.VLF_VirusName LIKE N'cookie')) ORDER BY COUNT(tb_AVVirusLog.VLF_VirusName) DESC
I am trying to concatenate a string to a currency amount so that I can show both in a drop down list box. However when I create the SQL to do this using the Query tool I get a message to say that I must use the Convert funtion to convert the currency amount.Anyone know the SQL syntax for this function as I can seem to get it to work. ThanksPaul
Hi, I have found some ways on using Convert() function for SQL server. But i do not understand some of them. Example: CONVERT(VARCHAR(10),column name,108) What is the 108 for? I see some with 120 and 101. I tried all but only 108 is working for me the way I want..but i do not understand what is that for actually. Can anyone explain it to me? Thank you.
I have found an interesting problem with Convert function. When you Use this function to Convert Datetime to varchar and if the date happens to be 01/01/2000 or higher it return nothing. here is an Example.
CREATE TABLE ABC (Id int, Datein Datetime NOT NULL Default getdate()) GO INSERT INTO ABC VALUES(1,'12/31/1999') INSERT INTO ABC VALUES(2,'12/31/1999') INSERT INTO ABC VALUES(3,'12/31/1999') GO
SELECT * FROM ABC WHERE CONVERT(varchar,DateIn,101) < '01/01/2000' -- This query returns Nothing
SELECT * FROM ABC WHERE DateIn < '01/01/2000 00:00:00' AND DateIn > '12/30/1999 23:59:59'
-- this Query return the 3 row that it is suppose to.
Is there a Fix for this problem that any one know of. Please, Help .. Or else I will have to do this solution for every time I am useing Convert function. Thank You very much for your time. Aziz Vahora
I have found an interesting problem with Convert function. When you Use this function to Convert Datetime to varchar and if the date happens to be 01/01/2000 or higher it return nothing. here is an Example.
CREATE TABLE ABC (Id int NOT NULL, Datein Datetime NOT NULL) GO INSERT INTO ABC VALUES(3,'12/31/1999 01:00:00') INSERT INTO ABC VALUES(4,'12/31/1999 02:00:00') INSERT INTO ABC VALUES(5,'12/31/1999 03:00:00') INSERT INTO ABC VALUES(6,'01/01/2000 03:00:00') GO
SELECT * FROM ABC WHERE CONVERT(varchar,DateIn,101) < '01/01/2000' GO -- This query returns Nothing
SELECT * FROM ABC WHERE DateIn < '01/01/2000 00:00:00' AND DateIn > '12/30/1999 23:59:59'
-- this Query return the 3 row that it is suppose to.
Is there a Fix for this problem that any one know of. Please, Help .. Or else I will have to do this solution for every time I am using Convert function. Thank You very much for your time. Aziz Vahora
Hi, I succesfully transfered my Access database to MS SQL and tried to run my Visual Basic application I got the following error message: "...Disallowed implicit conversion from data type varchar to data type money, table SALES, column AMOUNT ...Use the CONVERT function to run this query."
Server: Msg 260, Level 16, State 1, Line 1 Disallowed implicit conversion from data type varchar to data type money, table 'Final Project.dbo.Inventory', column 'Unit_Cost'. Use the CONVERT function to run this query.
I write using the SQL ODBC driver from software into a SLQ table called UPSSHIPMENT the format is as followed: JobNumbervarchar50 Weightreal4 FreightCostvarchar8 TrackingNumbervarchar50 Shipmethodvarchar50 VOIDIDvarchar3
I then have a trigger set to update the PACKAGE table as followed CREATE TRIGGER [UPS] ON dbo.UPSSHIPMENT FOR INSERT
AS
BEGIN UPDATE PACKAGE SET WEIGHT = inserted.WEIGHT, FREIGHTCOST = inserted.FREIGHTCOST, TRACKINGNUMBER = inserted.TRACKINGNUMBER, COMMENTS = inserted.SHIPMETHOD FROM PACKAGE INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER WHERE inserted.VOIDID = 'N'
UPDATE PACKAGE SET WEIGHT = '', FREIGHTCOST = '', TRACKINGNUMBER = '', COMMENTS = 'UPS VOID' FROM PACKAGE INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER WHERE inserted.VOIDID = 'Y'
END
The format of the PACKAGE table is as followed Jobnumbervarchar50 FreightCostmoney8 TrackingNumbervarchar50 Commentsvarchar2000 Weightreal4
When the trigger goes off I am getting the following error --------------------------- Microsoft SQL-DMO (ODBC SQLState: 42000) --------------------------- Error 260: Disallowed implicit conversion from data type varchar to data type money, table 'TESTing.dbo.Package', column 'FreightCost'. Use the CONVERT function to run this query. --------------------------- OK ---------------------------
How do you use the convert function to change the data before the update?Thank You!
I'm an Access/VB coder by experience and trying to move apps to SQL Server 2000. I have got to grips with the basics of DTS and tables and views and Stored Procedures (to an extent) but now need to upgrade an app that uses a vb function to produce a phased value for a set of budgets.
The VB function looks like this...
--------------------------------- Function calcPercentOfBudget(datFromDate As Date, datToDate As Date, iMonth As Integer, cBudget As Currency) As Currency
Dim iDuration As Integer iDuration = DateDiff("d", datFromDate, datToDate) + 1
' if either date not in current year then 0 (both dates should be in same year) If Year(datFromDate) <> Year(Now) Or Year(datToDate) <> Year(Now) Then calcPercentOfBudget = 0 End If
Dim sRatio As Single, idaysInMonth As Integer, idaysInYear As Integer
' if passed month outside of period then 0 If Not (iMonth >= Month(datFromDate) And iMonth <= Month(datToDate)) Then calcPercentOfBudget = 0 Exit Function End If
idaysInMonth = daysInMonth(iMonth, Year(Now))
'if from date and to date in same month then 100% of budget If Month(datFromDate) = Month(datToDate) Then calcPercentOfBudget = cBudget Exit Function End If
' if passed month in From month then ratio of passed month (caters for 1st day of month - 100%) If Month(datFromDate) = iMonth Then 'calcPercentOfBudget = (idaysInMonth + 1 - Day(datFromDate)) / idaysInYear * cBudget calcPercentOfBudget = (idaysInMonth + 1 - Day(datFromDate)) / iDuration * cBudget Exit Function End If
'if passed month in To month then ratio of passed month (caters for last day of month - 100%) If Month(datToDate) = iMonth Then 'calcPercentOfBudget = Day(datToDate) / idaysInYear * cBudget calcPercentOfBudget = Day(datToDate) / iDuration * cBudget Exit Function End If
' if passed month within period then 100% If iMonth > Month(datFromDate) And iMonth < Month(datToDate) Then 'calcPercentOfBudget = idaysInMonth / idaysInYear * cBudget calcPercentOfBudget = idaysInMonth / iDuration * cBudget Exit Function End If
End Function --------------------------------- Function daysInMonth(iMonth As Integer, iYear As Integer) As Integer
I have a UDF function LastDayInMonth to replace the daysInMonth vb function, and that works fine.
However I'm starting to get frustrated in trying to convert the main VB funciton to a UDF function. This is what I have got to, and as you'll see its totally wrong!...
CREATE FUNCTION [dbo].[calcPercentOfBudget] (@datFrom as datetime, @datTo as datetime , @iMonth as int, @cBudget as money, @GetDate as datetime) RETURNS money AS BEGIN declare @iDuration int declare @returnvalue money declare @sRatio decimal declare @iDaysInMonth int
set @iDuration = datediff(d, @datFrom, @datTo) set @iDaysinMonth = dbo.LastDayInMonth('1/' + cast(@iMonth as varchar) + '/' + cast(year(@getdate) as varchar))
case when year(@datFrom) <> year(@getdate) or year(@datTo) <> year(@getdate) set @returnvalue = 0 when not(@iMonth >= Month(@datFrom) and @iMonth <= month(@datTo) set @returnvalue 0 when month(@datFrom) = month(@datTo) @set @returnvalue = @cBudget when month(@datFrom) = @iMonth set @returnvalue = (@iDaysInMonth + 1 - day(@datFrom)) / @iDuration * @cBudget when month(@datTo) = @iMonth set @returnvalue = Day(datToDate) / iDuration * cBudget when @iMonth > month(@datFrom) and @iMonth < month(@datTo) set @returnvalue = @iDaysInMonth / @iDuration * @cBudget end
return @returnvalue END
This is my first post to this forum - so any constructive criticism will be welcomed.
Basically, where am I going wrong? - have I got the wrong end of the stick? Have I got the wrong stick? Have I got a stick of dynamite?!
chirag writes " i want to convert datatype numeric to varchar
my query is ==================================================== SELECT ParameterName+' ('+instancename+')' as Item, CurrentUse as LastPoll, Threshold + case Comparer when 'Greater Than' then '( > )'when 'Less Than' then '( < )'end , CASE Status WHEN 'Running' THEN 'N' WHEN 'Stopped' THEN 'Y' END as Breach FROM PARAMETER ====================================================== please solve this query as soon as possible
I’m trying to cast and integer type to a varchar and then concatenatetwo varchar and ’01’ and convert it into datetime with the followingcode:CONVERT(datetime, { fn CONCAT({ fn CONCAT(CAST(PeriodYear AS varchar),CAST(PeriodMonth AS varchar)) }, ’01’) }, 112) AS dateIt gives an error saying syntax error converting datetime fromcharacter string.Any help appreciated!!thx..--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict261333.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=900084
I have a situation where I want to filter records for a given day. Thefield that stores the date/time uses the date() function as a default value,kind of a date/time stamp for the record.The problem is when I want to filter records for a given day instead of adate range. I use the CONVERT function to return just the date part of thefield (101 as a style parameter) and compare that to a start and stop date(both being the same) and I get nothing. The stored procedure is asfollows:Alter Procedure spESEnrollmentCount@StartDate smalldatetime, @StopDate smalldatetimeAsSELECT tblCustomers.CustomerName, tblCostCenters.CostCenter,COUNT(tblESEnrollments.EnrollmentID)AS [Count of Enrollments]FROM tblESEnrollmentsINNER JOIN tblCustomers ON tblESEnrollments.CustID = tblCustomers.CustIDINNER JOIN tblCostCenters ON tblCustomers.CostCenterID =tblCostCenters.CostCenterIDWHERE ( CONVERT(DATETIME, tblESEnrollments.DTStamp, 101) >= @StartDate) AND( CONVERT(DATETIME, tblESEnrollments.DTStamp, 101) <= @StopDate) AND(Rejected = 0)GROUP BY tblCustomers.CustomerName, tblCostCenters.CostCenterIf I put 10/31/06 in for both parameters shouldn't I get records dated10/31/06 if there are some?Thanks,Wes
I have SQL table with dateTime field which is INT type (This field contains a number representing DATE / TIME) I would like to convert this umber to actuall date time output. However, SQL gives invalid date times. See below) Appears the resulting date time are all the same. SQL COMMAND Select TimeStamp, cast(convert(TimeStamp,103) As datetime) from winsData2 OUTPUT TimeStamp Converted TimeStamp 2147483647 1900-01-01 00:00:00.343 2147483647 1900-01-01 00:00:00.343 1178694066 1900-01-01 00:00:00.343 2147483647 1900-01-01 00:00:00.343 1178688211 1900-01-01 00:00:00.343 1178828143 1900-01-01 00:00:00.343 2147483647 1900-01-01 00:00:00.343 Any assitance appreciated.
Maybe it's easier to direct beginners here, rather than point them to Books Online?SELECTCURRENT_TIMESTAMP AS [Right now], CONVERT(VARCHAR, CURRENT_TIMESTAMP, Style) AS [Formatted text], Style AS [Style used], 'CONVERT(VARCHAR, CURRENT_TIMESTAMP, ' + CAST(Style AS VARCHAR) + ')' AS Example FROM( SELECT0 as Style UNION ALL SELECT100 UNION ALL SELECT1 UNION ALL SELECT101 UNION ALL SELECT2 UNION ALL SELECT102 UNION ALL SELECT3 UNION ALL SELECT103 UNION ALL SELECT4 UNION ALL SELECT104 UNION ALL SELECT5 UNION ALL SELECT105 UNION ALL SELECT6 UNION ALL SELECT106 UNION ALL SELECT7 UNION ALL SELECT107 UNION ALL SELECT8 UNION ALL SELECT108 UNION ALL SELECT9 UNION ALL SELECT109 UNION ALL SELECT10 UNION ALL SELECT110 UNION ALL SELECT11 UNION ALL SELECT111 UNION ALL SELECT12 UNION ALL SELECT112 UNION ALL SELECT13 UNION ALL SELECT113 UNION ALL SELECT14 UNION ALL SELECT114 UNION ALL SELECT20 UNION ALL SELECT120 UNION ALL SELECT21 UNION ALL SELECT121 UNION ALL SELECT126 UNION ALL SELECT127 UNION ALL SELECT130 UNION ALL SELECT131 ) AS xOutput isRight nowFormatted textStyleExample ---------------------------------------------------------------------------------------------- 2007-03-14 11:00:12.153Mar 14 2007 11:00AM 0CONVERT(VARCHAR, CURRENT_TIMESTAMP, 0) 2007-03-14 11:00:12.153Mar 14 2007 11:00AM 100CONVERT(VARCHAR, CURRENT_TIMESTAMP, 100) 2007-03-14 11:00:12.15303/14/07 1CONVERT(VARCHAR, CURRENT_TIMESTAMP, 1) 2007-03-14 11:00:12.15303/14/2007 101CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101) 2007-03-14 11:00:12.15307.03.14 2CONVERT(VARCHAR, CURRENT_TIMESTAMP, 2) 2007-03-14 11:00:12.1532007.03.14 102CONVERT(VARCHAR, CURRENT_TIMESTAMP, 102) 2007-03-14 11:00:12.15314/03/07 3CONVERT(VARCHAR, CURRENT_TIMESTAMP, 3) 2007-03-14 11:00:12.15314/03/2007 103CONVERT(VARCHAR, CURRENT_TIMESTAMP, 103) 2007-03-14 11:00:12.15314.03.07 4CONVERT(VARCHAR, CURRENT_TIMESTAMP, 4) 2007-03-14 11:00:12.15314.03.2007 104CONVERT(VARCHAR, CURRENT_TIMESTAMP, 104) 2007-03-14 11:00:12.15314-03-07 5CONVERT(VARCHAR, CURRENT_TIMESTAMP, 5) 2007-03-14 11:00:12.15314-03-2007 105CONVERT(VARCHAR, CURRENT_TIMESTAMP, 105) 2007-03-14 11:00:12.15314 Mar 07 6CONVERT(VARCHAR, CURRENT_TIMESTAMP, 6) 2007-03-14 11:00:12.15314 Mar 2007 106CONVERT(VARCHAR, CURRENT_TIMESTAMP, 106) 2007-03-14 11:00:12.153Mar 14, 07 7CONVERT(VARCHAR, CURRENT_TIMESTAMP, 7) 2007-03-14 11:00:12.153Mar 14, 2007 107CONVERT(VARCHAR, CURRENT_TIMESTAMP, 107) 2007-03-14 11:00:12.15311:00:12 8CONVERT(VARCHAR, CURRENT_TIMESTAMP, 8) 2007-03-14 11:00:12.15311:00:12 108CONVERT(VARCHAR, CURRENT_TIMESTAMP, 108) 2007-03-14 11:00:12.153Mar 14 2007 11:00:12:153AM 9CONVERT(VARCHAR, CURRENT_TIMESTAMP, 9) 2007-03-14 11:00:12.153Mar 14 2007 11:00:12:153AM 109CONVERT(VARCHAR, CURRENT_TIMESTAMP, 109) 2007-03-14 11:00:12.15303-14-07 10CONVERT(VARCHAR, CURRENT_TIMESTAMP, 10) 2007-03-14 11:00:12.15303-14-2007 110CONVERT(VARCHAR, CURRENT_TIMESTAMP, 110) 2007-03-14 11:00:12.15307/03/14 11CONVERT(VARCHAR, CURRENT_TIMESTAMP, 11) 2007-03-14 11:00:12.1532007/03/14 111CONVERT(VARCHAR, CURRENT_TIMESTAMP, 111) 2007-03-14 11:00:12.153070314 12CONVERT(VARCHAR, CURRENT_TIMESTAMP, 12) 2007-03-14 11:00:12.15320070314 112CONVERT(VARCHAR, CURRENT_TIMESTAMP, 112) 2007-03-14 11:00:12.15314 Mar 2007 11:00:12:153 13CONVERT(VARCHAR, CURRENT_TIMESTAMP, 13) 2007-03-14 11:00:12.15314 Mar 2007 11:00:12:153 113CONVERT(VARCHAR, CURRENT_TIMESTAMP, 113) 2007-03-14 11:00:12.15311:00:12:153 14CONVERT(VARCHAR, CURRENT_TIMESTAMP, 14) 2007-03-14 11:00:12.15311:00:12:153 114CONVERT(VARCHAR, CURRENT_TIMESTAMP, 114) 2007-03-14 11:00:12.1532007-03-14 11:00:12 20CONVERT(VARCHAR, CURRENT_TIMESTAMP, 20) 2007-03-14 11:00:12.1532007-03-14 11:00:12 120CONVERT(VARCHAR, CURRENT_TIMESTAMP, 120) 2007-03-14 11:00:12.1532007-03-14 11:00:12.153 21CONVERT(VARCHAR, CURRENT_TIMESTAMP, 21) 2007-03-14 11:00:12.1532007-03-14 11:00:12.153 121CONVERT(VARCHAR, CURRENT_TIMESTAMP, 121) 2007-03-14 11:00:12.1532007-03-14T11:00:12.153 126CONVERT(VARCHAR, CURRENT_TIMESTAMP, 126) 2007-03-14 11:00:12.1532007-03-14T11:00:12.153 127CONVERT(VARCHAR, CURRENT_TIMESTAMP, 127) 2007-03-14 11:00:12.15325 ??? 1428 11:00:12:153AM 130CONVERT(VARCHAR, CURRENT_TIMESTAMP, 130) 2007-03-14 11:00:12.15325/02/1428 11:00:12:153AM 131CONVERT(VARCHAR, CURRENT_TIMESTAMP, 131) Peter Larsson Helsingborg, Sweden
I'm going crazy trying to convert an Access Function to SQL.From what I've read, it has to be done as a stored procedure.I'm trying to take a field that is "minutes.seconds" and convert it to minutes.This is what I have in Access:Function ConvertToTime (myAnswer As Variant)Dim myMinutesmyMinutes-(((((myAnswer * 100)Mod 100/100/0.6)+(CInt(myAnswer-0.4))))ConvertToTime =(myMinutes)End FunctionWhen I tried to modify it in SQL:CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTimeFunction ConvertToTime(myAnswer As Variant)Dim myMinutesmyMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))ConvertToTime=(myMinutes)EndI get an error after ConverToTime.
I've imported a number of excel sheets into a Power Query Table. All seems to appear ok until I load the data. Of the 15k rows around 2k have a similar error where it cannot convert an integer to type string as below example
Expression.Error: We cannot convert the value 40 to type Text. Details: Â Â Value=40 Â Â Type=Type
The columns in question are all of integer type, I've looked through the M query and there is no conversion to string taking placeThe values where we don't get the error are also integers hence the intriguing question is why does the error occur on a subset and not the others. I suspect there is a limit to the number of errors also somewhere internally M query is converting the column to text for some reason.
Hi people, I am trying to use Aggregate function like count or average on a column which has a datatype Varchar. In order to use avg or count on it , I am doing a cast on it. If somebody has use Cast/Convert with aggregate function please help me Thanks Jesal
I have the following code in a stored procedure which needs modification as a result of the change in the input format for display_cur_price_tx field(price range). ------------------------------------------------------------------------------ old format for display_cur_price_tx is: $69 - $99
------------------------------------------------------------------------------ New format for display_cur_price_tx is: $69-$99 9without the spaces inbetween).
What needs to be new code for sp?
If I still run the old code mentioned above now, I am getting the following error:
Server: Msg 8114, Level 16, State 5, Line 4 Error converting data type varchar to numeric.
Any help on how to modify the above code to suit the new format for price range?? Any help is appreciated. Thanks. Sheila.
