DataSource Controls :: Converting Dateformats From Text To Datevalue?
Jan 12, 2010
I have a CSV file with a date column. My database date format is MM/dd/yyyy and the column in which I wish to place the date is called "enddate"
The CSV date column may hold all sort of (sometimes INVALID) values for the date:
31-12-2009
1-12-2009
3/4/5
12-23-2009
Now, my assumption is that the format in the CSV date column is: d-mm-yyyy
My thought now is:import the date in a tablecolumn "datetext" as flat text.
Then run a SQL query that checks the "datetext" and tries to convert it to mm/dd/yyyy, if the conversion fails, just insert null into column "enddate" else insert the datevalue.
what SQL command can do this? Is there perhaps a function?
We currently have a DB in SQL 2005 and would like to convert it to SQL 2008 taking advantage of all the new features (if there are any) in SQL 2008. Would like to know the best approach or if there are any tools which can convert all the tables/views/storeprocs etc to 2008 specifications...
I'm using Entity to SQL to retrieve some data from the database and i'm using it to fill an DataList.The query bellow is correct and working properly. My problem is when i try to convert it to an List.I've tried with foreach(EncomendasProdutos ec in produtos)but with no sucess ("Cannot convert type 'AnonymousType#1' to 'cenatolim.EncomendasProdutos'").
Is it because i'm joining tables? I need to save the values of some fields like IVA and Final from all products of the result, but i don't know how.[Code]....
I have a feedback page with rating controls and I use a stored procedure to insert the record using C#
I get an error when I try to insert about conversion.
[Code]....
I have 20 ratings and a textbox I think the error is with the last parameter @Overall
it displays on the page as 4.9 in a label control, it is previously calculated in the Calculate_Ratings(), and it is declared as a decimal, I have also tried it declared as double. My column is declared decimal(1,1) in sqlDatabase
The Error says
System.Data.SqlClient.SqlException: Error converting data type numeric to decimal. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
when i am trying to uedit my recods in gridview this error is coming. I am using SQLDatasource with gridview and for edit purpose i am using stored procedure. I have manually executed the SP and its woring fine. my SP is here
Using the following quires, for select and Update,. Select qry is working fine ,but update qry throws 'conversion failed when converting datetime from character string' .
select * from table1 where CreatedDt >= convert(datetime,'12/23/2009',101) and createdDt < dateadd(d,1,convert(datetime,'12/23/2009',101))
Update Table1 set status = 'y' where CreatedDt>= convert(datetime,'12/23/2009',101) and CreatedDt < dateadd(d,1,convert(datetime,'12/23/2009',101)
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[SP_ORDER_LIST] @EmailId nvarchar(50) AS begin declare @Id int set @Id = (select SellerId from cart_seller_master where EmailId = @EmailId ) select CTD.OrderId,CTD.Product_code, CPM.product_name,CTD.Quantity,CTD.Orderdate, case when convert(datetime,isnull(CTD.DispatchedDate,null)) is null then 'Not Dispatched' end DispatchedDate , case when convert(datetime,isnull(CTD.DeliveryDate,null)) is null then 'Not Dispatched' end DeliveryDate from cart_Product_master as CPM left outer join cart_Transaction_details as CTD on ctd.product_code=cpm.product_code where CPM.SellerId = 1 group by CTD.OrderId,CTD.Product_code, CPM.product_name,CTD.Quantity,CTD.Orderdate, CTD.DispatchedDate,CTD.DeliveryDate end
we are using transactional replication on an sql 2008 server. we try now to add a table to the replication that only consists of 1 culomn of type date and we insert the dates to the table. when we then create the replication we are getting an error on the creation of the snapshot. here is the create table with the insert dates:
USE [Product] GO /****** Object: Table [TimeSlices].[BaseDate] Script Date: 05/03/2010 15:52:24 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TimeSlices].[BaseDate]') AND type in (N'U')) DROP TABLE [TimeSlices].[BaseDate] GO USE [Product] GO /****** Object: Table [TimeSlices].[BaseDate] Script Date: 05/03/2010 15:52:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [TimeSlices].[BaseDate]( [TargetDate] [date] NOT NULL, CONSTRAINT [PK_BaseDate] PRIMARY KEY CLUSTERED ( [TargetDate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO [TimeSlices].[BaseDate] SELECT TargetDate FROM [TimeSlices].UDF_GetTravelDateRange(GETDATE()-2 , GETDATE()+1000) attached is the UDF: [Code].... this is the add article: use [Product] exec sp_addarticle @publication = N'Product$(QuoteNumber)', @article = N'BaseDate', @source_owner = N'TimeSlices', @source_object = N'BaseDate', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'BaseDate', @destination_owner = N'TimeSlices', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_TimeSlicesBaseDate', @del_cmd = N'CALL sp_MSdel_TimeSlicesBaseDate', @upd_cmd = N'SCALL sp_MSupd_TimeSlicesBaseDate' GO
this is the error we get:
Message: Query for data failed
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper) at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause, Boolean useTableLockHint, Int32 bcpFileFormatVersion) at Microsoft.SqlServer.Replication.Snapshot.SqlServer.BcpOutThreadProvider.DoWork(WorkItem workItem) at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc() at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0) Message: Conversion failed when converting date and/or time from character string. Stack: (Source: MSSQLServer, Error number: 241)
why we are getting the conversion error on the snapshot creation?
I get this error when I am trying to concatenate the two fields in bold into one field for a datagrid.
select count(cp.iOwnerID) as enrolled,(duration + '' + duration_interval) AS Duration ,session_id,school_id as SchoolId,program_id as ProgramId,convert(nvarchar(10),session_start_date,101) as SessionStartDate, session_class_size as SessionClassSize from dbo.cnSchoolProgramSessions cn with (nolock) left join customerproduct cp on convert(datetime,convert(nvarchar(10),session_start_date,101)) = convert(datetime,convert(nvarchar(10),cp.dtPurchasedate,101)) where record_status = 1 and school_id = '" & iSchool & "' and program_id = '" & iProgram & "' and (CP.chProductNumber = '" & Me.Label5.Text & "' or CP.chProductNumber IS NULL) group by session_id, school_id, program_id,session_start_date,session_class_size,duration,duration_interval
I recently converted a web application of ours to VS 2008 so that we could upgrade to .net 3.51 framework.
I have been making some changes to the layout, to take advantage of ajax and minimize the amout of data needing to be loaded and the number of postbacks processed.
The problem I am having is when I try to save the data.
The page postback calls a local Sub which reads the form data into local variables and then passes these variables to our assembly.
The primary assembly has 70 parameters which are used to populate one of the tables in our database.
So I have 70 local variables just for this call plus a variable reference to the output of the assembly which contains a property for each of the columns in the associated database record.
There are some additional assemblies called to update other tables so, in all there are 121 locally defined variables in this sub.
Bear in mind that this sub is called from the page load event which has 93 of it's own locally declared variables.
Here is what is happening, when I run the code I am using cdate() to convert the value of a text box to datetime and pass it to my assembly. When doing so it throws a conversion error.
So I created a local datetime variable and converted the value of the control to the datetime and passed the variable to the assembly.
No more error, but the proc did not save the data as it was supposed to.
What I noticed in debugging, on the locals tab, is that every local datetime variable displayed the following message:
Cannot evaluate expression because we are stopped in a place where garbage collection is impossible, possibly because the code of the current method may be optimized
I also noticed that on the watch window the same message would display if I attempted to reference any of the datetime controls on the form.
There are only 3 variables and 3 datetime controls that appear to be affected. the controls are all html input boxes. Every other variable and control on the form behaves normally and I can see their values while debugging, it is just these six that are exhibiting the problem, whats more is that they are not new controls to the form.
I have a text box; txtPriceQuoted, and want to submit it's value to my SQL Server Database, I get an error about the conversion:
"Conversion failed when converting the varchar value '12.5' to data type int."The error only occurs when there is a decimal point in the number, how should i convert it so that I can input the value into my database....?
What I'd like to do is a find-and-replace for certain words appearing in the body text of a web page. Every time a certain word appears in the body text, I'd like to convert that word into a hyperlink that links to another page on our site.
I have embedded a Google Calendar Reminder button into an aspx page. It assumes the date is in a yyyyddmm format. So that 08/14/2010 in my text box needs to be converted to 20101408 What would be the format to take a date (string) from a textbox and convert it into a string will be in the desired format for Google?
I have a form on my website which inserts information into a table stored in sql server 2008... when I try to submit the form and I have entered a date like "08/10/2010" it works perfecting... but when trying to enter "24/10/2010" it doesn't work as the default format in SQL server is "mm/dd/yyyy" - so throws up an error saying:
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated. Also when I enter the date "08/10/2010" then submit the form, when retreiving that date on another page it displays as "10/08/2010"?
I have a website having an aunthentication system. When I am setting my website on IIS 6 the code is absolutly working fine. But when i configure the website on IIS 7 (Windows 2008 server) it is showing me the above error. I am using a stored procedure for it
