thanksNever tried it myself but it should let you step through a sproc like you would your C#/VB.Net code behind.
Walkthrough: Debugging Hello World, a SQL Stored Procedure|||hi MMS
I was able to isolate the line tht was causing the problem..however i will go through the article to see if it will help with some info for future use.
thanks.
I am trying to add some datetime values into a table. However, the database keeps throwing the following error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
The code is as follows:
string start = dateCal.SelectedDate.ToString().Substring(0, 10) + " " + startTB.Text;
string end = dateCal.SelectedDate.ToString().Substring(0, 10) + " " + endTB.Text;
//DateTime starts = Convert.ToDateTime(start);
//DateTime ends = Convert.ToDateTime(end);
sqlInsert.CommandText = "INSERT INTO aspnet_reportdate VALUES ('" + refno + "', '" + start + "', '" + end + "')";
where startTB and endTB contains a time in valid format (HH:MM)
The same error would occur even if I converted the string to a DateTime object before I try to insert it into the database.
a sample value of start would be "6/19/2007 10:00"
What puzzles me is that only the error is only thrown by the database (SQL Server Express) but not C#
I have tried using both datetime and smalldatetime for the columns in question in the database also.
There is no problem with your SQL Server or C#. The problem is on your input data (mismatched date format).
I strongly recommand to change your code as follow as. It is really very dangerous code you are writing (SQL Injuction).
Don't use Dynamic Query generation on the UI. Beaware of SQL Injunction. (use parameters or Stored Procs)
The following code will work for you, (the sql server parse the param before executing it, so you can easily identify the problem)
Code Snippet
sqlInsert.CommandText = "INSERT INTO aspnet_reportdate VALUES (@.refno, @.start, @.end)";
sqlInsert.Parameters.Add(new SqlParameter("@.refno", DbType.String));
sqlInsert.Parameters.Add(new SqlParameter("@.start", DbType.DateTime));
sqlInsert.Parameters.Add(new SqlParameter("@.end", DbType.DateTime));
sqlInsert.Parameters[0].Value = refno;
sqlInsert.Parameters[1].Value = starts;
sqlInsert.Parameters[2].Value = ends;
sqlInsert.ExecuteNonQuery();
|||Just to add to what Mani has said, if you do this kind of thing, the best way to get help is to add a print statement, messagebox, etc and print out the SQL statement that you are trying to execute. You can't use profiler with express (at least not with a license to the real tools), but if you can, using profiler you can see the statement that you are trying to execute. I would strongly consider purchasing a developer license to get the tools to work with. Like Mani says, use a parameterized statement in all cases possible, but if you are trying to build a reproducible script, that might not be possible.
The first most important step in a process like this is to figure out what you are trying to execute and take that statement to Management Studio (Express will do for this) and work out what the issue is in the query.
Date values should ideally use the standard formats. Look up "datetime data type, formats" in the index, and check out the ODBC timestamp format. It will always work.
|||thx all for the help
just a further question. what is sql injunction and what problems does it bring about?
couldn't seem to find anything substantial on it
|||
Louis Davidson wrote:
Just to add to what Mani has said, if you do this kind of thing, the best way to get help is to add a print statement, messagebox, etc and print out the SQL statement that you are trying to execute. You can't use profiler with express (at least not with a license to the real tools), but if you can, using profiler you can see the statement that you are trying to execute. I would strongly consider purchasing a developer license to get the tools to work with. Like Mani says, use a parameterized statement in all cases possible, but if you are trying to build a reproducible script, that might not be possible.
will bear this in mind
most of what i do now is more for interest or sch work, so my choice of tools are more restricted.
declare @.a datetime
declare @.b varchar(10)
set @.b='26/04/2004'
set @.a= Convert(datetime, @.b)
but it gives me this error:
Server: Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I believe is my date format..i want to know how to make sure that i am the dd/mm/yyyy format is correct way?Why are you sending the date in as a varchar ?|||Use SET DATEFORMAT
Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.|||Hi,
thanks for all ur reply.. basically i wanted to send date in as dateTime but i cant because i cant assign a date to null so i have to send it to my database and from there i convert it to valid date time.
Thanks alot.
Hello,
Firstly, i need to work out why I cannot change my datatypes(please see query)
Code Snippet
SELECT * FROM (
SELECT top 10
ref,
RecordDate,
TransactionID,
StatusChangedTimeStamp,
TransactionStatus,
PartyTransactionStatus,
BadDeliveryReason,
TradingDaysRef
FROM (
SELECT 1 seq,
'ref' ref,
'RecordDate' RecordDate,
'TransactionID' TransactionID,
'TransactionStatus' TransactionStatus,
'StatusChangedTimeStamp' StatusChangedTimeStamp,
'PartyTransactionStatus' PartyTransactionStatus,
'BadDeliveryReason' BadDeliveryReason,
'TradingDaysRef' TradingDaysRef
UNION ALL
SELECT 2 seq,
cast(ref as bigint),
RecordDate,
TransactionID,
StatusChangedTimeStamp,
TransactionStatus,
PartyTransactionStatus,
BadDeliveryReason,
TradingDaysRef
FROM dbo.ParticipantTradeStatusChange
) x
order by seq, RecordDate
) y
The error returned is:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
The reason for me doing this, is exporting both column names & data to a xls file.
Secondly, once i get the query to complete...I kindly ask, how would i make this query a little swifter in which, i mean, select the top 100 from the table then SELECT the columns...when i do
SELECT top 10 * FROM (
SELECT *
It falls over and dies
Help much appreciated
thanks,
i
The data -- literals -- that you have above your unions are not implicitly compatible with what is below the union. Look at this example:
Code Snippet
select 'Header'
union all
select 2
/*
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Header' to a column of data type int.
*/
I am basically getting the same error. You may need to explicitly declare the datatype of the lower part of the union to have them go as varchar -- maybe like:
Code Snippet
select 'Header' as Data
union all
select cast (2 as varchar)
/*
Data
Header
2
*/
i've done a dirty workaround..
simply used cast(columnname as varchar(4000))
did the trick and i have my bcp file with headings
all todo now is make it run faster...
DECLARE @.ENTITY nvarchar (100)
set @.ENTITY = 'AccidentDimension'
DECLARE @.FIELD nvarchar (100)
set @.FIELD = 'JurisdictionState'
DECLARE @.KEYID nvarchar (100)
SET @.KEYID = '1234567890'
DECLARE @.VALUE nvarchar (100)
SET @.VALUE = 'WI'
DECLARE @.WC_TABLE NVARCHAR(100)
SET @.WC_TABLE = 'WorkingCopyAdd' + @.ENTITY
DECLARE @.SQL1 NVARCHAR (1000)
SET @.SQL1 = 'INSERT INTO ' + @.WC_TABLE+ ' (Claim, '+ @.Field +') VALUES ('''+ @.KEYID +''', '''+@.VALUE+''')'
EXECUTE sp_executesql @.SQL1
Can somebody help me. I get this error:
Error converting data type varchar to numeric.
while executing this Dynamic TSQl Command
Odd; what is the data type of the "JurisdictionState" column. The other column should be fine, but also what is the data type of the "Claim" column.
|||The statement you are executing is
INSERT INTO WorkingCopyAddAccidentDimension (Claim, JurisdictionState) VALUES ('1234567890', 'WI')
There is a bigger question than the varchar/numeric conversion, and that is why you are using dynamic SQL, but to answer the question you asked, it doesn't look like there should be a problem. Is this the exact code that is failing, or might it be failing on another @.KEYID value?
I suggest you look at www.sommarskog.se/dynamic_sql.html for more on dynamic SQL, even thought it's tangential to your specific question here.
Steve Kass
Drew University
http://www.stevekass.com
|||Claim is of type: varchar
JurisdictionState is char of length 2
I seperated and inserted with each colunm at a time to know if its problem with Claim or JursidictionState. Well, both of the time I received same error.
|||The error may be caused by an incorrect default, as in teh following sample:CREATE TABLE a(i int, j INT DEFAULT('acs'))
GO
INSERT a(i)VALUES(1)
also the error might be caused by a trigger. Do you have triggers on the table?|||
Well I had DEFAULT ' ' (A Blank / Space)
Is this the problem ?
|||
yes.. yes..
Always try to stick with the column datatype while giving the default value..
I am really wondering how SQL Server allows to create the default value with different datatype..
|||CREATE TABLE WCAddAcciDim
([Claim] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ' ',
[JurisdictionState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ' '
)
GO
INSERT dbo.WCAddAcciDim(Claim , JurisdictionState)VALUES('12', 'IN')
INSERT dbo.WCAddAcciDim(Claim , JurisdictionState)VALUES('1234567890', 'WI')
select * from WCAddAcciDim
DROP TABLE WCAddAcciDim
Well I see no problem when I execute this.
And when I execute this below line, just this line, without use of Dynamic SQL
INSERT dbo.WorkingCopyAddAccidentDimension(Claim, JurisdictionState) VALUES ('1234567890', 'WI')
I get error.
Very Strange.
I created teh table WorkingCopyAddAccidentDimension same way as I did above, infact I copied those lines and rename the tabel name thats it.
What might be the Hidden error, Any idea please
|||What is the error message you are getting… Verify the table schema using..
Sp_help WorkingCopyAddAccidentDimensiona
|||Mani:
When I removed the Defaults I was able to update and Insert as wanted and required. However i have NULLS in the rest of colunm. This table has 68 Colunms and I have multiple tables around 6of similar number of colunms. Now I am using the data from these Staging/ WorkingCopy tables and Inserting it back to main Table. Where certain colunm cannot be null. If there is a null in certain colunm it will not allow me to insert it. Thast why I chose to make BLANK as a default in WorkingCopy Table.
Now that I have remove BLANK/ SPACE from teh default, is there any Standard way of Replacing these NULLS with the Blank /Space. Could you please suggest any way to do this?
What does MS SQL Standards have to say on this?
What I could think of is to replace each colunm with a space were ever there is NULL but I guess this is not the standard way of doing. Any suggestion or any modification on re-creating WorkingCopy Table with Defaults?
|||You are in wrong direction, The default won’t help you here..
The default only activated when you have no entry on the INSERT statement. When you try to INSERT the NULL value the Default value will not be taken, rather it will store as NULL.
In single word, the DEFAULT value only stored when there is no value/no entry specified in the insert query…
As per the BOL,
Column definition
No entry, no DEFAULT definition
No entry, DEFAULT definition
Enter a null value
Allows null values
NULL
Default value
NULL
Disallows null values
Error
Default value
Error
So, you have to use the ISNULL function to fix your problem.
Code Snippet
Create table #Staging1
(
Id int,
Name varchar(10)
)
Insert Into #Staging1 Values(1, NULL);
Insert Into #Staging1 Values(1, 'test');
Go
Create table #Main
(
ID int NOT NULL,
Name varchar(10) NOT NULL DEFAULT ('')
);
--Will Work Fine
Insert Into #Main(ID)
Select ID From #Staging1
--Should Fail
Insert Into #Main(ID,Name)
Select ID,Name From #Staging1
--Will Work
Insert Into #Main(ID,Name)
Select ID,Isnull(Name,'') From #Staging1
|||
Excellent
Got it
Thanks a lot Mani
case when Revenue='A' then Sum(total_amt) else ' ' end as A
but it is giving me and error Error converting data type varchar to numeric.
please help
Quote:
Originally Posted by voroojak
HI
i have a very big problem.
i have a cloumn name Revenue and i have differt types of revenue in it such as A, B, C.
what i want to do is that i want to say that when the Revenue column is A then sum the tola mount and put in in a new column name A when b then again same thing.
this is my code
case when Revenue='A' then Sum(total_amt) else ' ' end as A
but it is giving me and error Error converting data type varchar to numeric.
please help
because your case statement said when revenue = 'A' return the sum of total_amt else return blank...the case statement should return (the then part and the else part) the same datatype or at least can be converted to the same datatype.
try ...else sum(0) end as A, ...or ....else 0 end as A
Hello...
I new in MS SQL.
Given: 2 servers, same SQL statements, same input, same tables, same data types, same triggers.
Problem: One server works fine while the other returns the error above.
I have no idea of the problem why the other is working.IN addition to that, they also have the same MS SQL version.|||Perhaps you could post the relevant code, as well as the table structure? It would be quite impossible to help without these two crucial pieces of information.
Hi,
Thank you in advance for your comments/suggestions. I am trying to create a View of a Table. The table is created by another application so I am unable to recreate it they way I want, also the data that is in the columns that I want to CAST are "numbers" not letters and will only be numbers. In the view I need certain columns to be CAST as numeric from varchar.
Here is the syntax that I am currently using:
Code Snippet
SELECT CAST(szF1 AS datetime) AS [Login Date/Time], szF2 AS [User Name], CAST(szF3 AS numeric) AS [Documents Indexed], CAST(szF4 AS datetime) AS [Logout Date/Time], CAST(szF5 AS numeric) AS [Documents Sent to QC], CAST(szF6 AS numeric) AS [Documents Reconciled], szF7 AS [Reject Reason], CAST(szF8 AS datetime) AS [Report Date]
FROM dbo.F_Report_Data AS a
WHERE (szF3 <> 'Blank')
When I open the view I get the error message about converting varchar to numeric.
Thanks,
Erik
Try running this query:
Code Snippet
SELECT CAST(szF1 AS datetime) AS [Login Date/Time],I tried your suggestion and I got an error: "Error in list of function arguments: '=' not recognized. Unable to parse query text.
|||It's likely because you have data in the szF5 or szF6 columns that can't be converted to numeric. For example, if I had the value aaa in szF5, I would get that error. More common is if I have a zero length string in the column, that can't be converted to numeric and I would get the error. A null would be fine but a zero length string would cause the error.
-Sue
|||I would suggest (1) give the schema of the table and (2) give 5 sample rows of data from the table by doing a
select top 5 * from F_Report_Data
|||The 3 columns that I want to cast as numeric have only numbers in them.
1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM
The items in bold are thecolumns that I am trying to covnert/cast as numeric.
|||The 3 columns that I want to cast as numeric have only numbers in them.
Code Snippet
1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM
The items in bold are thecolumns that I am trying to covnert/cast as numeric.
Could you clarify by what you mean "Schema", it has been a while since my DB class and I am not a DBA. Every column is varchar(8000),null except for the PK which is (int, not null).
I awm giong to attempt to see if I can get the necessary results w/o convert/cast because there is supposed to be implicit conversion of varchar to numeric.
|||Actually, you have answered the schema question -- all columns are varchar(8000) except for the PK which is integer -- a "wow" table. That should be enough for now. Try running this query and see if any results are returned:
Code Snippet
select left(szF1, 25) as szF1,
left(szF2, 25) as szF2,
left(szF3, 25) as szF3,
left(szF4, 25) as szF4,
left(szF5, 25) as szF5,
left(szF6, 25) as szF6,
left(szF7, 25) as szF7,
left(szF8, 25) as szF8
from dbo.F_Report_data
where isDate(szF1) = 0
or isNumeric (szF3 + 'D0') = 0
or isDate(szF4) = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0
or isDate(szF8) = 0
Yes it returned a result, it's good that it returned a result but does that mean that we can/cannot convert/cast a varchar as a numeric? Thanks for your help!!
|||Please post a sampling of the results that you received. It means that you will might either need to change the table or modify the way you display the data so that it is properly "clensed" -- you have dirty data.
|||Here are the results:
Code Snippet
01/24/2007 09:58:03 AM admin 207 01/24/2007 02:08:55 PM 0 0 2007012401/25/2007 01:55:19 PM test 0 01/25/2007 04:11:09 PM 1 0 Unable to read case numbe 20070125
| szf1| |szF2| szF3 |--szF4-| szF5 sz F6 |-szF7-| |szF8|
I just noticed that the query trimmed szF7 (Where it says "Unable to read case numbe"), I will need that field a little larger for the text. I never would have thought that there would be so much trouble to convert/cast a varchar to a numeric in a view.|||That did not display the same way it did on my screen when I was typing it. I hope it isn't too confusing.|||
Can somebody point Erik to an article about cleaning up data? My tests weren't strong enough and I really am not interested in wasting Erik's time. I suspect that blanks in his data caused the isNumeric tests to fail.
Erik:
You can try this query; it will exhibit which test is failing:
Code Snippet
select isDate(rtrim(szF1)) as szF1isDate,I feel like I need a fresh set of eyes on this at this point. Help?
|||Here are the results of the query. I don't really know what they are saying though, could you give me pseudo code explanation of the query?
0,1,0,1,1,0,,Blank,0,,0,0,,
1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518
Erik:
To me the problem here is that the columns are not sufficiently typed; this is a design problem that should be fixed. If a column is intended to be used as a number it should be typed as numeric. Similarly, if a column is going to be used as a date it should be typed as a datetime column, not as a varchar. Here is the basic response to the records returned from the query:
0,1,0,1,1,0,,Blank,0,,0,0,,
this record failed for 3 reasons:
(1) The szF1 field is not a valid date (it is an empty string)
(2) The szF4 field is not a valid date (it is an empty string)
(3) the szF8 field is not a valid date (it is an empty string)
1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
this record faild for two reasons:
(1) The szF5 field is not numeric (it is an empty string)
(2) the szF6 field is not numeric (it is an empty string)
]
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)
Now, you might be able to use the NULLIF function to get around these problems since all of these are manifest when the column is an EMPTY string. If you are wanting to test for NUMERIC columns you might also want to give a look to this article about problems with the "isNumeric" built-in function:
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
Hi,
Thank you in advance for your comments/suggestions. I am trying to create a View of a Table. The table is created by another application so I am unable to recreate it they way I want, also the data that is in the columns that I want to CAST are "numbers" not letters and will only be numbers. In the view I need certain columns to be CAST as numeric from varchar.
Here is the syntax that I am currently using:
Code Snippet
SELECT CAST(szF1 AS datetime) AS [Login Date/Time], szF2 AS [User Name], CAST(szF3 AS numeric) AS [Documents Indexed], CAST(szF4 AS datetime) AS [Logout Date/Time], CAST(szF5 AS numeric) AS [Documents Sent to QC], CAST(szF6 AS numeric) AS [Documents Reconciled], szF7 AS [Reject Reason], CAST(szF8 AS datetime) AS [Report Date]
FROM dbo.F_Report_Data AS a
WHERE (szF3 <> 'Blank')
When I open the view I get the error message about converting varchar to numeric.
Thanks,
Erik
Try running this query:
Code Snippet
SELECT CAST(szF1 AS datetime) AS [Login Date/Time],I tried your suggestion and I got an error: "Error in list of function arguments: '=' not recognized. Unable to parse query text.
|||It's likely because you have data in the szF5 or szF6 columns that can't be converted to numeric. For example, if I had the value aaa in szF5, I would get that error. More common is if I have a zero length string in the column, that can't be converted to numeric and I would get the error. A null would be fine but a zero length string would cause the error.
-Sue
|||I would suggest (1) give the schema of the table and (2) give 5 sample rows of data from the table by doing a
select top 5 * from F_Report_Data
|||The 3 columns that I want to cast as numeric have only numbers in them.
1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM
The items in bold are thecolumns that I am trying to covnert/cast as numeric.
|||The 3 columns that I want to cast as numeric have only numbers in them.
Code Snippet
1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM
The items in bold are thecolumns that I am trying to covnert/cast as numeric.
Could you clarify by what you mean "Schema", it has been a while since my DB class and I am not a DBA. Every column is varchar(8000),null except for the PK which is (int, not null).
I awm giong to attempt to see if I can get the necessary results w/o convert/cast because there is supposed to be implicit conversion of varchar to numeric.
|||Actually, you have answered the schema question -- all columns are varchar(8000) except for the PK which is integer -- a "wow" table. That should be enough for now. Try running this query and see if any results are returned:
Code Snippet
select left(szF1, 25) as szF1,
left(szF2, 25) as szF2,
left(szF3, 25) as szF3,
left(szF4, 25) as szF4,
left(szF5, 25) as szF5,
left(szF6, 25) as szF6,
left(szF7, 25) as szF7,
left(szF8, 25) as szF8
from dbo.F_Report_data
where isDate(szF1) = 0
or isNumeric (szF3 + 'D0') = 0
or isDate(szF4) = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0
or isDate(szF8) = 0
Yes it returned a result, it's good that it returned a result but does that mean that we can/cannot convert/cast a varchar as a numeric? Thanks for your help!!
|||Please post a sampling of the results that you received. It means that you will might either need to change the table or modify the way you display the data so that it is properly "clensed" -- you have dirty data.
|||Here are the results:
Code Snippet
01/24/2007 09:58:03 AM admin 207 01/24/2007 02:08:55 PM 0 0 2007012401/25/2007 01:55:19 PM test 0 01/25/2007 04:11:09 PM 1 0 Unable to read case numbe 20070125
| szf1| |szF2| szF3 |--szF4-| szF5 sz F6 |-szF7-| |szF8|
I just noticed that the query trimmed szF7 (Where it says "Unable to read case numbe"), I will need that field a little larger for the text. I never would have thought that there would be so much trouble to convert/cast a varchar to a numeric in a view.|||That did not display the same way it did on my screen when I was typing it. I hope it isn't too confusing.|||
Can somebody point Erik to an article about cleaning up data? My tests weren't strong enough and I really am not interested in wasting Erik's time. I suspect that blanks in his data caused the isNumeric tests to fail.
Erik:
You can try this query; it will exhibit which test is failing:
Code Snippet
select isDate(rtrim(szF1)) as szF1isDate,I feel like I need a fresh set of eyes on this at this point. Help?
|||Here are the results of the query. I don't really know what they are saying though, could you give me pseudo code explanation of the query?
0,1,0,1,1,0,,Blank,0,,0,0,,
1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518
Erik:
To me the problem here is that the columns are not sufficiently typed; this is a design problem that should be fixed. If a column is intended to be used as a number it should be typed as numeric. Similarly, if a column is going to be used as a date it should be typed as a datetime column, not as a varchar. Here is the basic response to the records returned from the query:
0,1,0,1,1,0,,Blank,0,,0,0,,
this record failed for 3 reasons:
(1) The szF1 field is not a valid date (it is an empty string)
(2) The szF4 field is not a valid date (it is an empty string)
(3) the szF8 field is not a valid date (it is an empty string)
1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
this record faild for two reasons:
(1) The szF5 field is not numeric (it is an empty string)
(2) the szF6 field is not numeric (it is an empty string)
]
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)
Now, you might be able to use the NULLIF function to get around these problems since all of these are manifest when the column is an EMPTY string. If you are wanting to test for NUMERIC columns you might also want to give a look to this article about problems with the "isNumeric" built-in function:
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
Hi,
Thank you in advance for your comments/suggestions. I am trying to create a View of a Table. The table is created by another application so I am unable to recreate it they way I want, also the data that is in the columns that I want to CAST are "numbers" not letters and will only be numbers. In the view I need certain columns to be CAST as numeric from varchar.
Here is the syntax that I am currently using:
Code Snippet
SELECT CAST(szF1 AS datetime) AS [Login Date/Time], szF2 AS [User Name], CAST(szF3 AS numeric) AS [Documents Indexed], CAST(szF4 AS datetime) AS [Logout Date/Time], CAST(szF5 AS numeric) AS [Documents Sent to QC], CAST(szF6 AS numeric) AS [Documents Reconciled], szF7 AS [Reject Reason], CAST(szF8 AS datetime) AS [Report Date]
FROM dbo.F_Report_Data AS a
WHERE (szF3 <> 'Blank')
When I open the view I get the error message about converting varchar to numeric.
Thanks,
Erik
Try running this query:
Code Snippet
SELECT CAST(szF1 AS datetime) AS [Login Date/Time],I tried your suggestion and I got an error: "Error in list of function arguments: '=' not recognized. Unable to parse query text.
|||It's likely because you have data in the szF5 or szF6 columns that can't be converted to numeric. For example, if I had the value aaa in szF5, I would get that error. More common is if I have a zero length string in the column, that can't be converted to numeric and I would get the error. A null would be fine but a zero length string would cause the error.
-Sue
|||I would suggest (1) give the schema of the table and (2) give 5 sample rows of data from the table by doing a
select top 5 * from F_Report_Data
|||The 3 columns that I want to cast as numeric have only numbers in them.
1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM
The items in bold are thecolumns that I am trying to covnert/cast as numeric.
|||The 3 columns that I want to cast as numeric have only numbers in them.
Code Snippet
1/24/2007 9:58:03 AM admin 207 1/24/2007 2:08:55 PM 0 0 1/24/2007 12:00:00 AM
1/24/2007 9:59:03 AM admin 0 1/24/2007 4:09:25 PM 1 0 Unable to read case number 1/24/2007 12:00:00 AM
1/24/2007 9:56:03 AM admin 0 1/24/2007 4:26:33 PM 0 3 1/24/2007 12:00:00 AM
1/25/2007 1:55:19 PM admin 0 1/25/2007 3:32:51 PM 0 0 1/25/2007 12:00:00 AM
1/25/2007 1:55:19 PM test 0 1/25/2007 4:11:09 PM 1 0 Unable to read case number 1/25/2007 12:00:00 AM
The items in bold are thecolumns that I am trying to covnert/cast as numeric.
Could you clarify by what you mean "Schema", it has been a while since my DB class and I am not a DBA. Every column is varchar(8000),null except for the PK which is (int, not null).
I awm giong to attempt to see if I can get the necessary results w/o convert/cast because there is supposed to be implicit conversion of varchar to numeric.
|||Actually, you have answered the schema question -- all columns are varchar(8000) except for the PK which is integer -- a "wow" table. That should be enough for now. Try running this query and see if any results are returned:
Code Snippet
select left(szF1, 25) as szF1,
left(szF2, 25) as szF2,
left(szF3, 25) as szF3,
left(szF4, 25) as szF4,
left(szF5, 25) as szF5,
left(szF6, 25) as szF6,
left(szF7, 25) as szF7,
left(szF8, 25) as szF8
from dbo.F_Report_data
where isDate(szF1) = 0
or isNumeric (szF3 + 'D0') = 0
or isDate(szF4) = 0
or isNumeric (szF5 + 'D0') = 0
or isNumeric (szF6 + 'D0') = 0
or isDate(szF8) = 0
Yes it returned a result, it's good that it returned a result but does that mean that we can/cannot convert/cast a varchar as a numeric? Thanks for your help!!
|||Please post a sampling of the results that you received. It means that you will might either need to change the table or modify the way you display the data so that it is properly "clensed" -- you have dirty data.
|||Here are the results:
Code Snippet
01/24/2007 09:58:03 AM admin 207 01/24/2007 02:08:55 PM 0 0 2007012401/25/2007 01:55:19 PM test 0 01/25/2007 04:11:09 PM 1 0 Unable to read case numbe 20070125
| szf1| |szF2| szF3 |--szF4-| szF5 sz F6 |-szF7-| |szF8|
I just noticed that the query trimmed szF7 (Where it says "Unable to read case numbe"), I will need that field a little larger for the text. I never would have thought that there would be so much trouble to convert/cast a varchar to a numeric in a view.|||That did not display the same way it did on my screen when I was typing it. I hope it isn't too confusing.|||
Can somebody point Erik to an article about cleaning up data? My tests weren't strong enough and I really am not interested in wasting Erik's time. I suspect that blanks in his data caused the isNumeric tests to fail.
Erik:
You can try this query; it will exhibit which test is failing:
Code Snippet
select isDate(rtrim(szF1)) as szF1isDate,I feel like I need a fresh set of eyes on this at this point. Help?
|||Here are the results of the query. I don't really know what they are saying though, could you give me pseudo code explanation of the query?
0,1,0,1,1,0,,Blank,0,,0,0,,
1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518
Erik:
To me the problem here is that the columns are not sufficiently typed; this is a design problem that should be fixed. If a column is intended to be used as a number it should be typed as numeric. Similarly, if a column is going to be used as a date it should be typed as a datetime column, not as a varchar. Here is the basic response to the records returned from the query:
0,1,0,1,1,0,,Blank,0,,0,0,,
this record failed for 3 reasons:
(1) The szF1 field is not a valid date (it is an empty string)
(2) The szF4 field is not a valid date (it is an empty string)
(3) the szF8 field is not a valid date (it is an empty string)
1,1,1,0,0,1,01/25/2007 01:55:19 PM,admin,19,01/25/2007 04:11:43 PM,,,,20070125
this record faild for two reasons:
(1) The szF5 field is not numeric (it is an empty string)
(2) the szF6 field is not numeric (it is an empty string)
]
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 09:58:22 AM,1,0,Invalid case number,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)
1,0,1,1,1,1,05/18/2007 08:37:01 AM,ATRAIN28,,05/18/2007 10:01:14 AM,0,1,,20070518
this record failed because:
(1) The szF3 field is not numeric (it is an empty string)
Now, you might be able to use the NULLIF function to get around these problems since all of these are manifest when the column is an EMPTY string. If you are wanting to test for NUMERIC columns you might also want to give a look to this article about problems with the "isNumeric" built-in function:
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
