Change column datatype "$2,000" to 2000

Hello,

How do I change the datatype of a column in a CSV file. Preferably in the select statement (apparently Cast, Convert, & Replace functions don't work when selecting from a CSV).

I have a page where users upload their CSV files to and then I use SQLBulkCopy to insert all the records into my sql table. The problem is with columns of money data.

EX: "$2,000" >> 2000

The CSV file interprets the "$2,000" as a string of text. How do I convert it to a decimal so I can insert it into my sql database?

The CSV doesn't interpret anything, CSV files have no data types, it's all just text. You can use a text editor to do a search and replace and remove all the dollar signs and periods if you wish. You should be able to set the SQL data type for currency as well. Or use a DTS job to change the data format instead of doing a bulk copy.

Jeff

|||

Hi Jeff,

As I said, users are uploading CSV files to my site where I use SQLBulkCopy to automatically upload the records to a SQL table. So that means no manual editing of the csv file and no using DTS.

Or can you do a "DTS job" via code? If so I've never done that before and a code example would be much appreciated.

You're right, I shouldn't have said CSV interprets it. I meant while reading from the CSV to a datatable, etc it is interpreted as string data.

Thanks a bunch,

|||

Moderators: please stop marking this post as resolved/answered. It is obvious Jeff's response is NOT the answer. Marking it so only removes the chance of my question actually being answered.

|||

You can probably trigger a DTS job via code, I've never looked at whether this could be done or how. You may need to parse the CSV file in code as it is uploladed, before the bulk copy, to strip the string portions out and format the currency as a number. I'm not sure if a SQLBulkCopy can change data types, I'm not familiar enough with it to say.

Jeff

PS: This isn't the answer either I'm afraid... :)

casting not working?

Can anyone see what's wrong with this transformation?

((DT_STR)([IN-DLN]))

I'm trying to cast DLN as a string, but I keep getting a parsing error in the derived column tranformer...

Thanks!

Jim Work
What *is* the error exactly?

You might want to just try:
(DT_STR)[IN-DLN]|||"Attempt to parse the expression "(DT_STR)[IN-DLN]" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or it might be missing part of a required element such as a parenthesis."

Any ideas? Your suggested syntax didn't work, either.
|||Silly me...

(DT_STR,length in bytes,1252)[IN-DLN]|||When looking at the list of available Type Casts, you can see that the DT_STR type cast requires two arguments.|||Thanks as ever, Phil!

Jim Work

Casting Int to Varchar

I want to cast an In to varchar with a specific number of decimal places. So 10 will come across as 10.00.
Is there an easy way of doing this?Would you be llooking for something like this?

Code:
------------------------------
select cast(cast(10 as money) as varchar(10))
select cast(cast(10 as numeric(12,4)) as varchar(10))
------------------------------|||I think this is what Paul was trying to show:

select cast(cast(id as decimal(5,2)) as varchar(10)) from table

Where id and table are defined by you. The decimal parameters would be determined by your maximum integer.

casting float output param throws an exception.

I keep getting an exception when trying to cast an output param as a float type. the SPROC is marked as float OUTPUT and the Cost column in the database is a float type as well. And there are no nulls in the cloumn either. here is how my code looks:

SqlParameter prmCost= new SqlParameter("@.Cost", SqlDbType.Float,8);
prmCost.Direction=ParameterDirection.Output;
cmd.Parameters.Add(prmCost);
//...blah blah blah
//invalid cast gets throw on here (it happens with all my float types)
productDetails.Cost=(float)prmCost.Value;

Any suggestions as to what I am doing wrong?You have to use the <datatype>.Parse() methods to covert to numbers, e.g.

float test = float.Parse(prmCost.Value.ToString());

|||If you do a watch on prmCost before the error do you see a reasonable value in there?

If so, maybe try the cast with a double on the .Net side and see if you can get it in. Try the code below to help debug. If it still crashes maybe the exception message will be more helpful than your current one.

string message = "";
double doubleValue = 0.0;
try{
string seeWhatsThere = prmCost.Value.ToString();
// put a watch on seeWhatsThere to see what it looks like
doubleValue = System.Double.Parse( seeWhatsThere );
}
catch( FormatException ex){
message = ex.Message;
}
catch( Exception ex ){
message = ex.Message;
}

|||Yes, there is a reasonable value for the parameter (I check the param for a null value before attempting to cast it). I did it as follows, but I'm not sure if I will loose any precision this way. From what I understand Single is the same thing as float, correct me if I'm wrong.

product.Cost=Convert.ToSingle(prmCost.Value);

Thanks for the help.

Casting DT_WSTR to integers in SSIS

Hello, all.

Why is it that, despite what is said in the sketchy SQL Help content, it appears to be impossible to cast a string to an integer in the Expression Builder to generate a value for a variable? More specifically, why does the following expression cause an error?

(DT_UI4) (SUBSTRING(@.[User::FullDataPath], LEN(@.[User:ataPath]) + 1, 2))

I'm iterating over files and using the name of a given file as an ID for an operation. I simply want to grab a file name using the Foreach Loop Container and process that file, while at the same time use the name in another operation. The file name will be something like "2.txt" (full path something like "c:\something\something\2.txt"). I can use string functions to return the file name, which is a number as a string, and it should be no problem to cast that number as a string to a number (an Int32). SQL Server 2005 help has a chart that indicates such a cast is legal.

Maybe it's a crazy thing to be doing. Maybe I have to go about this a completely different way, but casting from "2" to 2 should be possible in the Expression Builder.

Thanks for any help.

I wonder if it doesn't like a period in the data when it tries to cast to an integer....

substring("c:\something\something\2.txt",LEN("c:\something\something") + 1, 2) = "2."|||I see that's not the case, as what I wrote.

Workaround: add a second derived column and in that component, do your cast to DT_UI4. In the first, just perform the substring operation.

I'm not sure that it will allow it in one step because it can't parse the data to determine if it's numeric or not based on the substring operation. That is, when trying to convert the output of substring, you have no idea if it's numeric or not. (YOU do, but SSIS doesn't) When you add a second derived column component, casting a column to DT_UI4 should be straightforward because SSIS is going to imply that it's numeric and can be cast.

I don't like it, but that's my guess. You are always welcome to vent this issue over at http://connect.microsoft.com/sqlserver/feedback.

Phil|||

What's the error message?

-Jamie

|||

Hey, Phil.

Thanks for the response. It turns out that my problem was relying on ALL of my variables being populated at runtime. I didn't bother to specify default values for the variables I had added to my package, because I was populating these variables are runtime. This was working, but once I started using those variables for cast operations, the Expression Builder evaluation mechanism was choking.

What I was doing was this:

1. I was getting a variable folder path from a database and storing that in a variable.

2. I used that folder path variable as an expression for the Foreach Loop Container, wherefrom I derived a full file path (spec) in order to process multiple files.

3. But then I got the brilliant idea of using the folder path and the full file spec to derive a numerical value (based on the file name) and I used that value as an input into an Execute SQL Task to do an auxiliary task.

The bottom line is that once I specified bogus default values at design time (which values were replaced anyway at runtime) for the variables upon which my cast expression depended, the Expression Builder evaluation mechanism was able to validate my cast expressions.

This little idiocy of mine has cost me a few hours of work. But, I should say that I saw nothing about runtime evaluations of design time values DURING DESIGN TIME in the SQL Help.

|||

phanoteus wrote:

This little idiocy of mine has cost me a few hours of work. But, I should say that I saw nothing about runtime evaluations of design time values DURING DESIGN TIME in the SQL Help.

Indeed. So I think you're beig a bit harsh on yourself criticising your actions as idiocy. It doesn't strike me as sensible or intuitive that variables have to be initialised to a value that will never get used. But that's just me.

-Jamie

Casting decimal number

Hello,

When I declare a VB variable Dim s as Decimal,

I want to cast d like this :
1452,41
41,00
45,47
756544,04

Only with to digits after the ","

How can I perform this

Hi,

If my variable d=125,45111

How can I view d like 125,45 ?

|||you mean 2 digits after a "decimal" not a "comma" right?|||

You have two choices use place holder in strings and formatting or set precision and scale. Try the links below for details. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp

http://support.microsoft.com/?kbid=892406

|||

Hi,

If you are asking how to mak this conversion using t-sql, you can use CAST function

You can run and test the result for CAST

DECLARE @.d FLOAT
SET @.d = 125.45111

SELECT @.d, CAST(@.d AS DECIMAL(10,2))

|||

Hi,

Thank I mean two digits after a decimal number ( 44,45 or 4,00 or 7888,01 )

I'm using VB.NET and my variable is declared like Dim x as decimal.

Thanks

casting date stored as integers

Hi
I have a SQL server database that have dates stored like 12142006003423 which means 2006-12-14 00:34:00
is there any way to cast it directly to a datetime type (I don't mean a user defined function as I've already implemented one)

thanks

Eisa:

Did you deliberately truncate the seconds out? And if so do you want the seconds truncated or rounded?

|||no,
it is just a COINCIDENCE|||

Eisa:

Here is a select statement to do this; however, it is a better option to have a front-end application do this formatting rather than have SQL Server perform this formatting:

declare @.dateTime bigint
declare @.dateTime2 bigint
set @.dateTime = 12142006003423
set @.dateTime2 = 8012006010203

select @.dateTime as [Date / Time],
convert (char(4), (@.dateTime/1000000)%10000) + '-' +
right ('0'+convert(varchar(2), (@.dateTime/cast(1000000000000 as bigint))%100), 2) + '-' +
right ('0'+convert(varchar(2), (@.dateTime/cast(10000000000 as bigint))%100), 2) + ' ' +
right ('0'+convert(varchar(2), (@.dateTime/10000)%100), 2) + ':' +
right ('0'+convert(varchar(2), (@.dateTime/100)%100), 2) + ':' +
right ('0'+convert(varchar(2), @.dateTime%100), 2)
as formattedDate

-- Output:

-- Date / Time formattedDate
-- -- -
-- 8012006010203 2006-08-01 01:02:03
-- 12142006003423 2006-12-14 00:34:23

|||Mugambo,
thanks for your interest. however I was looking for a direct cast or convert statement that can accomplish this task
I've created a scalar function for the ease of use as follows
CREATE FUNCTION Int2Date ( @.sdate bigint)
returns datetime as
begin
declare @.dt as varchar(14)
declare @.dd varchar(2)
declare @.mm varchar(2)
declare @.yy varchar(4)
declare @.hh varchar(2)
declare @.mi varchar(2)
declare @.ss varchar(2)
declare @.result varchar(25)

set @.dt = cast(@.sdate as varchar(14))
set @.mm = substring(@.dt,1,2)
set @.dd = substring(@.dt,3,2)
set @.yy = substring(@.dt,5,4)
set @.hh = substring(@.dt,9,2)
set @.mi = substring(@.dt,11,2)
set @.ss = substring(@.dt,13,2)

set @.result = cast (@.mm + '/' + @.dd + '/'+ @.yy + ' ' + @.hh + ':' + @.mi +':' + @.ss as datetime)
return @.result
end

In Oracle a to_date(8012006010203,'DDMMYYYYHHMISS') can do this in a single step i was wondering if SQL server have a similar function

thanks anyway

Cast/Convert value to VB variable

Hi all,
I have a VB 6.0 application that interacts with an MS Access backend. I am in the process of converting it so that it interacts with SQL Server 2000. The current applications uses Cint, CStr, etc. functions which are MS-Access specific, so I am now switching over to Convert (or Cast) function. The problem is that there are certain places wherein I first need to store the value of the resultant CAST/Convert function in a variable and then use that value in a SQL statement. However, I cant seem to figure out a way of storing the results from CAST/CONVERT functions in the VB 6.0 variable. All the examples on Internet show use of these functions directly in an SQL statement e.g. "Select CAST(title as Int) from xyz", etc.
Can anybody tell me how can i get the values to be stored in the variable? I am really stuck here and cant seem to progress.

Thanks in advance for all your help.

Regards:
Prathmeshe.g.

set rs=cmd.execute("select cast(title as int) as title from xyz")

title=rs.fields("title")|||Thanks for the reply oj. However, my requirement is somewhat different. I'll explain the scenario in short. The user chooses a filename to delete, which is stored in the database as a record. It is stored in 2 places in the database. One as a whole filename and in the second place as a breakdown record. The program should delete the filename from the database and also the file from the disk location. The filename in the database is stored as say "XY006CV003A.xls" or "XY005CJ003B.doc" however on the disk they are stored with the above number and the title for the document, concatenated e.g "XY006CV003A test.xls". So I need to extract the file name only which is "XY006CV003A.xls" to match the database record. The last part "003" is sort of a sequence number and is stored in the database. I need to extract the that sequence to match it and delete it from the second place as I have mentioned. '003' when extracted from the filename will be a string and I need to cast it to Integer type to match the record.

e.g.
fname = Split("XY006CV003A.xls",".") gives "XY006CV003A"
seq = Mid(fname, 8, 3) gives '003' which is string format

I now need to use this seq variable in the query

"Select * from XYZ where fileseq=" & seq

fileseq is of integer datatype so I need to cast/convert seq variable to Integer from String.
Can anybody suggest any ideas?

Regards:
Prathmesh

Cast/convert SARGable?

If we use cast or convert in where clauses, will SQL Server 2000 still
treat that as a possible SARG (search argument) with the possibility of
using indexes, or do we lose that ability? I'm thinking in particular
of dates and numbers from text.
Thanks.No, it will not be a SARG. Make sure you use proper datatype for the columns
and you will limit the
need for this. As for datetime searches, I have some tips in
http://www.karaszi.com/SQLServer/info_datetime.asp.
SQL Server does some tricks when you have an (implicit) cast between datatyp
es in the same group
(like between the integer class datatypes). For example (Northwind database)
:
SELECT * FROM [Order Details]
WHERE OrderID = 23
The constant 23 will be treated as a tinyint. OrderID is an int. Since int h
as higher datatype
precedence (see BOL for list), the tinyint will be converted to int. You can
see that in the
execution plan. I.e., the conversion is at the constant side so this is stil
l a SARG. But consider
below:
CREATE INDEX x ON [Order Details](Quantity)
SELECT * FROM [Order Details]
WHERE Quantity = 34556647
The constant 34556647 is an int where Quantity is smallint. So now Quantity
need to be converted, so
you have a CAST on the column side. Look at the execution plan and you will
see those tricks taken
by SQL server in order to use an index in this particular case.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"OrchidPop" <cristoff@.charter.net> wrote in message
news:1124266316.795238.311570@.f14g2000cwb.googlegroups.com...
> If we use cast or convert in where clauses, will SQL Server 2000 still
> treat that as a possible SARG (search argument) with the possibility of
> using indexes, or do we lose that ability? I'm thinking in particular
> of dates and numbers from text.
> Thanks.
>|||Thanks for the reply. I had hoped cast/convert were not considered true
functions to block SARGs.
Actually, I need to check with the original developer of our
application to find out why convert is used all over the
place,particularly for dates.
Thanks!|||> Actually, I need to check with the original developer of our
> application to find out why convert is used all over the
> place,particularly for dates.
Tell me about it... Only one example, I helped a customer with a query a few
ws ago that had a
date range condition and changing it to a SARG cut down I/O from 777,000 to
2,500.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"OrchidPop" <cristoff@.charter.net> wrote in message
news:1124286059.249743.275670@.g47g2000cwa.googlegroups.com...
> Thanks for the reply. I had hoped cast/convert were not considered true
> functions to block SARGs.
> Actually, I need to check with the original developer of our
> application to find out why convert is used all over the
> place,particularly for dates.
> Thanks!
>

CAST/Convert and performance measurements

Has anyone any testdata on how long a cast/convert from varchar to other datatypes is taking. Is casting a large number of data a major problem for MSSQL 2000?This is what I know.

CAST is the old way that SQL used to change from one data type to another. CONVERT is the preferred method according Microsoft.

I've used CONVERT on tables with a couple hundred thousand rows and didn't really see a large degradation in performance.

Hope this helps.|||CAST has simpler syntax, CONVERT has more functionality. They both work fast, and if you look at the documentation for them you'll see that SQL Server will do many conversion implicitily, so that you don't even need to use CAST or CONVERT.

blindman

cast('0.5' as numeric) + cast('0.5' as numeric) = 2 ???

Hi folks,
I want to talk about data types. I was of the impression that numeric
was a fixed data type. Why does sql 2005 exhibit the above behaviour
when casting to this datatype from a string?
Cheers,
Alex
The NUMERIC data type has precision and scale. Since you did not specify
scale in your CAST, the default scale of 0 is used. In that case the number
gets rounded to the decimal point and each CAST results in 1 for total of 2.
To CAST correctly you can specify scale 1:
SELECT CAST('0.5' AS NUMERIC(5, 1)) + CAST('0.5' AS NUMERIC(5, 1))
This will correctly return 1.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

cast('0.5' as numeric) + cast('0.5' as numeric) = 2 ???

Hi folks,
I want to talk about data types. I was of the impression that numeric
was a fixed data type. Why does sql 2005 exhibit the above behaviour
when casting to this datatype from a string?
Cheers,
AlexThe NUMERIC data type has precision and scale. Since you did not specify
scale in your CAST, the default scale of 0 is used. In that case the number
gets rounded to the decimal point and each CAST results in 1 for total of 2.
To CAST correctly you can specify scale 1:
SELECT CAST('0.5' AS NUMERIC(5, 1)) + CAST('0.5' AS NUMERIC(5, 1))
This will correctly return 1.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

CAST vs CONVERT

What's your opinion? Should I encourage our developers to use CAST or
CONVERT? Personally, I would think CAST should be preferred since it's
the ANSI SQL-92 syntax and CONVERT should only be used where you need to
specify the datetime conversion "style" when converting to a string.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.comSeems like personal preference to me... Personally, I usually use CONVERT, j
ust because I like the syntax more.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:eeG
ZFLHGFHA.1456@.TK2MSFTNGP09.phx.gbl...
What's your opinion? Should I encourage our developers to use CAST or CONVE
RT? Personally, I would think CAST should be preferred since it's the ANSI
SQL-92 syntax and CONVERT should only be used where you need to specify the
datetime conversion "style" when converting to a string.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com|||I agree with you. I like to use CAST whenever possible. But I don't
think it's ideal, since you still need CONVERT when dealing with dates.
I'd be interested in knowing if anyone has encountered any differences
between the two functions.
David Gugick
Imceda Software
www.imceda.com
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:eeGZFLHGFHA.1456@.TK2MSFTNGP09.phx.gbl...
What's your opinion? Should I encourage our developers to use CAST or
CONVERT? Personally, I would think CAST should be preferred since it's
the ANSI SQL-92 syntax and CONVERT should only be used where you need to
specify the datetime conversion "style" when converting to a string.|||>> Should I encourage our developers to use CAST or CONVERT?
Personally, I would think CAST should be preferred since it's the ANSI
SQL-92 syntax <<
Of course, and for the reason you gave. Would you require them to
write documentation in a local slang or Standard English?
conversion "style" when converting to a string. <<
I don't much like CONVERT() even for that. Standard SQL uses only the
ISO-8601 format, as do the rest of the ISO standards. Let the local
front end decide how to do the display, so the local user sees it in
whatever format his tribe wants.|||>> Should I encourage our developers to use CAST or CONVERT?
Personally, I would think CAST should be preferred since it's the ANSI
SQL-92 syntax <<
Of course, and for the reason you gave. Would you require them to
write documentation in a local slang or Standard English?
conversion "style" when converting to a string. <<
I don't much like CONVERT() even for that. Standard SQL uses only the
ISO-8601 format, as do the rest of the ISO standards. Let the local
front end decide how to do the display, so the local user sees it in
whatever format his tribe wants.|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eEDgIPHGFHA.228@.TK2MSFTNGP15.phx.gbl...
> I agree with you. I like to use CAST whenever possible. But I don't
> think it's ideal, since you still need CONVERT when dealing with dates.
> I'd be interested in knowing if anyone has encountered any differences
> between the two functions.
A couple of years ago I recall doing some sort of test for a project I
was working on and determining that convert was faster than cast. But I
have no recollection of what or how I tested. I just ran a quick test and
found them to be identical in the case of converting strings to integers...
So I'm not sure that my test then was valid... It would be interesting to do
a comprehensive test of different available casts and find out whether one
or the other really is faster.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Cast varchar to decimal

I am losing my hair...and my mind...
Is there any reason why I wouldn't be able to cast a varchar value of say
7.8 to decimal?
I have a whole bunch of lab results that come with a bunch of garbage in the
result column. I have stripped it away so that it is only a format
[1-x].[0-9]. I want to make it a number so I can identify High and low value
s
for each patient.
Am I missing something...besides my mind?
Thanks in advance,Don't see any problem in achieving what you want.
Can you post a sample to exactly understand what your issue is?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:CC2448DB-9147-4391-BBDA-7891A6E3294F@.microsoft.com...
>I am losing my hair...and my mind...
> Is there any reason why I wouldn't be able to cast a varchar value of say
> 7.8 to decimal?
> I have a whole bunch of lab results that come with a bunch of garbage in
> the
> result column. I have stripped it away so that it is only a format
> [1-x].[0-9]. I want to make it a number so I can identify High and low
> values
> for each patient.
> Am I missing something...besides my mind?
> Thanks in advance,
>
>|||Here is the code... pretty straight forward....
There is something still contained in the string that is messing up the
cast. (See raw data below code)
select top 7
patientid,
decodedvalue,
convert(varchar,replace(replace(replace(
decodedvalue,'
',''),'>',''),'%','')) value,
-- cast(convert(varchar,replace(replace(r
eplace(decodedvalue,'
',''),'>',''),'%','')) as float) value,
len(replace(replace(replace(decodedvalue
,' ',''),'>',''),'%','')) str_length
from
#diabetes_results
where
decodedvalue like '%.%'
order by
patientid
58 6.8 % 6.8 3
58 7.6 % 7.6 3
58 6.7 % 6.7 3
58 7.1 % 7.1 3
58 6.2 % 6.2 3
168 7.5 % 7.5 3
168 7.5 7.5 5
Note the length of '5' in the final record though it is obvious the length
should be 3. I trimmed the column of spaces but they remain.
Any thoughts?
Thanks in advance,
"SriSamp" wrote:

> Don't see any problem in achieving what you want.
> Can you post a sample to exactly understand what your issue is?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:CC2448DB-9147-4391-BBDA-7891A6E3294F@.microsoft.com...
>
>|||168 7.5 7.5 5
Perhaps those aren't spaces - in fact I think they are one carriage return
and one line feed character. How are these values inserted? You should
prevent illegal values from being entered at all.
In the mean time - remove char(13) and char(10) from the string.
ML
http://milambda.blogspot.com/

CAST statement

It must be something I'm overlooking but I keep getting an error message that this statement can't parse.

UPDATE product SET supplier = LEFT(supplier,LEN(supplier-4)) + CAST( '2100' AS varchar(4)) WHERE actualid = 'IS2100-CO2-CO2-0-4-I'

Any help would be greatly appreciated."LEFT(supplier,LEN(supplier-4))" should be "LEFT(supplier,LEN(supplier)-4)"

D'oh!

Also, you don't need to cast a numeric value that is already a string...|||"LEFT(supplier,LEN(supplier-4))" should be "LEFT(supplier,LEN(supplier)-4)"

D'oh!

Also, you don't need to cast a numeric value that is already a string...

thanks for the help i realized after it was too late:D

Cast Question - Converting Datetime into Date

Hi guys,
I knew that the Function Cast can do this but I tried a lot and I dont want to use the MONTH, YEAR, DAY function.

I have a smalldatetime field with a value of this 12/18/2004 4:02:00 PM
I just like to see it like this 12/18/2004

Hope you can help me up. Thanks.
-vinceSELECT CONVERT(varchar(10),GetDate(),101)

Got Books Online (BOL)?|||declare @.val datetime
set @.val='12/18/2005 11:00:00'
select @.val, convert(char(10),@.val,101)|||SELECT CONVERT(varchar(10),GetDate(),101)

Got Books Online (BOL)?

I have it here.. Thanks a lot...Sometimes, when you have a lot of things in your mind, you cant see exactly the answer which is sometimes in your face already. ;)

hehehe

Cast question

I'm trying to set up a query to check several fields for the value of 'Debug
'
for example. I've been successful in doing this with LIKE 'Debug' or LIKE
'Debug%'
however, I'd like to do an = comparison. Some fields are varchar, some are
text. The text ones seem to be a problem.
Is there a way to do a Cast, so that I can do an = comparison with these
types of fields?>> Is there a way to do a Cast, so that I can do an = comparison with these
If the length of the TEXT values are less than 8000, you can cast them to
VARCHAR type.
Anith

cast question

if i declare a variable within a stored procedure as a varchar, then later
cast it to an integer- will it act as an integer for all subsequent
references after the cast?
example:
declare @.myVar varchar(2)
set @.myVar = '2'
set @.myVar = cast(@.myVar as integer)
select @.myVar -- what data type is my variable now? is it an integer
because of the previous cast?
or would i need to specify: "select
cast(@.myVar as integer)" in order for it to be considered an integer?
thanks much,
jTJT,
DECLARE @.MYVAR VARCHAR(2)
SET @.MYVAR = '2'
SELECT ISNUMERIC(@.MYVAR)
SELECT 'THIS IS A TEST ' + @.MYVAR
SET @.MYVAR = CAST(@.MYVAR AS INTEGER)
SELECT ISNUMERIC(@.MYVAR)
SELECT 'THIS IS A TEST ' + @.MYVAR
RESULTS:
1
THIS IS A TEST 2
1
THIS IS A TEST 2
HTH
Jerry
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>|||Since you declared it to be varchar(2), that's how it will remain for the
batch.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
if i declare a variable within a stored procedure as a varchar, then later
cast it to an integer- will it act as an integer for all subsequent
references after the cast?
example:
declare @.myVar varchar(2)
set @.myVar = '2'
set @.myVar = cast(@.myVar as integer)
select @.myVar -- what data type is my variable now? is it an integer
because of the previous cast?
or would i need to specify: "select
cast(@.myVar as integer)" in order for it to be considered an integer?
thanks much,
jT|||Note however that this will cause the string concatenation to fail where the
others worked:
SELECT 'THIS IS A TEST ' + CAST(@.MYVAR AS INTEGER)
Results:
Server: Msg 245, Level 16, State 1, Line 7
Syntax error converting the varchar value 'THIS IS A TEST ' to a column of
data type int.
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eYsPhT1zFHA.3896@.TK2MSFTNGP10.phx.gbl...
> JT,
> DECLARE @.MYVAR VARCHAR(2)
> SET @.MYVAR = '2'
> SELECT ISNUMERIC(@.MYVAR)
> SELECT 'THIS IS A TEST ' + @.MYVAR
> SET @.MYVAR = CAST(@.MYVAR AS INTEGER)
> SELECT ISNUMERIC(@.MYVAR)
> SELECT 'THIS IS A TEST ' + @.MYVAR
> RESULTS:
> --
> 1
>
> --
> THIS IS A TEST 2
> --
> 1
> --
> THIS IS A TEST 2
> HTH
> Jerry
> "JT" <jt@.nospam.com> wrote in message
> news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
>|||thanks much!
jT
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e1hiGX1zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Note however that this will cause the string concatenation to fail where
the
> others worked:
> SELECT 'THIS IS A TEST ' + CAST(@.MYVAR AS INTEGER)
> Results:
> Server: Msg 245, Level 16, State 1, Line 7
> Syntax error converting the varchar value 'THIS IS A TEST ' to a column of
> data type int.
> HTH
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eYsPhT1zFHA.3896@.TK2MSFTNGP10.phx.gbl...
integer
>|||Just to add, you are casting the value in @.myVar to integer, not the @.myVar
variable. Note also that you don't have to cast it to an integer just get
use it as an integer. If you said
select 1 + '1'
It will return:
2
But if you enter
select 1 + 'bob'
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'bob' to a column of data type
int.
It shouts "WRONG" at you, just as if you did:
select cast('bob' as int)
So be careful with this sort of operaton, because when you cast a varchar to
an int it will not just return NULL, it gives an error.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JT" <jt@.nospam.com> wrote in message
news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>|||In addition to the other posts, check out "data type precedence" in Books On
line. Will probably
explain a lot for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JT" <jt@.nospam.com> wrote in message news:Ol$KsP1zFHA.3000@.TK2MSFTNGP12.phx.gbl...arkred">
> if i declare a variable within a stored procedure as a varchar, then later
> cast it to an integer- will it act as an integer for all subsequent
> references after the cast?
> example:
> declare @.myVar varchar(2)
> set @.myVar = '2'
> set @.myVar = cast(@.myVar as integer)
> select @.myVar -- what data type is my variable now? is it an integer
> because of the previous cast?
> or would i need to specify: "select
> cast(@.myVar as integer)" in order for it to be considered an integer?
>
> thanks much,
> jT
>

Cast problems with CLR

hi,

i have looked at several guides, and have come up with some code, although for some reason i am unable to get it to work, and receive an error:

Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Object[]'.
System.InvalidCastException:
at UserDefinedFunctions.FillRow(Object obj, String& PID)
.

Below is my code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using System.Collections;

public partial class UserDefinedFunctions
{
const string SELECT =
@."SELECT pointData.PID
FROM pointData
INNER JOIN pointDevices ON pointData.PID = pointDevices.PID
WHERE pointDevices.UUID = @.UUID AND
DATEADDED >= @.DATE_START AND
DATEADDED <= @.DATE_STOP
ORDER BY DATEADDED";

[SqlFunction(FillRowMethodName = "FillRow", DataAccess = DataAccessKind.Read, TableDefinition="PID nvarchar(50)")]
public static IEnumerable createJourney(SqlString UUID, DateTime DATE_START,DateTime DATE_STOP, SqlDouble JOURNEY_DELAY, SqlDouble JOURNEY_DISTANCE)
{
ArrayList RAW_PID_LIST = new ArrayList();

using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();

SqlCommand command = new SqlCommand(SELECT,conn);

command.Parameters.AddWithValue("@.UUID",UUID);
command.Parameters.AddWithValue("@.DATE_START",DATE_START);
command.Parameters.AddWithValue("@.DATE_STOP",DATE_STOP);

SqlDataReader reader = command.ExecuteReader();

using (reader)
{
while (reader.Read())
{
RAW_PID_LIST.Add(reader[0]);
}
}
}

return RAW_PID_LIST;
}

private static void FillRow(Object obj, out string PID)
{
object[] row = (object[])obj;

PID = (string)row[0];
}
};

could someone give me a clue as to why i might be getting this error please.

thank you.

FillRow gets only one object at a time. So, you should try this one:

private static void FillRow(Object obj, out string PID)
{
PID = (string)obj;
}

CAST Problem Continued

Thanks for previous help, I am however continuing to have problems as I attempt to search a table for a value keyed by the user (e.g. AccessionPresent= 3624-01). The input value that is being searched against is stored in the table as an INT. I have cast the INT to a VARCHAR on the line described as "MSQL=...

Can anyone ascertain why I get the error, described below? The query works in the Query Analyzer?? What am I doing incorrectly... Any assistance will be appreciated...

Thanks !

Dim DSAs DataSet
Dim MyCommandAs SqlDataAdapter
Dim AccessionPresent, strsearchedfor, strsearchresultAsString
Dim RcdCountAsInteger
Dim ResultCountAsInteger
AccessionPresent = Accession.Text
strsearchedfor ="The Keyed Accession Number "
strsearchresult =" Does Not Exist. Please Verify Your Entry. "
ErrorLabel.Text =String.Format("{1}<b><font size=ex-small color=003399>{0}</b></font>{2}", AccessionPresent, strsearchedfor, strsearchresult)
Dim MySQLAsString

MySQL ="SELECT * FROM ClinicalSpecimen WHERE CAST(SpecimenID AS varchar(50))='" & AccessionPresent &"'"

Dim objConnValidateAs SqlConnection
Dim mySettingsValidateAsNew NameValueCollection
mySettingsValidate = AppSettings
Dim strConnAsString
strConn = mySettingsValidate("connString")
objConnValidate =New SqlConnection(strConn)

MyCommand =New SqlDataAdapter(MySQL, strConn)
DS =New DataSet
MyCommand.Fill(DS,"SpecimenID")
RcdCount = DS.Tables("SpecimenID").Rows.Count.ToString()

If DS.Tables(0).Rows.Count > 0Then
Response.Redirect("Accession_Result.aspx?AccessionResult=" & AccessionPresent)

ElseIf DS.Tables(0).Rows.Count = 0Then
Response.Redirect(http://www.aol.com)

IS CAUSING THE FOLLOWING ERROR:

Server Error in '/ClinicTest2' Application.

---

Syntax error converting the nvarchar value '3624-01' to a column of data type int.

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: Syntax error converting the nvarchar value '3624-01' to a column of data type int.

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): Syntax error converting the nvarchar value '3624-01' to a column of data type int.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlDataReader.HasMoreRows() +194 System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +262 System.Data.SqlClient.SqlDataReader.Read() +29 System.Data.ProviderBase.DataReaderContainer.Read() +26 System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +240 System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +257 System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +383 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +251 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +308 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +152 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2859 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +84 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +153 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +99 System.Web.UI.WebControls.GridView.DataBind() +23 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +92 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +100 System.Web.UI.Control.EnsureChildControls() +134 System.Web.UI.Control.PreRenderRecursiveInternal() +109 System.Web.UI.Control.PreRenderRecursiveInternal() +233 System.Web.UI.Control.PreRenderRecursiveInternal() +233 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4436

You have to remove the - in 3624-01 from AccessionPresent before casting the nvarchar to int.

You may use replace function.

Example:

1replace(MyString,'-','')-- replace any - in MyString with nothing

Good luck.

|||

Thank you... Ive tried that, as :

replace(AccessionPresent,"-","")

However, I don't believe that this resolves the issue since the "int" is in the table as 3624-01 and if I remove the "-" from AccessionPresent then I have "362401" which does not match the int value (3624-01)...

|||

How can a value of 3624-01 fit in an int column? Isn't this just a display value? This value can't simply be put in the integer datatype of .NET. So you have to get rid of the dash, to convert it to an integer.

|||

OK.. so forgive me... please I was mistaken... SpecimenID is a tyoe VarChar... and the query works correctly in the Query Analyzer...

SELECT ClinicalID, SpecimenID, PatientID, LabID, Accession, Bacillus, Francisella, Yersinia, Brucella, Burkholderia, Coxiella, Staphylococcus, Other,
OtherExplanation, CollectionDate, strddlTransportMedium, strddlSpecimenSource, UserName, Test, SpecimenCount, DateAndTime
FROM ClinicalSpecimen
WHERE (SpecimenID = '3131-04')

So, what else can be incorrect to get the Stack Trace message below...

Can it be something in the code listed here? as the error message does not make sense.. to me at least??'

Thanks !!

-------------

Dim objConnValidateAs SqlConnection

Dim mySettingsValidateAsNew NameValueCollection

mySettingsValidate = AppSettings

Dim strConnAsString

strConn = mySettingsValidate("connString")

objConnValidate =New SqlConnection(strConn)

' Panel1.Visible = "true"

MyCommand =New SqlDataAdapter(MySQL, strConn)

DS =New DataSet

MyCommand.Fill(DS,"ClinicalSpecimen")

RcdCount = DS.Tables("ClinicalSpecimen").Rows.Count.ToString()

If DS.Tables(0).Rows.Count > 0Then

Response.Redirect("Accession_Result.aspx?AccessionResult=" & AccessionPresent)

ElseIf DS.Tables(0).Rows.Count = 0Then

Response.Redirect("http://www.aol.com")

------------

STACK TRACE:

Syntax error converting the nvarchar value '3131-04' to a column of data type int.

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: Syntax error converting the nvarchar value '3131-04' to a column of data type int.

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): Syntax error converting the nvarchar value '3131-04' to a column of data type int.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlDataReader.HasMoreRows() +194 System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +262 System.Data.SqlClient.SqlDataReader.Read() +29 System.Data.ProviderBase.DataReaderContainer.Read() +26 System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +240 System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +257 System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +383 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +251 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +308 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +152 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2859 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +84 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +153 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +99 System.Web.UI.WebControls.GridView.DataBind() +23 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +92 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +100 System.Web.UI.Control.EnsureChildControls() +134 System.Web.UI.Control.PreRenderRecursiveInternal() +109 System.Web.UI.Control.PreRenderRecursiveInternal() +233 System.Web.UI.Control.PreRenderRecursiveInternal() +233 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4435

|||

FORGIVE ME ALL, DUMBO, DUMBO ME!

After wracking what's left of my brain I finally found my answer... again, plainly put in front of my face... The error was not as I was supposing, happening in the query page, but it was in the resulting "Response Redirect" page where the errror WAS that I was trying to Match a VARCHAR and INT... It wasnt clear from the Stack Trace, until I looked in the address bar to see the page it was referencing... when I corrected the offending field that I was searching on the problem went away !!!

Once again... at least I learned from your posts how to CAST... So, Thanks for all your HELP !

|||

HiSmokinJoe,

Please make Answer the post(s) that help you; not your post.

This will help others to get the answer right away. I would like to suggest adding some tags to help seach engines as well.

Thanks SmokinJoe.