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 data like a case statement

Hello.

I have data in a SSIS package that I need to alter to something else.

The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".

The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.

I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.

Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?

Thanks

No, you need a derived column. Syntax is:

[ColumnName]=="ACT" ? 1 : 0

-Jamie

|||What Jamie said, but with quotes to be a little more meaningful if you're plugging them into a CHAR field.

[ColumnName] == "ACT" ? "1" : "0"

If, when you setup the derived column, you replace the CHAR field, it'll automatically set the type for you and ensure that you have no type errors.|||

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

|||

also how do I put this function in the derived column transformation editor?

"convert(char(10), dateadd(Year, 1, convert(datetime, A.txtdos)), 101)"

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

Use nested conditional operators.

-Jamie

|||

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,

You need some parantheses.

boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result : false_result)

)

)

-Jamie

|||

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

Correct, you have to write it on one line. I spread it over multiple lines to make it easier for you to read.

-Jamie

sqlsql

converting data like a case statement

Hello.

I have data in a SSIS package that I need to alter to something else.

The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".

The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.

I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.

Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?

Thanks

No, you need a derived column. Syntax is:

[ColumnName]=="ACT" ? 1 : 0

-Jamie

|||What Jamie said, but with quotes to be a little more meaningful if you're plugging them into a CHAR field.

[ColumnName] == "ACT" ? "1" : "0"

If, when you setup the derived column, you replace the CHAR field, it'll automatically set the type for you and ensure that you have no type errors.|||

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

|||

also how do I put this function in the derived column transformation editor?

"convert(char(10), dateadd(Year, 1, convert(datetime, A.txtdos)), 101)"

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Phil Brammer,

what can be done for cascade case statement?

thanks,

Use nested conditional operators.

-Jamie

|||

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

how to use that. i tried with

bool condition ? true : bool condition ? true:........................

but it seems too length and also given error while executing and not at design time

thanks,

You need some parantheses.

boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result :

(boolean_expression ? true_result : false_result)

)

)

-Jamie

|||

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

|||

Jwalant Natvarlal Soneji wrote:

Dear Jamie Thomson,

dont u think so the space given in derived column edior is not enought to write the whole query like this, or any other option available to write the same?

thanks,

Correct, you have to write it on one line. I spread it over multiple lines to make it easier for you to read.

-Jamie

Converting calculated field to real

I have a view with 3 integer fields: FailureCount, QuestionCount and
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall ArnoldOn Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>>I have a view with 3 integer fields: FailureCount, QuestionCount and
>>AuditCount. I want to get the following calculated result in a SELECT
>>statement:
>>SELECT FailureCount / (QuestionCount * AuditCount)
>>Problem is, this returns an integer result (as one would expect from 3
>>integers). I need the real number value, and CONVERT isn't working. I've
>>tried various syntaxes and I either get errors or no effect at all.
>>Any ideas how to properly do this?
>>Thanks,
>>Randall Arnold
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql

Converting Access 2000 Query - IIf statement to SQL Server 2000 Vi

I have a field in the query where I count the tech_area field. I have an Iif
(Count Of([tech_field])=0, CountOf([id]),CountOf[tech_field]).
My question is how do I translate that in SQL Server? I tried to write the
query using the new view window but I kept erroring when I put the IIf
statement in.
Any help would be greatly appreciated.
Thx,
CLM
You can use a Case expression instead. There is no IIF in
T-SQL or ANSI SQL. You can find more information and some
examples of using Case in SQL Server books online.
-Sue
On Mon, 21 Mar 2005 14:27:02 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:

>I have a field in the query where I count the tech_area field. I have an Iif
>(Count Of([tech_field])=0, CountOf([id]),CountOf[tech_field]).
>My question is how do I translate that in SQL Server? I tried to write the
>query using the new view window but I kept erroring when I put the IIf
>statement in.
>Any help would be greatly appreciated.
>Thx,
>CLM

Converting Access 2000 Query - IIf statement to SQL Server 2000 Vi

I have a field in the query where I count the tech_area field. I have an Ii
f
(Count Of([tech_field])=0, CountOf([id]),CountOf[tech_field]).
My question is how do I translate that in SQL Server? I tried to write the
query using the new view window but I kept erroring when I put the IIf
statement in.
Any help would be greatly appreciated.
Thx,
CLMYou can use a Case expression instead. There is no IIF in
T-SQL or ANSI SQL. You can find more information and some
examples of using Case in SQL Server books online.
-Sue
On Mon, 21 Mar 2005 14:27:02 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:

>I have a field in the query where I count the tech_area field. I have an I
if
>(Count Of([tech_field])=0, CountOf([id]),CountOf[tech_field]).
>My question is how do I translate that in SQL Server? I tried to write the
>query using the new view window but I kept erroring when I put the IIf
>statement in.
>Any help would be greatly appreciated.
>Thx,
>CLMsqlsql

Converting a value

I have a select statement like this:

select IsVerified from AppForm

the IsVerified returns 'True' ...can I convert that value to 'Yes' by using some sort of function?

SELECT IsVerified=CASE WHEN True THEN 'Yes' ELSE 'No' END from AppForm|||excellent, thanks!

Converting a smallint to an nvarchar

For a SQL statement in an Alias column I am am combing several
columns.
But I am having problems with one column as it is a smallint.

I get this error
Syntax error converting the nvarchar value to a column of data type
smallint

My Sql statement "
Select Stilngcol1 + stringcol2 + intcol1 + stringcol3 As NewColName
from Table1

I was wondering is there anyway to format/convert the smallint to
nvarchar, without changing the database.On 29 Sep 2004 23:06:12 -0700, ree32 wrote:

>I was wondering is there anyway to format/convert the smallint to
>nvarchar, without changing the database.

Hi ree32,

Use CAST(intcol1 AS varchar(3)).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>
> Use CAST(intcol1 AS varchar(3)).
> Best, Hugo

Thanks

I found another way of getting around this
convert(char,intcol1 )

Where would you place your CAST(intcol1 AS varchar(3)) in the context
of the SQL statement|||On 30 Sep 2004 15:31:50 -0700, ree32 wrote:

>I found another way of getting around this
>convert(char,intcol1 )

Hi ree32,

CONVERT and CAST are pretty much equivalent. Except that CONVERT allows
for more control when you're converting date and time data or fractions
and CAST is ANSI standard (ie more portable to other databases).

I use CAST, except when I need the added control CONVERT gives me.

>Where would you place your CAST(intcol1 AS varchar(3)) in the context
>of the SQL statement

It should replace "intcol1".

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql

Convert varchar to decimal

Hello, I am running a SELECT statement on a varchar 50 field. I would like
to convert the output to a numeric field with 2 decimal places. I tried a
CAST but that didn't work. Any suggestions would be appreciated. Thanks,
Pancho."Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work. Any suggestions would be appreciated. Thanks,
> Pancho.
Not knowing the largest decimal value you might wish to display, you could
do something like this:
SELECT CONVERT(<varchar field>, dec(10,2)) AS FieldName
FROM TableName
This will allow a total of 10 digits with 2 on the right of the decimal
point.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Pancho,
What didn't work?
Did you receive an error message?
If so, what?
If not, show us your code.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work. Any suggestions would be appreciated. Thanks,
> Pancho.|||Rick,
I tried CONVERT(FieldValue7, (dec(10,2)) AS NewFieldName
and got:
'dec' is not a recognized function name
Got the same error using 'decimal'. I am running a successful
SELECT CONVERT (CHAR (8), Field8, 112) AS Field8Text but the dec didn't work
.
"Rick Sawtell" wrote:

> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
>
> Not knowing the largest decimal value you might wish to display, you could
> do something like this:
> SELECT CONVERT(<varchar field>, dec(10,2)) AS FieldName
> FROM TableName
>
> This will allow a total of 10 digits with 2 on the right of the decimal
> point.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Hi Raymond,
I got the msg "Unable to convert varchar field" when using CAST. I am
taking data from a varchar and trying to format it to a numeric field, 10
wide with 2 decimal spaces. Pls see code above in my reply to Rick. Thanks
,
Pancho.
"Raymond D'Anjou" wrote:

> Hi Pancho,
> What didn't work?
> Did you receive an error message?
> If so, what?
> If not, show us your code.
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
>
>|||> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work.
What does "didn't work" mean? What did you try? Are you sure all of the
values are really numeric and can be converted? If so, why are you storing
them in a VARCHAR(50) column?|||> I tried CONVERT(FieldValue7, (dec(10,2)) AS NewFieldName
> and got:
> 'dec' is not a recognized function name
Did you try using the right syntax?
NewFieldName = CONVERT(DECIMAL(10,2), FieldValue7)|||> I got the msg "Unable to convert varchar field" when using CAST.
Well, because you decided to use a VARCHAR(50) to store decimals, invariably
you will get varchar data that is not a decimal.
You shouldn't be really surprised by this.
A start would be to filter out the rows where ISNUMERIC(column_name) = 0.
However, ISNUMERIC() is not perfect either, see http://www.aspfaq.com/2390
Then, try cleaning up your data and fixing the data type. When you do that,
you won't need to do a convert at all.|||You got me. I didn't design this DB; a vendor did. I would not have stored
a field which has cash values as a varchar either. I'm trying to convert it
from one vendor to another.
Thanks everyone for your comments. I'm closing this one now.
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "didn't work" mean? What did you try? Are you sure all of the
> values are really numeric and can be converted? If so, why are you storin
g
> them in a VARCHAR(50) column?
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OfuNFSyXGHA.4388@.TK2MSFTNGP03.phx.gbl...
> Did you try using the right syntax?
> NewFieldName = CONVERT(DECIMAL(10,2), FieldValue7)
>
Ooops.. Got it backwards. Thanks Aaron.
Rick

Convert to lower

hi

I have a column in my database i would like to convert to lowercase

is their a t-sql statement or something i can use so i dont have to do it manually ??

cheers!!!

Hi,

you can use theLOWER function in T-SQL.

Grz, Kris.

|||

hotsheep:

hi

I have a column in my database i would like to convert to lowercase

is their a t-sql statement or something i can use so i dont have to do it manually ??

cheers!!!

update table_name set column_name=lower(column_name) .. it will convert all ur records in column_name fields ............... to lower case

hope it will help u ...

Convert to DateTime

I have a date filed 12/26/2006 and a time field 7:00am. How can I combine them in my select statement and get a DateTime field.

declare @.v1 varchar(20)
select @.v1 ='12/26/2006'

declare @.v2 varchar(20)
select @.v2 ='7:00am'

select convert(datetime,@.v1 + ' ' + @.v2)

but is safer to use the YYYYMMDD format for dates

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Convert to DateTime

I have a date filed 12/26/2006 and a time field 7:00am. How can I combine them in my select statement and get a DateTime field.

Using SQL Server?

SELECT CONVERT(VARCHAR(10), MyDateField) + Convert(VARCHAR(12), MyTimeField) As MyDateTimeField
FROM MyTable

|||

I forgot to add a space between the date and time:

SELECT CONVERT(VARCHAR(10), MyDateField) + ' ' + CONVERT(VARCHAR(12), MyTimeField) As MyDateTimeField
FROM MyTable

|||

What are exact definitions of those fields in database?

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 text to float in SQL Statement

Hi,

Can you guys help me out?
I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast".

I do get the answer but its not the correct figure. My SQL statement is as follow:

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName

Please help.Try avoiding using flaot, pretty inaccurate.

Use decimal or numeric data types instead.|||Originally posted by Crespo-n00b
Try avoiding using flaot, pretty inaccurate.

Use decimal or numeric data types instead.

I've tried it and it still gives me a wrong answer.
I should have 45299 + 7832.5 = 53131.5, but I kept getting
13310.

Any more ideas?|||A Miracle ??

use pubs

create table answerint
(
QTY1 varchar(20),
QTY2 varchar(20)
)

insert into answerint select '45299','7832.5'

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM answerint

drop table answerint

Works for me though !!!|||Crespo and a mriacle in the same post...holy cow!

Where have you been hiding out?

And Yes float is quirky...

But I would add

USE Northwind

CREATE TABLE answerint
(
QTY1 varchar(20),
QTY2 varchar(20)
)

INSERT INTO answerint
SELECT '45299','7832.5' UNION ALL
SELECT 'BRETT', 'KAISER'

SELECT SUM(
(Cast(Qty1 as float))
+ (Cast(Qty2 as float))
) as intAnswer FROM answerint
WHERE ISNUMERIC(Qty1) = 1 AND ISNUMERIC(Qty2) = 1

DROP TABLE answerint|||Brett,

I have not been hiding anywhere. Just busy with work and life you know...

Amethystium.|||Originally posted by Crespo-n00b
Brett,

I have not been hiding anywhere. Just busy with work and life you know...

Amethystium.

Thanks for the replies.
I've tried it all out and they work for single select but not when a
"SUM" is used.

But I've found out that the answer came out the way it was because there were some NULL values in QTY2. When this happened, the result would be NULL even if QTY1 contained a figure.

Anymore ideas? I'm planning to manually grab these out evaluate/convert them using ASP before doing a calculation.

Thanks

Convert String into unicode

Hello everybody,
I want to convert string into unicode when i using the select statement, how
can i do ?
Thanks in advance .
^^
The most simple is:
SELECT CAST('abc' AS NVARCHAR)
As I am not sure of the context of your issue (eg, using .NET, ODBC,
etc) much of the desired result depends on how you access your DB.

Convert String into unicode

Hello everybody,
I want to convert string into unicode when i using the select statement, how
can i do '
Thanks in advance .
^^The most simple is:
SELECT CAST('abc' AS NVARCHAR)
As I am not sure of the context of your issue (eg, using .NET, ODBC,
etc) much of the desired result depends on how you access your DB.

Convert String into unicode

Hello everybody,
I want to convert string into unicode when i using the select statement, how
can i do '
Thanks in advance .
^^The most simple is:
SELECT CAST('abc' AS NVARCHAR)
As I am not sure of the context of your issue (eg, using .NET, ODBC,
etc) much of the desired result depends on how you access your DB.

Convert string into sql date time

i have a sql statement that i created in code and it is sending a query to the database
when i dim the variable a datetime variable it says that it cant convert it
if i make the variable a varchar it works but it only returns one result when it should be returning about 10

here is the code

 Public Function dbDGQSSearch(ByVal BatchID As String, ByVal CreatedBy As Integer, ByVal CreatedFor As Integer, ByVal DateCreatedMod As Integer, ByVal DateCreated As String, ByVal DateCompletedMod As Integer, ByVal DateCompleted As String, ByVal DateStartedMod As Integer, ByVal DateStarted As String, ByVal SearchType As Integer, ByVal Completed As Integer, ByVal PriorityMod As Integer, ByVal Priority As Integer, ByVal RemainingCallsMod As Integer, ByVal RemainingCalls As Integer, ByVal TotalCallsMod As Integer, ByVal TotalCalls As Integer, ByVal Bonus As Integer, ByVal Keyword1 As String, ByVal Keyword2 As String, ByVal Keyword3 As String, ByVal Keyword4 As String, ByVal Keyword5 As String)
 Dim strQueSearch As String
 strQueSearch = "SELECT tlkup_Rep.RepID, tlkup_Rep.PositionID, tlkup_Rep.RepFName, tlkup_Rep.RepLName, tlkup_Rep.RepPassword, tlkup_Rep.RepUserName, tlkup_Rep.RepFName + ' ' + tlkup_Rep.RepLName AS RepName, t_Que.QueID, t_Que.BatchID, t_Que.AdminID, t_Que.Manager, t_Que.BonusID, t_Que.QueCompleted, t_Que.QueDate, t_Que.QueNotes, t_Que.QuePriority, t_Que.QueQuantity, t_Que.QueStartDate, t_Que.Mail, t_Que.QueDateComplete, t_Que.QueTotal FROM t_Que INNER JOIN tlkup_Rep ON t_Que.Manager = tlkup_Rep.RepID AND t_Que.Manager = tlkup_Rep.RepID WHERE BatchID<>'' and BatchID<>'2' and BatchID<>'3' and BatchID<>'4' "
 'Creates statement for selecting the add to batch data where the criteria appear
 If BatchID <> "" Then
 strQueSearch = strQueSearch + " and t_Que.BatchID= @.BatchID "
 End If
 If CreatedBy > 1 Then
 strQueSearch = strQueSearch + " and t_Que.RepID =@.RepID "
 End If
 If CreatedFor > 1 Then
 strQueSearch = strQueSearch + " and t_Que.Manager = @.Manager "
 End If
 If DateCreated <> "" Then
 If DateCreatedMod = 0 Then
 '>
 strQueSearch = strQueSearch + " and t_Que.QueDate >@.QueDate "
 ElseIf DateCreatedMod = 1 Then
 '<
 strQueSearch = strQueSearch + " and t_Que.QueDate <@.QueDate "
 ElseIf DateCreatedMod = 2 Then
 '=
 strQueSearch = strQueSearch + " and t_Que.QueDate =@.QueDate "
 End If
 End If
 If DateCompleted <> "" Then
 If DateCompletedMod = 0 Then
 '>
 strQueSearch = strQueSearch + " and t_Que.QueDateComplete >@.QueDateComplete "
 ElseIf DateCompletedMod = 1 Then
 '<
 strQueSearch = strQueSearch + " and t_Que.QueDateComplete <@.QueDateComplete and t_Que.QueDateComplete >'1/1/1900' "
 ElseIf DateCompletedMod = 2 Then
 '=
 strQueSearch = strQueSearch + " and t_Que.QueDateComplete =@.QueDateComplete "
 End If
 End If
 If DateStarted <> "" Then
 If DateStartedMod = 0 Then
 '>
 strQueSearch = strQueSearch + " and t_Que.QueStartDate >@.QueStartDate "
 ElseIf DateStartedMod = 1 Then
 '<
 strQueSearch = strQueSearch + " and t_Que.QueStartDate <@.QueStartDate "
 ElseIf DateStartedMod = 2 Then
 '=
 strQueSearch = strQueSearch + " and t_Que.QueStartDate =@.QueStartDate "
 End If
 End If
 If SearchType = 0 Then
 'Both
 'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 "
 ElseIf SearchType = 1 Then
 'Mail
 strQueSearch = strQueSearch + " and t_Que.Mail=1 "
 ElseIf SearchType = 2 Then
 'Phone
 strQueSearch = strQueSearch + " and t_Que.Mail=0 "
 End If
 If Completed = 0 Then
 'Both
 'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 "
 ElseIf Completed = 1 Then
 'Yes
 strQueSearch = strQueSearch + " and t_Que.QueCompleted=1 "
 ElseIf Completed = 2 Then
 'No
 strQueSearch = strQueSearch + " and t_Que.QueCompleted=0 "
 End If
 If Priority > 0 Then
 If PriorityMod = 0 Then
 '>
 strQueSearch = strQueSearch + " and t_Que.QuePriority >@.QuePriority "
 ElseIf PriorityMod = 1 Then
 '<
 strQueSearch = strQueSearch + " and t_Que.QuePriority <@.QuePriority "
 ElseIf PriorityMod = 2 Then
 '=
 strQueSearch = strQueSearch + " and t_Que.QuePriority =@.QuePriority "
 End If
 End If
 If RemainingCalls > 0 Then
 If RemainingCallsMod = 0 Then
 '>
 strQueSearch = strQueSearch + " and t_Que.QueQuantity >@.QueQuantity "
 ElseIf RemainingCallsMod = 1 Then
 '<
 strQueSearch = strQueSearch + " and t_Que.QueQuantity <@.QueQuantity "
 ElseIf RemainingCallsMod = 2 Then
 '=
 strQueSearch = strQueSearch + " and t_Que.QueQuantity =@.QueQuantity "
 End If
 End If
 If TotalCalls > 0 Then
 If TotalCallsMod = 0 Then
 '>
 strQueSearch = strQueSearch + " and t_Que.QueTotal >@.QueTotal "
 ElseIf TotalCallsMod = 1 Then
 '<
 strQueSearch = strQueSearch + " and t_Que.QueTotal <@.QueTotal "
 ElseIf TotalCallsMod = 2 Then
 '=
 strQueSearch = strQueSearch + " and t_Que.QueTotal =@.QueTotal "
 End If
 End If
 If Bonus > 1 Then
 strQueSearch = strQueSearch + " and t_Que.BonusID =@.BonusID "
 End If
 If Keyword1 <> "" Then
 strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@.Keyword1+'%' "
 End If
 If Keyword2 <> "" Then
 strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@.Keyword2+'%' "
 End If
 If Keyword3 <> "" Then
 strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@.Keyword3+'%' "
 End If
 If Keyword4 <> "" Then
 strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@.Keyword4+'%' "
 End If
 If Keyword5 <> "" Then
 strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@.Keyword5+'%' "
 End If
 'makes statement into sqlcommand
 C.daQueSearch.SelectCommand.CommandText = strQueSearch
 'var declaration
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.BatchID", SqlDbType.VarChar, 12))
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.Manager", SqlDbType.Int))
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.RepID", SqlDbType.Int))
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.QueDate", SqlDbType.VarChar, 20)) '<-- This is what,when i change to datetime, says it cant convert
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.QueStartDate", SqlDbType.VarChar, 20))
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.QueDateComplete", SqlDbType.VarChar, 20))
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.QuePriority", SqlDbType.Int))
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.QueQuantity", SqlDbType.BigInt))
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.QueTotal", SqlDbType.BigInt))
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.BonusID", SqlDbType.SmallInt))
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.Keyword1", SqlDbType.VarChar, 50))
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.Keyword2", SqlDbType.VarChar, 50))
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.Keyword3", SqlDbType.VarChar, 50))
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.Keyword4", SqlDbType.VarChar, 50))
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.Keyword5", SqlDbType.VarChar, 50))
 'data entry
 C.daQueSearch.SelectCommand.Parameters("@.BatchID").Value = BatchID
 C.daQueSearch.SelectCommand.Parameters("@.Manager").Value = CreatedBy
 C.daQueSearch.SelectCommand.Parameters("@.RepID").Value = CreatedFor
 C.daQueSearch.SelectCommand.Parameters("@.QueDate").Value = DateCreated
 C.daQueSearch.SelectCommand.Parameters("@.QueStartDate").Value = DateStarted
 C.daQueSearch.SelectCommand.Parameters("@.QueDateComplete").Value = DateCompleted
 C.daQueSearch.SelectCommand.Parameters("@.QuePriority").Value = Priority
 C.daQueSearch.SelectCommand.Parameters("@.QueQuantity").Value = RemainingCalls
 C.daQueSearch.SelectCommand.Parameters("@.QueTotal").Value = TotalCalls
 C.daQueSearch.SelectCommand.Parameters("@.BonusID").Value = Bonus
 C.daQueSearch.SelectCommand.Parameters("@.Keyword1").Value = Keyword1
 C.daQueSearch.SelectCommand.Parameters("@.Keyword2").Value = Keyword2
 C.daQueSearch.SelectCommand.Parameters("@.Keyword3").Value = Keyword3
 C.daQueSearch.SelectCommand.Parameters("@.Keyword4").Value = Keyword4
 C.daQueSearch.SelectCommand.Parameters("@.Keyword5").Value = Keyword5
 Try
 C.ndConnection.Open()
 C.daQueSearch.SelectCommand.ExecuteNonQuery()
 Catch ex As Exception
 lblMainError1.Text = err("dbDGQSSearch " + ex.Source, ex.Message, CurUsr)
 lblMainError1.Visible = True
 Finally
 C.ndConnection.Close()
 End Try
 FillQSDG()' this fills the datagrid
 End Function

does your above code work ? because you are building the search string conditionally but adding the parameters without checking the conditions...

for xample :

If BatchID <> "" Then
 strQueSearch = strQueSearch + " and t_Que.BatchID= @.BatchID "
End If

you are appending to the sql stmt if batchid <> ""...but here

C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.BatchID", SqlDbType.VarChar, 12))

you are adding the parameter to the collection without any checks..

lets say the the user did not supply any id for the batchid...then the sql stmt wil not be appended with "and t_Que.BatchID= @.BatchID " part...but the parameter is still being added ...

do you get my point ?|||i get your point but it seems to work correctly
it is adding to the parameter collection but doesnt actually use it until it is in the statement.
it probably isnt proper but it does work|||just for kicks i changed it and it still did not work but the weird thing is it doesnt work even if there is no criteria entered.

what is weird is i used the cool little red dot program walkthrough thing and i stopped it right on the sql transaction and copied the command.text and pasted it into query analizer and it got the require results
But the data grid that it is outputting to only shows one record

This is the new code

Public Function dbDGQSSearch(ByVal BatchID As String, ByVal CreatedBy As Integer, ByVal CreatedFor As Integer, ByVal DateCreatedMod As Integer, ByVal DateCreated As String, ByVal DateCompletedMod As Integer, ByVal DateCompleted As String, ByVal DateStartedMod As Integer, ByVal DateStarted As String, ByVal SearchType As Integer, ByVal Completed As Integer, ByVal PriorityMod As Integer, ByVal Priority As Integer, ByVal RemainingCallsMod As Integer, ByVal RemainingCalls As Integer, ByVal TotalCallsMod As Integer, ByVal TotalCalls As Integer, ByVal Bonus As Integer, ByVal Keyword1 As String, ByVal Keyword2 As String, ByVal Keyword3 As String, ByVal Keyword4 As String, ByVal Keyword5 As String)
 Dim strQueSearch As String
 strQueSearch = "SELECT tlkup_Rep.RepID, tlkup_Rep.PositionID, tlkup_Rep.RepFName, tlkup_Rep.RepLName, tlkup_Rep.RepPassword, tlkup_Rep.RepUserName, tlkup_Rep.RepFName + ' ' + tlkup_Rep.RepLName AS RepName, t_Que.QueID, t_Que.BatchID, t_Que.AdminID, t_Que.Manager, t_Que.BonusID, t_Que.QueCompleted, t_Que.QueDate, t_Que.QueNotes, t_Que.QuePriority, t_Que.QueQuantity, t_Que.QueStartDate, t_Que.Mail, t_Que.QueDateComplete, t_Que.QueTotal FROM t_Que INNER JOIN tlkup_Rep ON t_Que.Manager = tlkup_Rep.RepID AND t_Que.Manager = tlkup_Rep.RepID WHERE BatchID<>'' and BatchID<>'2' and BatchID<>'3' and BatchID<>'4' "
 'Creates statement for selecting the add to batch data where the criteria appear
 If BatchID <> "" Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.BatchID", SqlDbType.VarChar, 12))
 C.daQueSearch.SelectCommand.Parameters("@.BatchID").Value = BatchID
 strQueSearch = strQueSearch + " and t_Que.BatchID= @.BatchID "
 End If
 If CreatedBy > 1 Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.RepID", SqlDbType.Int))
 C.daQueSearch.SelectCommand.Parameters("@.RepID").Value = CreatedFor
 strQueSearch = strQueSearch + " and t_Que.RepID =@.RepID "
 End If
 If CreatedFor > 1 Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.Manager", SqlDbType.Int))
 C.daQueSearch.SelectCommand.Parameters("@.Manager").Value = CreatedBy
 strQueSearch = strQueSearch + " and t_Que.Manager = @.Manager "
 End If
 If DateCreated <> "" Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.QueDate", SqlDbType.VarChar, 20))
 C.daQueSearch.SelectCommand.Parameters("@.QueDate").Value = DateCreated
 If DateCreatedMod = 0 Then
 '>
 strQueSearch = strQueSearch + " and t_Que.QueDate >@.QueDate "
 ElseIf DateCreatedMod = 1 Then
 '<
 strQueSearch = strQueSearch + " and t_Que.QueDate <@.QueDate "
 ElseIf DateCreatedMod = 2 Then
 '=
 strQueSearch = strQueSearch + " and t_Que.QueDate =@.QueDate "
 End If
 End If
 If DateCompleted <> "" Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.QueDateComplete", SqlDbType.VarChar, 20))
 C.daQueSearch.SelectCommand.Parameters("@.QueDateComplete").Value = DateCompleted
 If DateCompletedMod = 0 Then
 '>
 strQueSearch = strQueSearch + " and t_Que.QueDateComplete >@.QueDateComplete "
 ElseIf DateCompletedMod = 1 Then
 '<
 strQueSearch = strQueSearch + " and t_Que.QueDateComplete <@.QueDateComplete and t_Que.QueDateComplete >'1/1/1900' "
 ElseIf DateCompletedMod = 2 Then
 '=
 strQueSearch = strQueSearch + " and t_Que.QueDateComplete =@.QueDateComplete "
 End If
 End If
 If DateStarted <> "" Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.QueStartDate", SqlDbType.VarChar, 20))
 C.daQueSearch.SelectCommand.Parameters("@.QueStartDate").Value = DateStarted
 If DateStartedMod = 0 Then
 '>
 strQueSearch = strQueSearch + " and t_Que.QueStartDate >@.QueStartDate "
 ElseIf DateStartedMod = 1 Then
 '<
 strQueSearch = strQueSearch + " and t_Que.QueStartDate <@.QueStartDate "
 ElseIf DateStartedMod = 2 Then
 '=
 strQueSearch = strQueSearch + " and t_Que.QueStartDate =@.QueStartDate "
 End If
 End If
 If SearchType = 0 Then
 'Both
 'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 "
 ElseIf SearchType = 1 Then
 'Mail
 strQueSearch = strQueSearch + " and t_Que.Mail=1 "
 ElseIf SearchType = 2 Then
 'Phone
 strQueSearch = strQueSearch + " and t_Que.Mail=0 "
 End If
 If Completed = 0 Then
 'Both
 'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 "
 ElseIf Completed = 1 Then
 'Yes
 strQueSearch = strQueSearch + " and t_Que.QueCompleted=1 "
 ElseIf Completed = 2 Then
 'No
 strQueSearch = strQueSearch + " and t_Que.QueCompleted=0 "
 End If
 If Priority > 0 Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.QuePriority", SqlDbType.Int))
 C.daQueSearch.SelectCommand.Parameters("@.QuePriority").Value = Priority
 If PriorityMod = 0 Then
 '>
 strQueSearch = strQueSearch + " and t_Que.QuePriority >@.QuePriority "
 ElseIf PriorityMod = 1 Then
 '<
 strQueSearch = strQueSearch + " and t_Que.QuePriority <@.QuePriority "
 ElseIf PriorityMod = 2 Then
 '=
 strQueSearch = strQueSearch + " and t_Que.QuePriority =@.QuePriority "
 End If
 End If
 If RemainingCalls > 0 Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.QueQuantity", SqlDbType.BigInt))
 C.daQueSearch.SelectCommand.Parameters("@.QueQuantity").Value = RemainingCalls
 If RemainingCallsMod = 0 Then
 '>
 strQueSearch = strQueSearch + " and t_Que.QueQuantity >@.QueQuantity "
 ElseIf RemainingCallsMod = 1 Then
 '<
 strQueSearch = strQueSearch + " and t_Que.QueQuantity <@.QueQuantity "
 ElseIf RemainingCallsMod = 2 Then
 '=
 strQueSearch = strQueSearch + " and t_Que.QueQuantity =@.QueQuantity "
 End If
 End If
 If TotalCalls > 0 Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.QueTotal", SqlDbType.BigInt))
 C.daQueSearch.SelectCommand.Parameters("@.QueTotal").Value = TotalCalls
 If TotalCallsMod = 0 Then
 '>
 strQueSearch = strQueSearch + " and t_Que.QueTotal >@.QueTotal "
 ElseIf TotalCallsMod = 1 Then
 '<
 strQueSearch = strQueSearch + " and t_Que.QueTotal <@.QueTotal "
 ElseIf TotalCallsMod = 2 Then
 '=
 strQueSearch = strQueSearch + " and t_Que.QueTotal =@.QueTotal "
 End If
 End If
 If Bonus > 1 Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.BonusID", SqlDbType.SmallInt))
 C.daQueSearch.SelectCommand.Parameters("@.BonusID").Value = Bonus
 strQueSearch = strQueSearch + " and t_Que.BonusID =@.BonusID "
 End If
 If Keyword1 <> "" Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.Keyword1", SqlDbType.VarChar, 50))
 C.daQueSearch.SelectCommand.Parameters("@.Keyword1").Value = Keyword1
 strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@.Keyword1+'%' "
 End If
 If Keyword2 <> "" Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.Keyword2", SqlDbType.VarChar, 50))
 C.daQueSearch.SelectCommand.Parameters("@.Keyword2").Value = Keyword2
 strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@.Keyword2+'%' "
 End If
 If Keyword3 <> "" Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.Keyword3", SqlDbType.VarChar, 50))
 C.daQueSearch.SelectCommand.Parameters("@.Keyword3").Value = Keyword3
 strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@.Keyword3+'%' "
 End If
 If Keyword4 <> "" Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.Keyword4", SqlDbType.VarChar, 50))
 C.daQueSearch.SelectCommand.Parameters("@.Keyword4").Value = Keyword4
 strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@.Keyword4+'%' "
 End If
 If Keyword5 <> "" Then
 C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@.Keyword5", SqlDbType.VarChar, 50))
 C.daQueSearch.SelectCommand.Parameters("@.Keyword5").Value = Keyword5
 strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@.Keyword5+'%' "
 End If
 'makes statement into sqlcommand
 C.daQueSearch.SelectCommand.CommandText = strQueSearch
 Try
 C.ndConnection.Open()
 C.daQueSearch.SelectCommand.ExecuteNonQuery()
 Catch ex As Exception
 lblMainError1.Text = err("dbDGQSSearch " + ex.Source, ex.Message, CurUsr)
 lblMainError1.Visible = True
 Finally
 C.ndConnection.Close()
 End Try
 FillQSDG()

|||you are declaring it as a function...whats the return type? what are you returning ?

Public Function dbDGQSSearch(ByVal BatchID As String, ByVal CreatedBy As Integer, ByVal CreatedFor As Integer, ByVal DateCreatedMod As Integer, ByVal DateCreated As String, ByVal DateCompletedMod As Integer, ByVal DateCompleted As String, ByVal DateStartedMod As Integer, ByVal DateStarted As String, ByVal SearchType As Integer, ByVal Completed As Integer, ByVal PriorityMod As Integer, ByVal Priority As Integer, ByVal RemainingCallsMod As Integer, ByVal RemainingCalls As Integer, ByVal TotalCallsMod As Integer, ByVal TotalCalls As Integer, ByVal Bonus As Integer, ByVal Keyword1 As String, ByVal Keyword2 As String, ByVal Keyword3 As String, ByVal Keyword4 As String, ByVal Keyword5 As String)...?

hth|||what is the question?
if it is because it is not returning anything, that is fine, that isnt what is wrong, it doesnt need to return anything.
if it is because it is excruciatingly long i know
but this doesnt help me a whole lot|||i asked those q's because i didnt understand what you are trying to do in the function...you have a select statement but you say executenonquery... which does not return any records..if you need the resultset you need to say execteReader

hth|||i changed it to executeREADER and it is still only returning one result to the datagrid.
and, if as i said before, i take the statement that it is going to the query and put it into the query analizer it will return the correct results|||sorry
being un observant can be very frustrating, i was filling the dataset with the wrong data adapter

Convert string into datetime with timezone

I have tried to execute the following sql statement to convert a string with time zone information to datetime without success:

SELECT CONVERT(datetime, '2007-07-24T14:00:00+07:00', 126);

Is there any advice? I'm SQL Server Express 2005 as follows:

Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft Data Access Components (MDAC) 6.0.6000.16386 (vista_rtm.061101-2205)
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.6000.16473
Microsoft .NET Framework 2.0.50727.1318
Operating System 6.0.6000

Thanks,

Jong

You can only parse it. SQL doesn't recognize that as a legit date format.

You might try this:

Code Snippet

declare @.strDate varchar(255)

set @.strDate = '2007-07-24T14:00:00+07:00'

select convert(datetime,left(@.strDate,len(@.strDate) - patindex('%[+-]%',reverse(@.strDate))))

YOu have to reverse the string before Patindex becuase you need to allow for + or - and of course, there are dashes after the year.

Alternativly, if you can always rely on the exact format:

Code Snippet

declare @.strDate varchar(255)

set @.strDate = '2007-07-24T14:00:00+07:00'

select convert(datetime,left(@.strDate,19))

Neither of these converts the time to GMT...you would have to further parse and then use DateAdd.|||

Here is an illustration of one possible way to handle this issue.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyDate varchar(30)
)

INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00+07:00' )
INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00+11:00' )
INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00-02:00' )
INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00-11:00' )

SELECT
RowID,
LocalTime = dateadd( hour, cast( substring( MyDate, 20, 3 ) AS int ), convert( datetime, left( MyDate, 19 )))
FROM @.MyTable

RowID LocalTime
-- --
1 2007-07-24 21:00:00.000
2 2007-07-25 01:00:00.000
3 2007-07-24 12:00:00.000
4 2007-07-24 03:00:00.000

|||

rusag2 wrote:

You can only parse it. SQL doesn't recognize that as a legit date format.

I thought SQL Server 2005 should recognize it as instructed in http://msdn2.microsoft.com/en-us/library/ms187928.aspx. I'm not sure if there is any difference between SQL Server 2005 and SQL Server Express 2005.

Anyway, thanks for answering.

|||

SQL Server 2005 Xquery includes support for dateTime values with timezone information. So you can use the built-in xquery functions to do the appropriate conversion. You simply pass the datetime string value to one of the xquery methods like value and do the conversion. Below are some examples:

Code Snippet

select cast('' as xml).value('xs:dateTime("2007-07-24T14:00:00+07:00")', 'datetime')

declare @.d varchar(30);
set @.d = '2007-07-24T14:00:00+07:00'
select cast('' as xml).value('xs:dateTime(sql:variable("@.d"))', 'datetime')

select cast('' as xml).value('xs:dateTime(sql:column("t.d"))', 'datetime')

from (select '2007-07-24T14:00:00+07:00') as t(d)