The first thing that springs to mind is that the varchar field contains data that cannot be converted to a numeric value ( or money ).

select * from stageTable where...

Glad it worked out for You.

For data manipulation / cleaning I always use SQL.

In DTS, if I use it, I put as little business logic as possible, if any.

The staging...

Yeah,

But

select convert(datetime,'00/00/0000')

is just as invalid as

select convert(datetime,'00-00-0000')

You could use vbs to convert '00/00/0000' to '01/01/1753' ( or what you prefer )

to make it a valid date.

or vbs to convert '00/00/0000'...

Hi Josh,

You are welcome.

and( SS.status_id = @entry or @entry is null)

if @entry is null this will always evaluate to true, thus return all the rows no matter what SS.status_id is.

if...

Hey JP,

I really would have loved it if your solution worked !?

Wouldn't that have been nice 🙂

/rockmoose

having an identity column or not will not affect performance.

having good indexes will affect performance.

every table should have a clustered index.

every table should not have an identity column just for...

Hi

Import the text file into a staging table ( with the datecolumn char(10) ).

Then use sql to scrub the data.

1. truncate stageTable

2. Source -> stageTable

3. insert realTable(,,,datecolumn,,,)

select ,,,case isdate(datecolumn) when...

Second questions... When I am testing this query in Query Analyzer and I hit the 'Play' button it creates the SP for me inside of the Enterprise Manager. This causes...

Whoa JP,

don't know what to say that was a bunch of code! ... 🙂

Here is a different approach....

We create 2 resultsets, lower and higher and take the average.

To overcome the...

SELECT COUNT(*) AS NoOfRows, MIN(RegDate) AS OldestDate FROM Table

/rockmoose

Why do You have to do it as an Active X script ?

You could specifiy a Query with apropriate SQL as the source.

/rockmoose

See:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=126525

declare @myday datetime

set @myday = '07/15/2004'

select datediff(day,dateadd(quarter,datediff(quarter,0,@myday),0),@myday) + 1 as day_of_quarter

day_of_quarter

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

15

set @myday = '08/15/2004'

select datediff(day,dateadd(quarter,datediff(quarter,0,@myday),0),@myday) + 1 as day_of_quarter

day_of_quarter

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

46

/rockmoose

True Noel, this is a requirement question for the application.

And I was wrong in generalizing that broadly...

Triggers is IMO a "last resort" for enforcing business rules, but ok, sometimes -...

And a very good opinion noeld, agree.

Don't format until the data is presented to the user.

/rockmoose