Just checking - are the zip codes defined as text or numeric within Excel? If they are numeric, that is your problem (regardless of how they are displayed in...

It's not pretty, but see whether this helps:

declare @sql varchar(400)

set @sql = 'select * from ?.information_schema.tables where table_name = ''[tablename]'''

exec sp_msforeachdb @sql

--replace [tablename] with the name of the table you...

The original poster asked for a way of retrieving just the date part of a datetime field.  Your method returns exactly the opposite - the time part - in a...

Hi Ed

We need to get to the bottom of why so many records are being returned first, I think.  This is almost certainly because of the fact that you have...

No problem!

Should work. Is state_id a text field? Then you'd need

state_id = '101'

Or does it need to be trimmed?

trim(state_id) = '101'

Aha ... just seen your amended post - good.

Something like this?

select t.transporter_name, a.state_id

from transporters t left outer join association a on

t.transporter_id = a.transporter_id

where state_id = 101 or state_id is null

Perhaps you could run Profiler to check the SQL Server side of the equation? Otherwise, I'm running out of ideas.

Good luck

Phil

So do you know at what stage in the process that the conversion is failing for these records?

I'm thinking it could be

a) Something in the logic of the conversion program...

So you've got everything you can find on your server set to US format and you're still getting d/m/y date format problems from somewhere?

So are you trying to make your SQL Server 'pretend' it is American? Don't know whether this will work, but you might try changing the control panel/regional options on...

Hi Ed

If, for a particular record in 'organization', a related record does not exist in the 'totals' table, all of the fields retrieved from the totals table will be NULL.

So...

In general, for readability ...

'On' should be used for joins between tables:

select * from tablea a inner join tableb b

on a.id = b.id

etc

'Where' should be used to filter the results...

Are you looking for all the combinations -

PRIM1 | A | B

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

1 A1 B1

1 A1 B2

1 A1 B3

1 A2 B1

1 A2 B2

1 A2 B3

From your post, I'm guessing not....

Agree with you Joseph - same has happened to me several times, on the longer, more time-consuming posts of course. There must be a way round this that doesn't...