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?
>

DataAdapter - SELECT Statement - items in last 30 days

I'm using DataList to return vales stored in an SQL database, of which one of the fields contains the date the record was added.

I am trying to fill the dataset with items only from the last 30 days.
I've tried a few different ways, but all the database rows are returned.

What is the WHERE clause I sholud use to do this??

ThanksTry with the following SQL statement, i belive it should work.

select * from <tablename> where datediff(day, <columnname>, getdate()) < 30

Hope it solves your issue.|||Thanks very much, it worked a treat

Data Values Validation

Hello all...I am trying to validate that the new work date that is being imported from the text file does not exist in the table. So in other words I do not want duplicate data. This is what I haveSqlDataReader dr =newSqlDataReader(); SqlParameter sp =newSqlParameter("@.WorkDate", Data.SqlDbType.datetime, 8, Data.ParameterDirection.Input); if (dr.HasRows) sp ="@.WorkDate"; else *How does that look? Am I in the ball part…

Try using a stored procedure but instead of

INSERT INTO tablename (WorkDate) VALUES (@.WorkDate)

have

IF NOT EXISTS(SELECT * FROM tablename WHERE WorkDate =@.WorkDate)
INSERT INTO tablename (WorkDate) VALUES (@.WorkDate)

That way you can call the stored procedure passing in the WorkDate value, but a record will only be inserted if the value does not already exist.

|||

Thanks TAT for your help... question, what if a record in there with the same date how would I let the user know?

|||

To let the user know, you need an additional parameter so your stored procedure would look like this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: Add record if not already on file
-- =============================================
CREATE PROCEDURE dbo.usp_TableNameAdd
@.WorkDate DateTime,
@.Exists BIT OUTPUT
AS
SET NOCOUNT ON;
SET @.Exists = 0
IF NOT EXISTS(SELECT * FROM tablename WHERE WorkDate = @.WorkDate)
INSERT INTO tablename (WorkDate) VALUES (@.WorkDate)
ELSE
SET @.Exists = 1
GO

|||

Another way could be to raise user friendly error message from within your SP. Read BOL for RAISERROR.

|||

I have a few more questions. When calling the sp I am getting an error message also in the SP the staring lines

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

are not working for me, is there something I am not seeing.

namespace WindowsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
//Pull from Text File in from Network Drive
string Source = @."\.\J-1119.txt";
string Date = "";
string O1 = "";
string A1;
string P1;
string R1;
string LineIn;
StreamReader InFile = new StreamReader(Source);

***TRYING TO CALL MY SP****
SqlCommand SC = new SqlCommand("SP_PromoNoDups", sConnect);

SC.Parameters.Add(new SqlParameter("@.WorkDate", WorkDate));
// SqlCommand SC = new SqlCommand("Select * from [tblname]", sConnect);
SC.CommandType = CommandType.StoredProcedure;

LineIn = InFile.ReadLine();
// pos is the location of the cursor
int pos = LineIn.IndexOf("WORK DATE:");
if (pos > 0)
{
Date = LineIn.Substring(pos + 10, 10).Trim();
}
while (LineIn != null)
{
if (LineIn.IndexOf("O1:") == 2)
{
if (LineIn.Length > 10)
{
O1 = LineIn.Substring(10).Trim();
}
else
{
O1 = "";
}
LineIn = InFile.ReadLine();
LineIn = InFile.ReadLine();
while (LineIn != null && LineIn.Trim() != "")
{
P1 = LineIn.Substring(2, 5).Trim();
A1 = LineIn.Substring(19, 20).Trim();
R1 = LineIn.Substring(41, 6).Trim();
string Adjustment = LineIn.Substring(57).Trim(); LineIn = InFile.ReadLine();
if (LineIn.IndexOf("mane") >= 0)
{
break;
} SC.CommandType = LineOut;(Erroring out here)
SC.ExecuteReader();
// if page header - look for next O1
}
LineIn = InFile.ReadLine();
LineIn = InFile.ReadLine();
}
InFile.Close();
}
}
}
}

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 issue

Hi guys
I exported some data from a text file to sql server. Here is the sample data..

This table has about 2 million rows.There is a date field in the table which comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i get an error as operation timed out..

Here is the data from the text file...

Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag Patient category Units dispensed Total days supply
1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00

I have tried the bulk insert as well.

Here is the script for the create table ..

USE [Library]

GO

/****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[NormalOutlier1](

[Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,

[Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,

[Formulation ID] [float] NULL,

[Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,

[NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,

[Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,

[Units dispensed] [float] NULL,

[Total days supply] [float] NULL

) ON [PRIMARY]

Hope this helps

Did you try using a datetime rather than NVARCHAR(MAX) in the definition of the table ? There might be a implicit conversion possible for the values. if not you will have to convert the dates with your own logic using either an ETL process in DTS / SSIS or just by using a string manipulation.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Data transformation services

Hi

I was told that using DTS will allow me to schedule stored procedures to keep an sql database up to date. For example if a user registers but does not activate the registration, his details will be removed by a stored procedure which is scheduled to run every 24 hours. I use to use the global.asax file to fire a update by using a file containing a the date of the last update and then by adding 24 hours to it, it would execute a SP to delete unwanted data.

I have tried to install DTS with no success. I am running the following

Visual web studio express

SQL 2005 Express. (From SQLExpr_exe) and I have told it to install all the extra components

Installed SQLEXPR_Toolkit.exe with all its options

Installed SQLServer2005_DTS.MSI

When I go into the sql server using MS SQL Server Management Studio Express. I cannot see the Data transformation services node. I have also just installed server reports which I had no problems installing.

Can somebody please help me.

DTS is a SQL2000 component; SQL2005 hasa totally rewritten equivalent called SSIS. An SSIS (SQL Server Integration Sercvices) job amongst other things will run stored procedures for you. However it is SQL Agent that provides the scheduling capability.

|||

Hi

Thanks for the reply. I need to know where to download the ssis installation application. The other thing is my service provider that I use uses SQL2000. Im developing in SQL Express 2005. How will I deploy the scheduled jobs to there server if im using the newer version.

Regards