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
i have given two functions - table valued and scaler valued
when select * from table_func('')
i m getting results..
but in select * from scalar_func('')
it gives error - invalid object name,
while i have both scripts open in same database but still from one func it gives results and from another it gives error..
so can anyone tell me plz. what should i do to see results from scalar func
thanks
I want to change decimal precision dynamically without rounding value
For example
10.56788 value for 2 decimal precision is 10.56.
10.56788 value for 3 decimal precision is 10.567.
---CASE 1 without dynamic parameter---------
DECLARE @DECIMALVALUE AS tinyint
SELECT CONVERT(DECIMAL(10,2),500000.565356) As Amount
[Code] ....
I am getting error as follows......
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@DECIMALVALUE'
This decimal precision format value will vary company to company ...
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.
I would be very greatful if anybody can help me.
I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.
Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.
But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.
Please see my query below:-
SELECT Â Â Week_Day.Week_Day_name, Subject.Subject_Code, Â Daily_Timetable.Period_No
FROM Â Â Â Â Week_Day LEFT JOIN
           Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6  LEFT JOIN
           Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by  Week_Day.Week_Day_Id ,Daily_Timetable.Period_No
When I am doing the divide all values are showing Zero.
DECLARE @test_sample TABLE (ClientID VARCHAR(5), FiscalYear varchar(4), QtrNumerator int, QtrrDenominator int)
INSERT INTO @test_sample VALUES
('ABC','2014',0,100),
('ABC','2015', 10,40),
('CDE','2013',14,0),
('CDE','2012',20,50)
select QtrNumerator/nullIf(QtrrDenominator,0) as QTR from @test_sample
and also I want to show the QTR with %, for example, 66.57%
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);
try
{
con.Open(); int update = cmd.ExecuteNonQuery(); CatInsertStatus.Text = update.ToString() + " record updated.";
}catch (Exception Err)
{
CatInsertStatus.Text = Err.Message;
}
finally
{
con.Close();
}
}
SQL Sever 2012, I have a table where I store data related with a Log, the columns of the table are three, IDTable, DateLog and CountLog, the column CountLog is a cumulative value, each new rows can increment this value, my query will receive two parameters related with the Date,  @dateFrom and @dateTo, with this information is necesary to get the diff between two months, for example:
CREATE TABLE MyTable
(
 IDTable INT IDENTITY NOT NULL,
 DateLog  DATE NOT NULL,
 CountLog INT NOT NULL
)
[code]....
In this case I need to apply a filter with the Dates:
@dateFrom ='2014-01-01',
@dateTo   ='2014-04-28'
But according with this filter I need to limit the results only for this period between the date, but I need that the first month (January 2014) take the information of the max value from previous month even not included in the filter, in this case the results will be the next:
Id     MonthYear    Diff
1 Â Â Â Â Â Â Jan2014 Â Â Â 5
2 Â Â Â Â Â Â Feb2014 Â Â Â 3
3 Â Â Â Â Â Â March2014 Â 0
4 Â Â Â Â Â Â April2014 Â Â Â 5
With the results, for the first mont (January) is, according to the range of times, January is the lower limit,the last cumulative value of CountLog before the first month of the range  was 2 (December 2013), the max value of the CountLog for this first month was 7, the diff is 7-2 equal to 5 , in the case of the next month (February) the max value of the CountLog was 10, the diff is 10 - 7 equal to 3 and this way. Â
[URL]
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.
Is there a way to define a query-filter (Example: WHERE column1 > 5 AND column2 = 'value') in a function?
So I can create a query like this:
SELECT *
 FROM Table
 WHERE MyFunction()
I know it's a bit of a strange question, but I'm writing a dynamic software that will have the ability to run Stored Procedures on any database to create some data-checks. Through parametrisation, a user can define for a specific Stored Procedure that some results are no longer necessary in the result-set.
Example:
ID - Name - State
1 - Jozef De Veuster - Mad
2 - Piet Husentruut - Not Happy
3 - Jeroen Meus - Angry
Is the result of a Stored Procedure "Show_me_unhappy_persons". But we already know that Jozef De Veuster is ALWAYS Mad, so a user can say: Exclude ID = 1, so it won't appear anymore in the result.
I want to handle this by doing:
SELECT *
FROM PeopleStates
WHERE --Some stuff--
AND CheckUserExclusions(SomeID)
And CheckUserExclusions will translate to "NOT (ID = 1)"
I'm trying to return a query based on the dateadd function. I have a column in the database called date_added which is am successfully using the the DATEADD function above as date1. The Var1 variable I need to populate from the database too from a column called life_span which is an int data type. The error I get is An expression of non-boolean type specified in context where a condition is expected near select
My query is as follows: select guid, dateadd(day,life_span,date_added) as datepayday. From User_table
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:
[SqlException (0x80131904): Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.] 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.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149 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.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +401 System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +447 System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +72 System.Web.UI.WebControls.DetailsView.HandleInsert(String commandArg, Boolean causesValidation) +390 System.Web.UI.WebControls.DetailsView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +623 System.Web.UI.WebControls.DetailsView.OnBubbleEvent(Object source, EventArgs e) +95 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.DetailsViewRow.OnBubbleEvent(Object source, EventArgs e) +109 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.LinkButton.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) +174 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
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
View 3 Replies View RelatedI have a table with single row like below
 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0Â Â Â | Value1Â Â Â | Value2Â Â Â Â | Value3Â Â Â |Â Value4Â |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _
i got this stored procedure.
i tried to modify it and now its giving me this scaler error.
Msg 137, Level 15, State 2, Procedure insertuser, Line 4
Must declare the scalar variable "@seller_id".
USE [DBCars]
GO
/****** Object: StoredProcedure [dbo].[insertuser] Script Date: 05/23/2008 20:44:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[insertuser]
(@Make nchar(10),@Model nchar(10),@City nchar(10),@SellerID varchar(50),@MileAge nchar(10),@Year_Model int)
as
insert into tbcar values(@make,@model,@city,@seller_id,@mileage,@year_model);
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
View 3 Replies View RelatedI'll go right to the point:
I have a textbox in wich the user can seize a "money" value.
But when i'm doing the INSERT command, it fails and says I cannot implicitely convert nvarchar data (mytextbox.text) to money data.
I don't know how to handle this...
objCmd = New SqlCommand("INSERT INTO tbl_appel_service_pieces " & _
"(fld_nom_piece, fld_quantite, fld_prix, fld_description, fld_num_appel) " & _
"VALUES (@fld_nom_piece, @fld_quantite, @fld_prix, @fld_description, @fld_num_appel)", objConn)
objCmd.Parameters.Add("@fld_nom_piece", champsNomPiece.Text)
objCmd.Parameters.Add("@fld_quantite", champsQuantite.Text)
objCmd.Parameters.Add("@fld_prix", champsPrix.Text)
objCmd.Parameters.Add("@fld_description", champsDescription.Text)
objCmd.Parameters.Add("@fld_num_appel", champsNumAppel.Text)
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
afficherPieces()
How and where to do the conversion ? On the VALUES variable ?
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.
Hi,
Here is the statement I'm trying to run....
declare @A varchar(5)
select @A='123'
select @A=(select convert(money,@A))
select @A
This is the error message I get:
Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.
Does anyone know what should I change in the query to make it work?
Thank you very much,
Anastasia.
Hi all,
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
Hi all,
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."
Used code looks like:
sSQL = "INSERT INTO sales ([TRIP], [CC], [TRANS], [DATE], [AMOUNT], [GST], [PST], [TOTAL], [NOTE], [CATEG]) VALUES ('" & txtTrip.Text & "', '" & cmbCC.Text & "', '" & txtTrans.Text & "', '" & txtDate.Text & "', '" & txtSubtotal.Text & "', '" & txtGST.Text & "', '" & txtPST.Text & "', '" & txtTotal.Text & "', '" & txtNote.Text & "', '" & cmbCat.Text & "'); "
Set rst = New ADODB.Recordset
rst.Open sSQL, objAccessConnection, adOpenKeyset, adLockOptimistic
Can you please help me fix this error, I am new to MS SQL...
Thanks.
I get this error when I run this query, How do I use the CONVERT function in this scenario
insert into Inventory (Item_ID,
Inventory_ID,Perishable_indicator, Perishable_Date,
Department_ID, Category, Description, Unit_of_Measure,
Quantity_on_Hand, Unit_Cost, Master_Pack_Quantity,
Supplier_ID)
values
('23456','8263458', 'Yes', 'Feb 25 2005
12:00AM','695', 'Produce', 'Apple', 'per lb', '6000',
'0.99', '10000', '893')
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!
Hi
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
Dim datTemp As Date
datTemp = CDate("1/" & CStr(iMonth) & "/" & CStr(iYear))
datTemp = DateAdd("m", 1, datTemp)
datTemp = DateAdd("d", -1, datTemp)
daysInMonth = Day(datTemp)
End Function
---------------------------------
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?!
All help greatfully received,
Paul:eek:
Can u help me transform this code into sql function?
/* Append modulus 11 check digit to supplied string of digits. */
function GenMOD11( $base_val )
{
$result = "";
$weight = array( 2, 3, 4, 5, 6, 7,
2, 3, 4, 5, 6, 7,
2, 3, 4, 5, 6, 7,
2, 3, 4, 5, 6, 7 );
/* For convenience, reverse the string and work left to right. */
$reversed_base_val = strrev( $base_val );
for ( $i = 0, $sum = 0; $i < strlen( $reversed_base_val ); $i++ )
{
/* Calculate product and accumulate. */
$sum += substr( $reversed_base_val, $i, 1 ) * $weight[ $i ];
}
/* Determine check digit, and concatenate to base value. */
$remainder = $sum % 11;
switch ( $remainder )
{
case 0:
$result = $base_val . 0;
break;
case 1:
$result = "n/a";
break;
default:
$check_digit = 11 - $remainder;
$result = $base_val . $check_digit;
break;
}
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
at
chiragvm@gmail.com
"
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
View 4 Replies View RelatedI 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
View 1 Replies View RelatedDear Friends,
I have a OLEDB Source and on it I have a convert(datetime,GetDate() ,102) AS RKData.
The field RKDATA returns me the value 2007-05-02 11:23:00...
How can I return only the date without time? Like:
2007-05-02 00:00:00...
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
CAN SQL GURUS(PESO,MADHAVAN,VOLTER,KRISTEN) PROVIDE ME EXAMPLE OF CONVERT AND CAST FUNCTION CLEARLY WITH EXAMPLE
View 9 Replies View Related