When Trying To Do An Insert, I Get Implicit Conversion From Data Type Sql_variant To Uniqueidentifier Is Not Allowed. Use The CONVERT Function To Run This Query
Jan 4, 2008
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.
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.
I am using SQL Server 2000. I have created a Store Procedure to insert information, 4 of the fields are date types. I am using OleDb Data Provider (System.Data.OleDb) namespace.
The dates are filled by a form (web form) on submit event, I created a class that has functions to create my OleDb Parameters. I add the parameters to the command and execute it through a SQL Server Stored Procedure. In the event that I must have coded something wrong, I tested in the SQL Query Analyzer and the IN parameters for the date I used the getDate() method. This is where I know it is OleDb and SQL Server Parameters.
What Converstion Format should I use in the SP for the date? OleDb.date are doubles from some date in 1979 or something or another. So I used an OleDbDataType.DBDate. It seems that when the Stored Procedure uses the IN Parameters dates provided by OleDbDataType.Date or DBDate, that it doesn't like the int format. I am guessing that I am not converting the date in the parameters in the Insert of the Store Procedure... this is basically what I have...
Function to add parameters and execute SP
private int _startdate = DateTime.Now; private int _finishdate = DateTime.Now.AddDays(30);
CREATE PROCEDURE myInsert @StartDate datetime, @FinishDate datetime AS
INSERT INTO myTable (STARTDATE, FINISHDATE) VALUES (Convert(datetime, @StartDate), Convert(datetime, @FinishDate))
Please keep in mind that I am using System.Data.OleDb namespace..... please don't tell me to use SQLClient, it seems since I've been so adaptive of using OleDb, that it sould work just as well. I am way too far into this to change my Provider to SQL Client, but I promise myself that the next project (if using SQL Server, I will be using SQLClient and I will keep using OleDb for Oracle.) *sigh*
Hi, I'm using this code to export record from sql to excel and i got this error message "Implicit conversion from data type text to nvarchar is not allowed. use the convert function to run this query"
Excel file is already created columns in the view and excel file are the same and cell format of the excel is converted to text.
--- code used insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:filename.xls;', 'SELECT * FROM [filename$]') select * from ViewName
I am facing a problem while using SQL Server with VB application.Implicit conversion from datatype text to nvarchar is not allowed.Use the convert function to run this query.When i see the trace file, i see one stored procedure called but nolines of code get executed, and immediately after that the ROLLBACKTRANSACTION occurs and the applications fails.But to my surprise i am able to do the same thing on a differentmachine using the same application and the same database on the sameserver with the same user id.Can anyone explain the reason of occurance of this problem.I require this very urgently, so i will be oblized if anyone can comeup with a quick response.Kind Regards,Amit Kumar
I am new at ASP.net and I am having problems inserting data using C# in ASP.netI have created a table named "Profile" in the MS sql server database named "MyDataBase". There is a field named "ID" that has data type 'uniqueidentifier'.I am confused how to INSERT data into this data field. I have used MS Access and MYSQL in which there is an option of auto increment so we don't a unique identifier for each record.Please tell me what can I do to If I want to have a uniqueidentifier for each new record I INSERT in the "Profile" table of MS sql server database.While trying to insert, I get following errorsCannot insert the value NULL into column 'ID'and I don't know how I can insert something in this field that is of value type unique identifier.Please help me I will be very thankfull of you.
I am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns.
I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work.
I hope someone can help me work through this.
Thanks in advance,
SK
SSIS package "Package3.dtsx" starting.
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Task failed: Bulk Insert Task 1
Task failed: Bulk Insert Task
Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
I am trying to construct a query in Query Analyzer (with SQL Server 2000), but am getting an error regarding "implicit conversion."
Here is the query:
SELECT dbo.AUCTION.EbayNum, dbo.AUCTION.EndDate, DATENAME([month], dbo.AUCTION.EndDate) + ' ' + DATENAME([year], dbo.AUCTION.EndDate) AS [PmtMonth], dbo.LOT.Description, dbo.AUCTION.WinBid, PaidStat = CASE dbo.AUCTION.PaidStatus WHEN 0 THEN '' ELSE 'PAID' END, PaidAmt = CASE dbo.AUCTION.PaidStatus WHEN 0 THEN '' ELSE dbo.AUCTION.WinBid END FROM dbo.AUCTION INNER JOIN dbo.LOT ON dbo.AUCTION.LotNum = dbo.LOT.LotNum WHERE (LEN(dbo.AUCTION.Winner) > 0)
The error occurs in the PaidAmt CASE statment: "Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query."
Why is there an implicit conversion going on? And how can I fix it? :mad:
I am using the HitSoftware driver to pass data to an AS400 from a SQL 7 database. Data makes it fine to 2 of the 4 tables but I cannot get the syntax correct to even get out of the S/P edtitor in SQL for the other 2.
I have fields in the SQL S/P defined as VARCHAR. The target fields on the AS400 are ALPHA. So I figured the insert statement to look like:
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.
I have a table adapter for one of my SQL2005 tables, and in two different fields I accept a date time. Now 99% of the times, new rows to this table will be filled out using DateTIme.Now(), as a Time Stamp is what I'm going for.
Here is the line of code in question...cops_current_data_adapter.Insert(ProductOrder, Convert.ToInt16(Session["StationId"].ToString()), PartNumber, DateTime.Now, DateTime.Now, Convert.ToInt16(qty), 0); The second DateTime.Now is the one that can be null, and it's throwing a formatting error everytime I try and drop it in there. It's a FormatException, and there's not much more to the example except unhelpful tips like be careful when conveting a string to a dateTime, which I'm not doing. Needless to say for the code to compile, and then throw a Format error at runtime is a bit frustraiting. Any suggestions would be most appreciated
I'm using the uniqueidentifier data type to generate a unique key for a table. Is there a way to change the type of unique key that's generated? I'd like it to start at 200,000 and increment as integers by 1.
Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Using OnSelected on the SqlDataSource----here is my currect code:private void On_Load(Object sender, SqlDataSourceCommandEventArgs e){if (e.Command.Parameters["@DueDate"].Value = "NULL"){e.Command.Parameters["@DueDate"].Value = "0000-00-00 00:00:00.000";}} I am trying to change the value from NULL(in a datetime field) to "0000-00-00 00:00:00.000" for use on the page.Any ideas, suggestions, or thoughts would be greatly appreciated! ,Gary"we truly fail, only when we quit."
hello all .. I have a form that includes two textboxes (Date and Version) .. When I try to insert the record I get the following error message .. seems that something wrong with my coversion (Data type)"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."
in my SQL database I have the date feild as datetime and the version as nvarchar(max) this is the code in the vb page .. Can you please tell me how to solve this problem?Imports System.Data.SqlClient Imports system.web.configuration
Partial Class Admin_emag_insert Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Record_DateTextBox.Text = DateTime.Now
End Sub
Protected Sub clearButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles clearButton.Click Me.VersionTextBox.Text = "" End Sub
Protected Sub addButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles addButton.Click Dim objConnection As SqlConnection Dim objDataCommand As SqlCommand Dim ConnectionString As String Dim record_date As Date Dim version As String Dim emagSQL As String
'save form values in variables record_date = Record_DateTextBox.Text version = VersionTextBox.Text
'Create and open the connection objConnection = New SqlConnection(ConnectionString) objConnection.Open() emagSQL = "Insert into E_Magazine (Record_Date, Version ) " & _ "values('" & record_date & "','" & version & "')"
'Create and execute the command objDataCommand = New SqlCommand(emagSQL, objConnection) objDataCommand.ExecuteNonQuery() objConnection.Close()
AddMessage.Text = "A new emagazine was added successfully"
I have a sql statement that must run in both sql server and oracle. I can't change the sql statement as it is created as part of a core application from Rational Rose.
Basically the problem is that I have a table with a column defined as a decimal type in sql server (numeric in oracle).
The select statement, however, treats the column as a char. I can reproduce the problem with this simple code:
create table atest2 (a decimal(9,0))
insert into atest2 values (1)
insert into atest2 values (2)
insert into atest2 values (null)
insert into atest2 values (4)
select * from atest2 where a = ' '
This gives the error:
Error converting data type varchar to numeric.
The documents seem to indicate the sql server will implicitly convert decimals to varchar but this doesn't seem to be happening. Is there an environment setting that might control this?
have a sql statement that must run in both sql server and oracle. I can't change the sql statement as it is created as part of a core application from Rational Rose.
Basically the problem is that I have a table with a column defined as a decimal type in sql server (numeric in oracle).
The select statement, however, treats the column as a char. I can reproduce the problem with this simple code:
create table atest2 (a decimal(9,0))
insert into atest2 values (1)
insert into atest2 values (2)
insert into atest2 values (null)
insert into atest2 values (4)
select * from atest2 where a = ' '
This gives the error:
Error converting data type varchar to numeric.
The documents seem to indicate the sql server will implicitly convert decimals to varchar but this doesn't seem to be happening. Is there an environment setting that might control this?
update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?
Hi: I get this following error when I run try to insert a record from the DetailsView. Please help me out. --------------------------------------------------------------------------------------------------------------------- Disallowed implicit conversion from data type sql_variant to data type uniqueidentifier, table 'getsetwin.lax21.tblCreateGoal2', column 'ApplicationId'. Use the CONVERT function to run this query.Disallowed implicit conversion from data type sql_variant to data type uniqueidentifier, table 'getsetwin.lax21.tblCreateGoal2', column 'UserId'. Use the CONVERT function to run this query.Operand type clash: sql_variant is incompatible with image --------------------------------------------------------------------------------------------------------------------- Thank you & Best regards, Lax
I have a field that is currently stored as the data type nvarchar(10), and all of the data in this field is in the format mm/dd/yyyy or NULL. I want to convert this field to the smalldatetime data type. Is this possible? I've tried to use cast in the following way, (rsbirthday is the field name, panelists is the table), but to no avail.
SELECT rsbirthday CAST(rsbirthday AS smalldatetime)
FROM panelists
the error returned is "incorrect syntax near 'rsbirthday'.
I'm rather new to all things SQL, so I only have the vaguest idea of what I'm actually doing.
In a stored procedure, the following code is causing a huge read and CPU load when it really shouldn't. The @IDParameter below is coming in as a parameter to the proc.
Here's the snippet of code where the problem is coming in:
DECLARE @ID INT; SET @ID = (SELECT ID From OtherTable WHERE FKID = @IDParameter); SELECT COUNT(*) FROM LargeTable
WHERE MostlyZeroID = @ID AND MostlyZeroID > 0Most (90+%) of the MostlyZeroID rows are 0 (hence the name) but regardless of distribution this should evaluate with minimal work on SQL Server's part to 0. However, when this was run, it is using a ton of CPU and doing a ton of Reads as it seeks through the entire index. When I look at the execution plan, I see under the seek predicate a Scalar Operator(CONVERT_IMPLICIT(int,[@1],0)) which is what is destroying the performance.
I've confirmed that the MostlyZeroID column in the LargeTable is defined as an INT NOT NULL. I also tested the scenario outside the stored procedure without any variables as the following to make sure it wasn't some kind of strange parameter sniffing scenario:
SELECT COUNT(*) FROM LargeTable WHERE MostlyZeroID = 0 AND MostlyZeroID > 0
However, this query also did the implicit conversion. I then tried this out on a temp table populated with a similar number of records (100 million) with a similar distribution and I didn't get the implicit conversion (I got a constant scan as I would've expected) when I did this:
SELECT COUNT(*) FROM #TestTable WHERE MostlyZero = 0 AND MostlyZero > 0
I also tried the same on several other tables that are set up similarly (large amount of zeros in an INT column) and I always got a constant scan and didn't do an implicit conversion.
why the query engine is interpreting this 0 as something other than an INT and doing an implicit conversion when getting the count in the scenario above? What can be done to protect against it? In the above scenario, an IF @ID > 0 statement was placed before the code including the count since there was no reason to even run the code if the @ID was equal to zero.
Advance thanks ....... My table is TimeSheet:----------------------------------- CREATE TABLE [dbo].[TimeSheet]( [autoid] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NOT NULL, [starttime] [datetime] NOT NULL, [endtime] [datetime] NOT NULL, [summary] [nvarchar](50) NOT NULL, [description] [nvarchar](50) NULL, [dtOfEntry] [datetime] NOT NULL, [Cancelled] [bit] NULL) ON [PRIMARY] My Query is------------------ insert into timesheet (UserId, StartTime,EndTime, Summary, Description,DtOfEntry) values (2, '19/04/2008 2:05:06 PM', '19/04/2008 2:05:06 PM', '66', '6666','19/04/2008 2:05:06 PM')i m not able to insert value Error Message is-------------------------Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated. can any body give any solution
Hey, I have a big problem that i wanna search data from SQL by DateTime like thatselect * from test where recorddate='MyVariableWhichHoldDate'i use variable that holds Date info.i searched a lot infomation on net but there is no perfect solution. i know why this occur but there is no function to solve this problem. i used a lot of ways. it accept yyyy-mm-dd format but my variable format is dd-mm-yyyyy . is there any function for this problem? and any other solution.thanks for ur attentionregards
I am using Visual Studio 2005 and SQL Express 2005. The database was converted from MS Access 2003 to SQL Express by using the upsize wizard.
I would like to store the current date & time in a column in a table. This column is a smalldatetime column called 'lastlogin'.
The code I'm using is:
Dim sqlcommand As New SqlCommand _
("UPDATE tableXYZ SET Loggedin = 'True', LastLogin = GetDate() WHERE employeeID = '" & intEmployeeID.ToString & "'", conn)
Try
conn.Open()
sqlcommand.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
This code works fine on my local machine and local SQL server. However at the client side this code results in the error as mentioned in the subject of this thread. I first used 'datetime.now' instead of 'getdate()', but that caused the same error. Then I changed the code to 'getdate()', but the error still remains.
The server at the client is running Windows Server 2000 UK . My local machiine is running WIndows XP Dutch.
Maybe the conversion from Dutch to UK has something to do with it. But this should be solved by using the 'Getdate()' function..... ?
I got this erorr when trying to create my stored proc,
What do i need to fix, and how do i fix it?!!
Msg 457, Level 16, State 1, Procedure PROC_DAILY_ACTIVITY, Line 13
Implicit conversion of char value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
Code Block set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
-- ============================================= -- Author: <Zaccheus,Tenchy> -- Create date: <NOVEMEBER,12,2007> -- Description: <Reporting stored procedure,DAILY ACTIVITY,> -- ============================================= CREATE PROCEDURE [dbo].[PROC_DAILY_ACTIVITY] (@Region_Key int=null) AS BEGIN SELECT Region_Key, Null as Customer_Code, Non_Customer_Activities.Question_code, Non_Customer_Activities.Description, Region AS Region, Name AS Territory_Name, Non_Customer_Activities.Que_Desc AS Store_Name, Non_Customer_Activities.Logged_Time AS TheDate, Non_Customer_Activities.response AS Response, Null as is_Visit_Fg FROM [FSSRC].[dbo].Qry_Sales_Group Sales_Group INNER JOIN (Select QH.[question_code] ,Question_Header.Description ,CONVERT(datetime,DATEADD(day, (qh.cycle_day-1), p.start_date),6) Logged_Time ,SUBSTRING([entity_code],1,5) SR_Code ,[response] ,Territory_Code SR_Territory_Code ,'Not Customer Related' Que_Desc From question_history QH INNER JOIN Period P ON p.period_code = qh.period_code INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Territory_In_Sales_Responsible] as Territory_In_Sales_Responsible ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code=SUBSTRING([entity_code],1,5) COLLATE Latin1_General_CI_AS INNER JOIN dbo.questions Question_Header ON Question_Header.question_code = QH.question_code WHERE [entity_code] like '%.USER%' AND Question_Header.Question_Code IN('AME01','ASE01','ACO01','ALU01','AOS01','APH01','ATR01','ATE01','ACR06','ACR05','ACR02','ACR03','ACR08','ACR07') AND CONVERT(datetime,DATEADD(day, (qh.cycle_day-1), p.start_date),6) = '11/9/2007' ) Non_Customer_Activities ON Sales_Group.Code = Non_Customer_Activities.SR_Territory_Code UNION ALL SELECT Customer_Activities.Customer_Code, NULL, NULL, Region AS Region, Name AS Territory_Name, Customer_Activities.Customer_Name AS Store_Name, Customer_Activities.Logged_Time AS TheDate, NULL AS Response, is_Visit_Fg FROM [FSSRC].[dbo].Qry_Sales_Group Sales_Group INNER JOIN (Select distinct time_log Logged_Time ,[entity_code] Customer_Code ,[name] Customer_Name ,Territory_Code Cust_Territory_Code ,MAX(is_Visit_Fg) Is_Visit_Fg From question_history QH INNER JOIN Period P ON p.period_code = qh.period_code INNER JOIN dbo.questions Question_Header ON Question_Header.question_code = QH.question_code INNER JOIN [FSSRC].[dbo].[customer] ON Entity_Code = [customer_code] INNER JOIN [FSSRC].[dbo].[visit] V ON V.[customer_code] = QH.[entity_code] AND V.[period_code] = QH.[period_code] AND V.[cycle_day] = QH.[cycle_day] INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Territory_In_Sales_Responsible] as Territory_In_Sales_Responsible ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code=[sales_person_code] COLLATE Latin1_General_CI_AS WHERE [entity_code] NOT like '%.USER%' AND Convert(datetime,convert(Varchar,time_log,110)) = '11/9/2007'
GROUP BY time_log ,[entity_code] ,[name] ,Territory_Code ) Customer_Activities ON Sales_Group.Code = Customer_Activities.Cust_Territory_Code WHERE @Region_Key=Region_Key order by 4 END
a) Date: I have a table called Table1. It has a field called HDate(nvarchar type). The records are like this; HDate H01042008 H01082008 H01102006 H01112008
Each records always start with an H charatcter, the remaining characters always represents date in the format of DDMMYYY. I want to convert this format into the date format of YYYYMMDD. So once coverted, the record should be as follows; HDateConverted 2008-04-01 2008-08-01 2006-10-01 2008-11-01
b) In the same table I have another field called NoID of nvarchar type. The records are like this; NoID 00. 000 001 002 003 004 005 006 007 008 010
I want to convert it to Integer. Once converted the records for above example should be like this; NoIDConverted 0 0 1 2 3 4 5 6 7 8 10
How can I solve both these conversions?.. Thanks a million for your qucik help.
