Converting delimited varchar @parameter for use in NOT IN()

I am creating a stored procedure which is passed a comma delimited string of
ids as a varchar datatype. The param is to be used in an SQL statement such
as:
CREATE PROCEDURE GetFromTable
@.IDs varchar(255)
AS
SELECT * FROM table WHERE iId NOT IN(@.IDs)
GO
The problem is that the iId field is of datatype int, so i get an error
converting the varchar datatype @.IDs to int.
I can not use dynamic SQL as i am not able to give table level access. It
has to be via EXEC rights on the stored procedure.
Any Help?
Thanks
PatrickArrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
AMB
"Patrick Russell" wrote:

> I am creating a stored procedure which is passed a comma delimited string
of
> ids as a varchar datatype. The param is to be used in an SQL statement suc
h
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>
>|||You cannot do this "this way". You'd need to parse your string,
load the values into a TABLE variable and then reference your
table variable:
SELECT * FROM table WHERE iId NOT IN (select myid from @.MyTableVariable)
These two articles will help:
http://www.eggheadcafe.com/articles/20001002.asp
http://www.eggheadcafe.com/PrintSea...asp?LINKID=529
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Patrick,
Parse the @.IDs into rows of a temp table or table variable then use a join
or a subselect. The in operator will not take a variable like this without
building dynamic SQL.
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Hi Patrick.
You could write a function like...
-- pseudo code
create function udtSplitIDs( @.ids varchar(1000) )
returns @.IDsTable table
(
id int
)
as
begin
while (get position of comma)
begin
insert @.IDsTable values( @.strValue )
find next comma
end
return @.IDsTable
end
your select could then be:
SELECT * FROM table
WHERE iId NOT IN(SELECT * FROM udtSplitIDs(@.IDs))
Bryce|||Out of curiousity. A query like that should be avoided if possible in a
high-performance situation due to performance issues, I assume?

Converting datatype on column...

What impact will changing the datatype of a column from
smalldatetime to datetime? Currently, I need to allow for record insertion
where the date may exeed year 2079.Eric wrote:
> What impact will changing the datatype of a column from
> smalldatetime to datetime? Currently, I need to allow for record
> insertion where the date may exeed year 2079.
That should be fine. The column will require twice the number of bytes
of storage in order to support datetime (8 bytes total). Newly inserted
values will show additional precision unless you account for this.
create table ABC (MyDate smalldatetime)
insert into dbo.ABC values (getdate())
insert into dbo.ABC values ('2005-01-10T14:22:22')
Select * from dbo.ABC
MyDate
--
2005-09-01 17:45:00
2005-01-10 14:22:00
Alter Table dbo.ABC
ALTER COLUMN MyDate DATETIME
Select * from dbo.ABC
MyDate
--
2005-09-01 17:45:00.000
2005-01-10 14:22:00.000
insert into dbo.ABC values (getdate())
insert into dbo.ABC values ('2005-01-10T14:22:22')
Select * from dbo.ABC
MyDate
--
2005-09-01 17:45:00.000
2005-01-10 14:22:00.000
2005-09-01 17:46:57.827
2005-01-10 14:22:22.000
drop table ABC
David Gugick
Quest Software
www.imceda.com
www.quest.com

Converting a string of binary numbers to a binary datatype

I have a varchar field which holds numeric (binary) data e.g. '00101111' and I want to convert this to a binary data type with the value e.g. 0x00101111

But when I try the following SQL:

select top 5 flag2,convert(binary,flag2) flag2_as_binary from my_table

I get:

flag2 flag2_as_binary
--- -------------------
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000100 0x303030303031303000000000000000000000000000000000 000000000000
00000100 0x303030303031303000000000000000000000000000000000 000000000000

(5 row(s) affected)

I want some SQL that will return the following (with flag2_as_binary as a real binary datatype):

flag2 flag2_as_binary
--- -------------------
00000000 0x00000000
00000000 0x00000000
00000000 0x00000000
00000100 0x00000100
00000100 0x00000100

(5 row(s) affected)

Thanks.The sql binary datatype is actually displayed hexidecimal base 16 usinge characters 0-9 and A-F, not base 2.

convert to a valid datetime

have a field that has date value, but the datatype is nvarchar(50)The value is 08/04/2006 23:58:51. When I use the convert to datetime statement, it errors our with the foll error:

How do I convert the datatype to a valid datetime?

select convert(datetime ,08/04/2006 23:58:51)- this errors out with the foll err:

Arithmetic overflow error converting expression to data type datetime.

Both of these work:

DECLARE @.MyDate nvarchar(50)

SET @.MyDate = '08/04/2006 23:58:51'

SELECT CONVERT(datetime ,@.MyDate)

SELECT CONVERT(datetime ,'08/04/2006 23:58:51')

|||

i already use the second and when come to record 500000 this error apper

i check the row nothing change

|||Does that record only contain this exact value '08/04/2006 23:58:51'?...no special characters?|||Use this:

UPDATE

SET DateField = CASE WHEN ISDATE(CharField) THEN CONVERT(datetime ,CharField) ELSE NULL END

Then:

SELECT * FROM TABLE WHERE DateField IS NULL AND CharField IS NOT NULL

And see what is not working and handle them by hand.

|||Thanks Tom it works

convert to a valid datetime

have a field that has date value, but the datatype is nvarchar(50)The value is 08/04/2006 23:58:51. When I use the convert to datetime statement, it errors our with the foll error:

How do I convert the datatype to a valid datetime?

select convert(datetime ,08/04/2006 23:58:51)- this errors out with the foll err:

Arithmetic overflow error converting expression to data type datetime.

Both of these work:

DECLARE @.MyDate nvarchar(50)

SET @.MyDate = '08/04/2006 23:58:51'

SELECT CONVERT(datetime ,@.MyDate)

SELECT CONVERT(datetime ,'08/04/2006 23:58:51')

|||

i already use the second and when come to record 500000 this error apper

i check the row nothing change

|||Does that record only contain this exact value '08/04/2006 23:58:51'?...no special characters?|||Use this:

UPDATE

SET DateField = CASE WHEN ISDATE(CharField) THEN CONVERT(datetime ,CharField) ELSE NULL END

Then:

SELECT * FROM TABLE WHERE DateField IS NULL AND CharField IS NOT NULL

And see what is not working and handle them by hand.

|||Thanks Tom it works

convert row to column?

Hi,
I have a row with 53 columns. All but the first three are of the same datatype.
I want to count the number of columns, out of the last 48 in that row, that have the same value. Is there a nifty way to do this with SQL or T-SQL?

Sample row:

a,b,c,1,1,1,2,1,2,2,2,3,3,3,4,4, etc

The values are not static. I want toknow how many 1's how many 2's etc.

Of course within the app I could for example put the row in a .net datarow and loop through the columns.

Thanks,
B.

my first thought was to insert all the column values into a temp table then do a select distinct on it. not very efficient though|||

The real issue is how you want the data returned. Normally, you'd want something like a data row for every a,b,c, followed, by the number, followed by the count of that number. For your example, you would have 4 rows (or more):
a,b,c,1,3
a,b,c,2,4
a,b,c,3,3
a,b,c,4,2

The old fashioned way would be to create a monster statement like:

SELECT col1,col2,col3,col4
UNION ALL
SELECT col1,col2,col3,col5
UNION ALL
SELECT col1,col2,col3,col6
...

Once you have that done (all 48 UNIONED), then you can play with it:

SELECT col1,col2,col3,col4,COUNT(*) as [Count]
FROM ( {Insert the SELECT/UNION/SELECT/UNION Here} ) t1
GROUP BY col1,col2,col3,col4

You can possibly avoid some of this by using the new UNPIVOT clause as well, but I'm not familiar enough to rattle it's syntax off the top of my head.

|||

Here is unpivot solution:

SELECT

col1, col2, col3, cols,count(cols)as myColCountFROM

(

select

col1, col2, col3, myCol, colsFROM

(

SELECT col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24,

col25

, col26, col27, col28, col29, col30, col31, col32, col33, col34, col35, col36, col37, col38, col39, col40, col41, col42, col43, col44, col45, col46, col47, col48, col49, col50, col51, col52, col53

FROM

onerow2) p

UNPIVOT

(colsFOR myColIN

(

col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24,

col25

, col26, col27, col28, col29, col30, col31, col32, col33, col34, col35, col36, col37, col38, col39, col40, col41, col42, col43, col44, col45, col46, col47, col48, col49, col50, col51, col52, col53

)

)

AS unPvt)as myNewTable

GROUP

BY col1, col2, col3, cols

Limno

|||

Thanks for the reply

The values after the first 3 (a,b,c) are user preferences. I need something like :

Adam, 4, 13
Adam, 3, 7
Adam, 2, 16
Adam, 1, 12

to know that Adam has 13 4's 7 3's etc.

I'll try the monster union statement and the UNPIVOT

Currently I'm inserting in a temptable

|||

Thanks

That's what I'm using now but trying to improve efficiency

convert number to date datatype

easy question here.

I have:
0123
0125
0227
0327
04..
05..
06..
07..
08..
09..
..
1231

The first two numbers is the month and the second two numbers is the day of the month.

I need to display 01 as Jan, 02 as Feb and so on.

left(field1, 2) -- I know this grabs the first two numbers, but how do I convert it to a date data type?dateadd(dd,convert(int,right(field1,2)),dateadd(mm ,convert(int,left(field1,2)),'19000101'))

Convert Money to Varchar(50)

I created a table using sql, and I accidentally made one of the fields a money datatype and I meant to make it a Varchar(50). Is there an easy way to change the datatype with an sql update/convert statement. I want to make the change permanent. As I need it to reflect on a couple of different databases with this same table name. Here is the update statement I have been trying.

update ibapoorderline set salestype=convert(varchar(50),salestype)

There is currently no data stored in the column salestype. They are all null. I just want to change the datatype so I can begin adding data.

Thanks in advance.Drop the column and re-add it.

create table f00 (f00id int, f00monex money)
go
--whoops!
alter table f00 drop f00monex
go
alter table f00 add f00varchar varchar(50) NULL
go

Convert will only convert the data for output, it won't change the datatype of the column.|||Thank you so much. What I was looking for was the drop line. That helps a lot. I couldn't remember how to get rid of a column and then add it back in with a different datatype.|||No problem. Note that when you drop/add it moves the column to the bottom of the column list. Not sure if you care about that...

Look up ALTER TABLE in Books Online for more syntax.

convert money datatype

Hello!

I need to convert money datatype into string without decimal points.

For example, I have column pmt_amt (money datatype)=27.00.

Is there a way to convert this amount into format "2700" without decimals?

Thanks,

Lena

Can you do that in the presentation layer?

declare @.m money

set @.m = 27

select @.m, str(@.m * 10000, 15, 0)

set @.m = 27.0015

select @.m, str(@.m * 10000, 15, 0)

AMB

|||

Thank you!

I tried your conversion and here's my results:

value in table converted

47.02 470200 1202.78 12027800 411.91 4119100 20401.78 204017800 711.01 7110100 517.83 5178300 11756 117560000 773 7730000 3439.75 34397500 4062.66 40626600

How can I get rid of zeroes in the end of converted values?

E. G.: for 40626600 I need it t be 406266.

|||

I would 'refine' AMB's suggestion with the following:

If you want the results to have ONLY 2 numbers to the right of the decimal, then use 100 instead of 10000 in the str() function. That is because the money datatype has four (4) numbers to the right of the decimal, and EVEN though you often ignore them, they are there.

(It will still round up/down as needed.)

And if you have really large numbers, the '15' in the string function may need to be increased.

|||

Money data type has 4 decimal digits. If you want to consider just the first two, then multiply the value by 100 instead 10000.

declare @.m money

set @.m = 4062.66

select @.m, str(@.m * 100, 15, 0)

AMB

|||

Thank you for all your replies.

It helped a lot.

Convert Long To DateTime

Hi.

I need to convert a vb function into a user-defined function in a stored procedure.
The vb function converts a long datatype into Date datatype.

Here is the VB function:

Function LongToTime(lTime As Long) As Date

LongToTime = (lTime \ 10000) / 24 + ((lTime Mod 10000) \ 100) / 1440 + (lTime Mod 100) / 86400

End Function

The function is used to convert a timestamp(hhmmss) into a more readable format.
Would it be possible to create a function similar to this in an SQL stored procedure?

Thanks in advance
Wesley

translation of your vb function should be

CREATE FUNCTION fn_LongToTime (@.lTime int)

RETURNS smalldatetime

AS

BEGIN

DECLARE @.dt smalldatetime

Select @.dt = convert(smalldatetime, floor(@.ts / 10000) / 24 + Floor((@.ts % 10000) / 100) / 1440) + (@.ts % 100) / 86400)

RETURN @.Kg

END

GO

You have to verify smalldatetime conversion is right...

Convert Long to Datetime

Hi.

I need to convert a vb function into a user-defined function in a stored procedure.
The vb function converts a long datatype into Date datatype.

Here is the VB function:

Function LongToTime(lTime As Long) As Date
LongToTime = (lTime \ 10000) / 24 + ((lTime Mod 10000) \ 100) / 1440 + (lTime Mod 100) / 86400
End Function

The function is used to convert a timestamp(hhmmss) into a more readable format.
Would it be possible to create a function similar to this in SQL?

Thanks
Wes

hi

there is no mod function in sql server

you have to use %

i think you can go ahead from here

regards

% (Modulo)

Provides the remainder of one number divided by another.

Syntax

dividend % divisor

Arguments

dividend

Is the numeric expression to divide. dividend must be any valid Microsoft? SQL Server? expression of the integer data type category. (A modulo is the integer that remains after two integers are divided.)

divisor

Is the numeric expression to divide the dividend by. divisor must be any valid SQL Server expression of any of the data types of the integer data type category.

Result Types

int

Remarks

The modulo arithmetic operator can be used in the select list of the SELECT statement with any combination of column names, numeric constants, or any valid expression of the integer data type category.

Examples

This example returns the book title number and any modulo (remainder) of dividing the price (converted to an integer value) of each book into the total yearly sales (ytd_sales * price).

USE pubs

GO

SELECT title_id,

CAST((ytd_sales * price) AS int) % CAST(price AS int) AS Modulo

FROM titles

WHERE price IS NOT NULL and type = 'trad_cook'

ORDER BY title_id

GO

|||declare @.lTime as int

select @.lTime = 153459

select convert(datetime, stuff(stuff(convert(varchar(6), @.lTime), 3, 0, ':'), 6, 0, ':'))

result : 1900-01-01 15:34:59.000

Convert image datatype to varchar

I have a table Table1 which has a Col called "Msg" datatype image<binary>. Msg alreay has the plain text or RTF text as a image datatype (binary)
If I execute the following query "Select Msg from Table1 where id =3" then this query is returning the following ASCII/Binary data.
Msg = "0x7B5C727466315C616E73695C616E7369637067313235325C64656666305C6465666C616E673130333
37B5C666F6E7474626C7B5C66305C6673776973735C66707271325C6663686172736574302041726961
6C3B7D7B5C66315C6673776973735C66707271325C666368617273657430204D6963726F736F667420"
Can any body tell me how can I convert the above binary data to plain text from my query?
Thanks for any reply.
There is no Conversion of Image to Varchar but you can convert Image to Varbinary with limitations, the text below is from the BOL( books online).
Automatic data type conversion is not supported for the text andimagedata types. You can explicitly converttextdata to character data, andimage data tobinaryorvarbinary, but the maximum length is 8000. If you attempt an incorrect conversion (for example, if you convert a character expression that includes letters to anint), SQLServer generates an error message.
Hope this helps.

Convert Image Datatype To Int

Hi,
I have a table which has a field defined as Image. I guess it was defined
that way to hold any value or type.
From what I am reading it seems that I can cast from image to binary and
then from binary to varchar or int...
Whenever I try to convert I keep getting zero as a value. I set up the test
so that I should get an integer value of 5050, but it just does not seem to
work. Is there something wrong with my code:
declare @.c varchar(100)
declare @.trimmed_c varchar(100)
declare @.i int
select @.c = cast(cast(s.VariableValue as varbinary) as varchar)
from SWVariables s where s.VariableName = 'deal_id' and s.ExecutionID = 5
select @.trimmed_c = substring(@.c, 1, CHARINDEX(char(0), @.c, 1) - 1)
select @.i = cast(@.trimmed_c as int)
select @.i as 'deal_id'
Any help would be great, thanks.Depending on how the data was inserted into the image column. Here is an
example that shows successful conversion both ways.
create table tmp(img image default '0x0')
insert tmp values(default)
go
declare @.i int, @.b varbinary(4), @.ptr binary(16)
set @.i=123
set @.b=convert(binary(4),@.i)
select @.ptr=textptr(img)
from tmp
writetext tmp.img @.ptr @.b
go
select img,convert(int,convert(binary(4),img)) [i]
from tmp
go
drop table tmp
-oj
"Dianna" <Dianna@.discussions.microsoft.com> wrote in message
news:F9DA6D91-5256-495A-A7AB-700F955483BD@.microsoft.com...
> Hi,
> I have a table which has a field defined as Image. I guess it was defined
> that way to hold any value or type.
> From what I am reading it seems that I can cast from image to binary and
> then from binary to varchar or int...
> Whenever I try to convert I keep getting zero as a value. I set up the
> test
> so that I should get an integer value of 5050, but it just does not seem
> to
> work. Is there something wrong with my code:
> declare @.c varchar(100)
> declare @.trimmed_c varchar(100)
> declare @.i int
> select @.c = cast(cast(s.VariableValue as varbinary) as varchar)
> from SWVariables s where s.VariableName = 'deal_id' and s.ExecutionID = 5
> select @.trimmed_c = substring(@.c, 1, CHARINDEX(char(0), @.c, 1) - 1)
> select @.i = cast(@.trimmed_c as int)
> select @.i as 'deal_id'
> Any help would be great, thanks.
>

convert hexadecimal datetime to normal datetime

Hi,
I have a field of timestamp datatype. The data is hexadecimal.
I would like to create a function or query the field so that I can see it
as normal 00:00:00 format?
It would also be nice to be able to query the field by entering a 00:00:00
value but it searches the field in the hexadecimal format and then returns
the results again in the 00:00:00 format.
thanksChris wrote:
> Hi,
> I have a field of timestamp datatype. The data is hexadecimal.
> I would like to create a function or query the field so that I can see it
> as normal 00:00:00 format?
> It would also be nice to be able to query the field by entering a 00:00:00
> value but it searches the field in the hexadecimal format and then returns
> the results again in the 00:00:00 format.
> thanks
Use the undocumented extended sproc xp_varbintohexstr
CREATE FUNCTION dbo.TStoString
(
@.ts binary(8)
)
RETURNS varchar(20)
AS
BEGIN
declare @.s varchar(20)
EXEC master.dbo.xp_varbintohexstr @.ts, @.s out
RETURN @.s
END|||Hi Chris
What do you mean by the normal 00:00:00 format?
A timestamp value has absolutely nothing to do with time. It is an internal
counter. Timestamps are also not meant to be queried. SQL Server compares
them internally to determine if a row has been updated.
If you want a datetime column for your own querying, you can add one to the
table.
HTH
Kalen Delaney, SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:8E6CBAB6-246F-445B-B56E-60B71C494053@.microsoft.com...
> Hi,
> I have a field of timestamp datatype. The data is hexadecimal.
> I would like to create a function or query the field so that I can see it
> as normal 00:00:00 format?
> It would also be nice to be able to query the field by entering a 00:00:00
> value but it searches the field in the hexadecimal format and then returns
> the results again in the 00:00:00 format.
> thanks|||Chris,
I think your by the name of the data type. SQL Server has datetime
and timestamp data types, but the second one has nothing to do with datetime
.
Example:
use northwind
go
create table dbo.t1 (
c1 int not null identity(1, 1) unique,
c2 datetime,
c3 timestamp
)
insert into dbo.t1 default values
insert into dbo.t1 default values
select * from t1
update
t1
set
c2 = getdate()
where
c1 = 1
select * from dbo.t1
drop table dbo.t1
go
See datetime and timestamp in BOL for more info.
AMB
"Chris" wrote:

> Hi,
> I have a field of timestamp datatype. The data is hexadecimal.
> I would like to create a function or query the field so that I can see it
> as normal 00:00:00 format?
> It would also be nice to be able to query the field by entering a 00:00:00
> value but it searches the field in the hexadecimal format and then returns
> the results again in the 00:00:00 format.
> thanks|||Thanks Kalen,
I learned in the books online that is useless to me as an actual way to
determine at what time a record was updated.
"Kalen Delaney" wrote:

> Hi Chris
> What do you mean by the normal 00:00:00 format?
> A timestamp value has absolutely nothing to do with time. It is an interna
l
> counter. Timestamps are also not meant to be queried. SQL Server compares
> them internally to determine if a row has been updated.
> If you want a datetime column for your own querying, you can add one to th
e
> table.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:8E6CBAB6-246F-445B-B56E-60B71C494053@.microsoft.com...
>
>

convert datatype from db2 to sql

i am running a package which extracts result of count(x), count(y),sum(z) from db2(as400) servers

i want to store the result of all the three in a table to a column of datatype varchar(25).

can someone help me with the convert/cast function in db2 which i can include in the query.

something like : select cast(count(x) as varchar(25)) from lib.file

No, but we can help you use SSIS to convert them...

Have you searched IBM's site for a SQL reference document containing commands?|||

yaa Phil,even i thought it is better to use a data converter transformation.

the problem iam running into is iam trying to store int and decimal datatypes into the same output column.

which is a varchar.

the convertor works fine for int datatype but is giving error for decimal.

"datatype of output column 'colname' doesnot mach with the datatype 'system.decimal' of the source column 'columnname' "

"component DataReaderSource failed validation and returned validation status VS_NEEDSNEWMETADATA"

what i understand is it not problem with the conver transformation its problem with the data source reader.

let me be more clear i have 6 querys running agains AS400 in a loop 1,2,4,5 querys give output in int and querys 3,6 give output numeric.

when i put each of this querys(1,2,4,5) in datasource reader the output datasource reader pics DT_I4 as the result datatype and for querys 3,6 it pics DT_NUMERIC

i think i am left with option to change in the query itself

help needed.... thanks in advance

Convert Data Tpye when importing to MS SQL

I'm trying to import a set of database (.DBF) to the MS SQL Server 2005. But I found the Datatype for the Time column is in char format. Is it possible to convert it to Time format when importing to MS SQL?

Also, if there is a way to convert to TIME formant, Can I combine it with the Date colume?

Not sure how you're doing the import, if you're using SSIS to import the data then there are ways to change the datatype on the fly during the ETL process (derived columns, expression language, Cast(SSIS), etc).

If you're using T/SQL to import the data then look at the CAST and CONVERT functions.

You could have something like CAST((date + ' ' + time) as datetime) if you want to combine.

Convert Data Tpye when importing to MS SQL

I'm trying to import a set of database (.DBF) to the MS SQL Server 2005. But I found the Datatype for the Time column is in char format. Is it possible to convert it to Time format when importing to MS SQL?

Also, if there is a way to convert to TIME formant, Can I combine it with the Date colume?

Not sure how you're doing the import, if you're using SSIS to import the data then there are ways to change the datatype on the fly during the ETL process (derived columns, expression language, Cast(SSIS), etc).

If you're using T/SQL to import the data then look at the CAST and CONVERT functions.

You could have something like CAST((date + ' ' + time) as datetime) if you want to combine.

convert data to currency

I have a column - datatype 'money' and my price variable is (for instance)
8450
how do I put this value into the money column without getting this error?

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Disallowed implicit conversion from data type varchar to data type money, table 'db196009544.dbo196009544.vehicles', column 'price'. Use the CONVERT function to run this query.

ASP VBScript
I am using this syntax:

price = Upload.Form("price") '--this gives 8450--

SQL="UPDATE vehicles SET price='" & price & "' WHERE id=" & request.queryString("linkID") & ""

Thanks
markDo you define the variable as a daatype...sounds like it's defined as char if you are|||not sure!
I have tried using cCur() and various others when estting the variables but to no avail.

I suspect it has to be done in the SQL string, but can't make that work either.|||I mean it lloks like vb, and I'd ratyher keep my hands clean, buit can you do?

price = Upload.Form(price)

Also, you'd be way better off using stored procedures|||removing the single quotes from the price variable apears to have solved that but now the datetime variable is playing up in the same fashion, so I can't be sure if all is well with the price.
see my next post!