DataAdd narrowing conversion error

I am simply trying to take a date and add the value from another field
to it using the following expression:
=dateadd("d", " & Fields!new_tgddatefrom.Value & ",
Parameters!HolidayDate.Value)
new_tgddatefrom.Value is an int data type
HolidayDate.Value is a date
The expression works fine if I use an acutal number for the value to
add but when I use the field.value I get an error:
Overload resoution failed because no accessible 'DateAdd can be called
without a narrowing conversion.
Why or why is it so difficult to work with dates!?!? If anyone can
help me get this working I would sincerely appreciate it!
DebraTry =dateadd("d", Fields!new_tgddatefrom.Value, Parameters!HolidayDate.Value)
"Debralous" wrote:
> I am simply trying to take a date and add the value from another field
> to it using the following expression:
> =dateadd("d", " & Fields!new_tgddatefrom.Value & ",
> Parameters!HolidayDate.Value)
> new_tgddatefrom.Value is an int data type
> HolidayDate.Value is a date
>
> The expression works fine if I use an acutal number for the value to
> add but when I use the field.value I get an error:
> Overload resoution failed because no accessible 'DateAdd can be called
> without a narrowing conversion.
> Why or why is it so difficult to work with dates!?!? If anyone can
> help me get this working I would sincerely appreciate it!
> Debra
>|||William wrote:
> Try =dateadd("d", Fields!new_tgddatefrom.Value, Parameters!HolidayDate.Value)
>
That was the first thing I tried. While it doesn't error, it doesn't
actually add the number of days in Fields!new_tgddatefrom.Value, it
just shows the date which is in Parameters!HolidayDate.Value.
Any other suggestions?|||This is the proper function call:
= DateAdd(interval, number, date)
Are you sure you have valid datatypes and values for your parameters?
If the function is used in a report with multiple datasets or groupings, do
you need to declare a different scope for your function variables?
"Debralous" wrote:
> William wrote:
> > Try =dateadd("d", Fields!new_tgddatefrom.Value, Parameters!HolidayDate.Value)
> >
> That was the first thing I tried. While it doesn't error, it doesn't
> actually add the number of days in Fields!new_tgddatefrom.Value, it
> just shows the date which is in Parameters!HolidayDate.Value.
> Any other suggestions?
>

data value split

http://www.google.com/accounts/VE?service=groups2&c=CNHcx6iS7qrZ0AEQsYSqltP04cOtAQ&hl=en
I have data like
Return|Item
Cancelled|Item [CFY]
Gifts
I would like to display
if data has | then display it in two text box
like Textbox1 Return
Textbox2 Item
if data does not have | then display the "field value" in
Textbox1 and
Textbox2 should be ""
any help would be nice
thanksThe instr, left, right, and iif functions should get you there.
one piece:
=left(field,instr("|")-1)
Mike G.
<aajay78@.hotmail.com> wrote in message
news:1190911497.216601.202900@.d55g2000hsg.googlegroups.com...
> http://www.google.com/accounts/VE?service=groups2&c=CNHcx6iS7qrZ0AEQsYSqltP04cOtAQ&hl=en
> I have data like
> Return|Item
> Cancelled|Item [CFY]
> Gifts
> I would like to display
> if data has | then display it in two text box
> like Textbox1 Return
> Textbox2 Item
> if data does not have | then display the "field value" in
> Textbox1 and
> Textbox2 should be ""
> any help would be nice
> thanks
>

data type real 4

I have a value in a SQL table of -1.#IND for a data type of real 4. I am
wondering what the value of -1.#IND means.
--
Thanks Jeff DaunIt generally means you hit an issue with values using
approximate data types - there are some values where the
representation of the value gets hosed out. You will see
values along the lines of 1.#NF or 1.#ND for approximate
data types when you hit this in SQL Server.
-Sue
On Tue, 17 Jan 2006 09:26:06 -0800, Jeff Daun
<JeffDaun@.discussions.microsoft.com> wrote:
>I have a value in a SQL table of -1.#IND for a data type of real 4. I am
>wondering what the value of -1.#IND means.|||Thank you
--
Thanks Jeff Daun
"Sue Hoegemeier" wrote:
> It generally means you hit an issue with values using
> approximate data types - there are some values where the
> representation of the value gets hosed out. You will see
> values along the lines of 1.#NF or 1.#ND for approximate
> data types when you hit this in SQL Server.
> -Sue
> On Tue, 17 Jan 2006 09:26:06 -0800, Jeff Daun
> <JeffDaun@.discussions.microsoft.com> wrote:
> >I have a value in a SQL table of -1.#IND for a data type of real 4. I am
> >wondering what the value of -1.#IND means.
>|||I was wondering how would I be able to view the data in the column so that I
can find the value being put in the field from the application so that I can
find the correct precision to use?
--
Thanks Jeff Daun
"Sue Hoegemeier" wrote:
> It generally means you hit an issue with values using
> approximate data types - there are some values where the
> representation of the value gets hosed out. You will see
> values along the lines of 1.#NF or 1.#ND for approximate
> data types when you hit this in SQL Server.
> -Sue
> On Tue, 17 Jan 2006 09:26:06 -0800, Jeff Daun
> <JeffDaun@.discussions.microsoft.com> wrote:
> >I have a value in a SQL table of -1.#IND for a data type of real 4. I am
> >wondering what the value of -1.#IND means.
>|||I don't think so as it has a value that the SQL Server can't
represent. It's not really a matter of the precision as much
as a value getting in there that shouldn't have. If I
remember, the issue is due to SQL Server not fully
implementing the IEEE 754 standard for binary representation
of floating point numbers.
-Sue
On Wed, 18 Jan 2006 07:09:03 -0800, Jeff Daun
<JeffDaun@.discussions.microsoft.com> wrote:
>I was wondering how would I be able to view the data in the column so that I
>can find the value being put in the field from the application so that I can
>find the correct precision to use?

data type real 4

I have a value in a SQL table of -1.#IND for a data type of real 4. I am
wondering what the value of -1.#IND means.
--
Thanks Jeff DaunIt generally means you hit an issue with values using
approximate data types - there are some values where the
representation of the value gets hosed out. You will see
values along the lines of 1.#NF or 1.#ND for approximate
data types when you hit this in SQL Server.
-Sue
On Tue, 17 Jan 2006 09:26:06 -0800, Jeff Daun
<JeffDaun@.discussions.microsoft.com> wrote:

>I have a value in a SQL table of -1.#IND for a data type of real 4. I am
>wondering what the value of -1.#IND means.|||Thank you
--
Thanks Jeff Daun
"Sue Hoegemeier" wrote:

> It generally means you hit an issue with values using
> approximate data types - there are some values where the
> representation of the value gets hosed out. You will see
> values along the lines of 1.#NF or 1.#ND for approximate
> data types when you hit this in SQL Server.
> -Sue
> On Tue, 17 Jan 2006 09:26:06 -0800, Jeff Daun
> <JeffDaun@.discussions.microsoft.com> wrote:
>
>|||I was wondering how would I be able to view the data in the column so that I
can find the value being put in the field from the application so that I can
find the correct precision to use?
--
Thanks Jeff Daun
"Sue Hoegemeier" wrote:

> It generally means you hit an issue with values using
> approximate data types - there are some values where the
> representation of the value gets hosed out. You will see
> values along the lines of 1.#NF or 1.#ND for approximate
> data types when you hit this in SQL Server.
> -Sue
> On Tue, 17 Jan 2006 09:26:06 -0800, Jeff Daun
> <JeffDaun@.discussions.microsoft.com> wrote:
>
>|||I don't think so as it has a value that the SQL Server can't
represent. It's not really a matter of the precision as much
as a value getting in there that shouldn't have. If I
remember, the issue is due to SQL Server not fully
implementing the IEEE 754 standard for binary representation
of floating point numbers.
-Sue
On Wed, 18 Jan 2006 07:09:03 -0800, Jeff Daun
<JeffDaun@.discussions.microsoft.com> wrote:

>I was wondering how would I be able to view the data in the column so that
I
>can find the value being put in the field from the application so that I ca
n
>find the correct precision to use?

data type real 4

I have a value in a SQL table of -1.#IND for a data type of real 4. I am
wondering what the value of -1.#IND means.
Thanks Jeff Daun
It generally means you hit an issue with values using
approximate data types - there are some values where the
representation of the value gets hosed out. You will see
values along the lines of 1.#NF or 1.#ND for approximate
data types when you hit this in SQL Server.
-Sue
On Tue, 17 Jan 2006 09:26:06 -0800, Jeff Daun
<JeffDaun@.discussions.microsoft.com> wrote:

>I have a value in a SQL table of -1.#IND for a data type of real 4. I am
>wondering what the value of -1.#IND means.
|||Thank you
Thanks Jeff Daun
"Sue Hoegemeier" wrote:

> It generally means you hit an issue with values using
> approximate data types - there are some values where the
> representation of the value gets hosed out. You will see
> values along the lines of 1.#NF or 1.#ND for approximate
> data types when you hit this in SQL Server.
> -Sue
> On Tue, 17 Jan 2006 09:26:06 -0800, Jeff Daun
> <JeffDaun@.discussions.microsoft.com> wrote:
>
>
|||I was wondering how would I be able to view the data in the column so that I
can find the value being put in the field from the application so that I can
find the correct precision to use?
Thanks Jeff Daun
"Sue Hoegemeier" wrote:

> It generally means you hit an issue with values using
> approximate data types - there are some values where the
> representation of the value gets hosed out. You will see
> values along the lines of 1.#NF or 1.#ND for approximate
> data types when you hit this in SQL Server.
> -Sue
> On Tue, 17 Jan 2006 09:26:06 -0800, Jeff Daun
> <JeffDaun@.discussions.microsoft.com> wrote:
>
>
|||I don't think so as it has a value that the SQL Server can't
represent. It's not really a matter of the precision as much
as a value getting in there that shouldn't have. If I
remember, the issue is due to SQL Server not fully
implementing the IEEE 754 standard for binary representation
of floating point numbers.
-Sue
On Wed, 18 Jan 2006 07:09:03 -0800, Jeff Daun
<JeffDaun@.discussions.microsoft.com> wrote:

>I was wondering how would I be able to view the data in the column so that I
>can find the value being put in the field from the application so that I can
>find the correct precision to use?

Data type Object in Send Mail Task?

I have an Execute SQL Task that runs a simple SELECT query. I have the result set = Full Result Set. The variable is of type Object and the value is System.Oject. After successful completion of the Execute SQL Task, I am doing a Send Mail task. For the Message Source, I want to use this Object. The drop down is only listing variables of type String. Can you not use a variable of type Object in a Send Mail Task? If not, what is the easiest workaround? Thanks!

No, you can't use an object for the message source. You could use a column from the resultset, though. If you give more description on what you are trying to do, we'll be able to suggest some work arounds.

If you are trying to send the contents of the recordset in an email, here are a few posts that might help:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1768087&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1502762&SiteID=1

Data Type Conversions

I am trying to convert int to date. first I have converted the int value to string(dt_str), using 'Data Conversion' Transformation, then again I have used another 'Data Conversion' Transformation to convert it into date. but its giving the following error

[Data Conversion 1 [3643]] Error: Data conversion failed while converting column "THEDATE_INTO_STR" (2799) to column "CALENDAR_YEAR1" (3657). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

What are the simple ways to convert from one data type to another? If somebody could give some example of the above...............Try using the Derived Column component to do the casting. Its more flexible than Data Conversion component.

-Jamie|||Right, you might need the flexibility of expressions in derived column.
What is the meaning of the integer as a date?|||int as a date meansI have one int type in source, which has data like20051211....
but my target column is of datetime.
Anyways, it done now...

Thanks

Data Type Conversions

I am trying to convert int to date. first I have converted the int value to string(dt_str), using 'Data Conversion' Transformation, then again I have used another 'Data Conversion' Transformation to convert it into date. but its giving the following error

[Data Conversion 1 [3643]] Error: Data conversion failed while converting column "THEDATE_INTO_STR" (2799) to column "CALENDAR_YEAR1" (3657). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

What are the simple ways to convert from one data type to another? If somebody could give some example of the above...............Try using the Derived Column component to do the casting. Its more flexible than Data Conversion component.

-Jamie|||Right, you might need the flexibility of expressions in derived column.
What is the meaning of the integer as a date?|||int as a date meansI have one int type in source, which has data like20051211....
but my target column is of datetime.
Anyways, it done now...

Thanks

Data type Conversion during update ...

I am trying to update a row in a table. To my SP, I send an XML string.
One of the columns I am trying to update is of type "Bit" and the value I am
getting from my app is either "true" or "false". This is the general
structure of sp:
UPDATE x
SET x.isEmployee = xmlEmp.isEmployee
WHERE ...
I tried the following to convert my xmlEmp.isEmployee to "BIT" ...
SET x.isEmployee = CASE WHEN xmlEmp.isEmployee='false' THEN 0 ELSE 1 END
SET x.isEmployee = CASE WHEN xmlEmp.isEmployee='false' THEN CAST(0 AS BIT)
ELSE CAST(1 AS BIT) END
both of them don't convert the strings 'false' and 'true' to 0 and 1 ... not
sure what I am missing. Appreciate any tips. TIA.
"exBK" <exBK@.discussions.microsoft.com> wrote in message
news:09146AA0-F234-45F6-9B71-D4F90FDED3B2@.microsoft.com...
>I am trying to update a row in a table. To my SP, I send an XML string.
> One of the columns I am trying to update is of type "Bit" and the value I
> am
> getting from my app is either "true" or "false".
That should work...
declare @.x varchar(10), @.y bit
set @.x = 'true'
set @.y = case when @.x = 'false' then 0 else 1 end
select @.y
The above results in 1. Are you getting an error?
Bryant

Data Type (Time Format)

Hi all,
I have a colume assigned to datetime format, where the value is say
"06:31:31" rather than 20/05/2004 06:31:31. When I retrieve the value from
the table, the result were 30/12/1899 06:31:31. Is there a way to change the
format so that I only need to put the time variable into the field as
opposed to the full datetime format?
Thanks
There is no datatype in SQL Server for just time. However, display is a
client issue; you should be able to pull just the time element out in
whatever language you're using for reporting/application development. You
can also use CONVERT to pull the time out as a text string:
SELECT CONVERT(CHAR(8), YourDateTimeCol, 8) AS TheTime
FROM YourTable
If, as I suspect, what you're storing is actually a duration, you might
consider storing it as number of seconds in a column of integer datatype
instead of using datetime. This will make calculations (if necessary) much
easier.
"VampireJ" <jwlchung@.yahoo.com.hk> wrote in message
news:ujXbOHmPEHA.556@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a colume assigned to datetime format, where the value is say
> "06:31:31" rather than 20/05/2004 06:31:31. When I retrieve the value from
> the table, the result were 30/12/1899 06:31:31. Is there a way to change
the
> format so that I only need to put the time variable into the field as
> opposed to the full datetime format?
> Thanks
>
|||Thanks very much, I will give that a try.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:e1WsrfoPEHA.2580@.TK2MSFTNGP09.phx.gbl...
> There is no datatype in SQL Server for just time. However, display is a
> client issue; you should be able to pull just the time element out in
> whatever language you're using for reporting/application development. You
> can also use CONVERT to pull the time out as a text string:
> SELECT CONVERT(CHAR(8), YourDateTimeCol, 8) AS TheTime
> FROM YourTable
> If, as I suspect, what you're storing is actually a duration, you might
> consider storing it as number of seconds in a column of integer datatype
> instead of using datetime. This will make calculations (if necessary)
much[vbcol=seagreen]
> easier.
>
> "VampireJ" <jwlchung@.yahoo.com.hk> wrote in message
> news:ujXbOHmPEHA.556@.tk2msftngp13.phx.gbl...
from
> the
>
|||hi
i have the same problem that you have.
can you please let me know if you find the solution.
thanks
alina
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

Data Type (Time Format)

Hi all,
I have a colume assigned to datetime format, where the value is say
"06:31:31" rather than 20/05/2004 06:31:31. When I retrieve the value from
the table, the result were 30/12/1899 06:31:31. Is there a way to change the
format so that I only need to put the time variable into the field as
opposed to the full datetime format?
ThanksThere is no datatype in SQL Server for just time. However, display is a
client issue; you should be able to pull just the time element out in
whatever language you're using for reporting/application development. You
can also use CONVERT to pull the time out as a text string:
SELECT CONVERT(CHAR(8), YourDateTimeCol, 8) AS TheTime
FROM YourTable
If, as I suspect, what you're storing is actually a duration, you might
consider storing it as number of seconds in a column of integer datatype
instead of using datetime. This will make calculations (if necessary) much
easier.
"VampireJ" <jwlchung@.yahoo.com.hk> wrote in message
news:ujXbOHmPEHA.556@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a colume assigned to datetime format, where the value is say
> "06:31:31" rather than 20/05/2004 06:31:31. When I retrieve the value from
> the table, the result were 30/12/1899 06:31:31. Is there a way to change
the
> format so that I only need to put the time variable into the field as
> opposed to the full datetime format?
> Thanks
>|||Thanks very much, I will give that a try.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:e1WsrfoPEHA.2580@.TK2MSFTNGP09.phx.gbl...
> There is no datatype in SQL Server for just time. However, display is a
> client issue; you should be able to pull just the time element out in
> whatever language you're using for reporting/application development. You
> can also use CONVERT to pull the time out as a text string:
> SELECT CONVERT(CHAR(8), YourDateTimeCol, 8) AS TheTime
> FROM YourTable
> If, as I suspect, what you're storing is actually a duration, you might
> consider storing it as number of seconds in a column of integer datatype
> instead of using datetime. This will make calculations (if necessary)
much
> easier.
>
> "VampireJ" <jwlchung@.yahoo.com.hk> wrote in message
> news:ujXbOHmPEHA.556@.tk2msftngp13.phx.gbl...
from[vbcol=seagreen]
> the
>|||hi
i have the same problem that you have.
can you please let me know if you find the solution.
thanks
alina
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

Data type

Hi,
I am trying to find the data types of the fields in my table "XYZ"....Since i have 140 fields in that table, i like to store a value in INT datatype.
Does anyone it on top of their head? Really appreciate the help...

Thanks.

Do you mean a one-time query to take a peek at the data types in your table? Try this:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTable'
|||Thanks. Really appreciate your help..

data transform

I want the value of field A if value field B = C, otherwise the value of A =
D and field A and field B are located in the same table. Can anyone please
advise me how to code it in DTS transformation? Many thanks.Eric
Look at thsi script helps you with the problem
BTW why do you need to use DTS for this purpose?
CREATE TABLE #Test
(
A INT,
B INT,
C INT,
D INT
)
go
INSERT INTO #Test VALUES (1,2,3,4)
INSERT INTO #Test VALUES (10,2,2,2)
INSERT INTO #Test VALUES (2,4,11,15)
INSERT INTO #Test VALUES (30,1,22,20)
go
UPDATE #Test SET A=CASE WHEN B=C THEN A ELSE D END FROM #Test
go
SELECT * FROM #Test
"Eric Clapton" <no_spam@.bk.com> wrote in message
news:uOZ8ZmyCEHA.1604@.TK2MSFTNGP11.phx.gbl...
> I want the value of field A if value field B = C, otherwise the value of A
=
> D and field A and field B are located in the same table. Can anyone
please
> advise me how to code it in DTS transformation? Many thanks.
>|||I am exporting a table to a text file using the DTS import/export and I want
one field transform to another based on another field value. I need the code
after I click the transform button. I should be more specific earlier. Sorry
about that. Can you please help me? Thanks.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OhhIlsyCEHA.1452@.TK2MSFTNGP09.phx.gbl...
> Eric
> Look at thsi script helps you with the problem
> BTW why do you need to use DTS for this purpose?
> CREATE TABLE #Test
> (
> A INT,
> B INT,
> C INT,
> D INT
> )
> go
> INSERT INTO #Test VALUES (1,2,3,4)
> INSERT INTO #Test VALUES (10,2,2,2)
> INSERT INTO #Test VALUES (2,4,11,15)
> INSERT INTO #Test VALUES (30,1,22,20)
> go
> UPDATE #Test SET A=CASE WHEN B=C THEN A ELSE D END FROM #Test
> go
> SELECT * FROM #Test
>
> "Eric Clapton" <no_spam@.bk.com> wrote in message
> news:uOZ8ZmyCEHA.1604@.TK2MSFTNGP11.phx.gbl...
A
> =
> please
>