Nice one roockmoose

Knew there was a way without convert but could not remember it

create function getMthEndDate (@date datetime)

returns datetime

as

begin

return DATEADD(d,-1,DATEADD(m,1,LEFT(CONVERT(varchar,@date,112),6)+'01'))

end

go

declare @wantednum varchar(10)

set @wantednum = '10'

select ID

from

where charindex(' ' + @wantednum + ' ',' ' + nums + ' ') > 0

Note that this...

on b.field1 = a.field - 1

should have been

on b.field1 = a.field1 - 1

If field1 is not int then you will have to cast the column to the correct data...

again this points to a data issue

run this

select [service date]

from

where [service date] > 0

and isdate([service date]) = 0

to what values sql considers not a date

You cannot convert character zero to a datetime.

You can convert integer zero to datetime as in

select cast(0 as datetime)

which will give you 1900-01-01 00:00:00.000

You will have to change zeros to...

Check that all your input data is formatted as yyyymmdd.

select cast(cast(20031224 as varchar) as datetime)

will work

select cast(cast(20032412 as varchar) as datetime)

will give conversion error

SELECT [Part#],

  SUM(CASE WHEN MONTH([Replacement Date]) = 1  THEN [Replace Qty] ELSE 0 END) AS [Jan],

  SUM(CASE WHEN MONTH([Replacement Date]) = 2  THEN [Replace Qty] ELSE 0 END)...

update a

set a.field2 = b.field2

from tablea a

inner join tablea b

on b.field1 = a.field - 1

where a.field2 is null

as per above, try this

declare @sql nvarchar(100), @idno int

set @sql = 'insert into ' + @databasename + '.dbo.tablea (cola,colb values (valuea,valueb) set @idno = SCOPE_IDENTITY()'

exec sp_executesql @sql,N'@idno int output',@idno...

DTS is Data Transformation Services and allows the copying and transformation of data between databases and or files.

BCP is Bulk Copy Program and allows the transfer (in or out) of data...

Try this

alter procedure prRecCount @vTbl varchar(25) as

declare

 @sql varchar(2000),

 @vCnt int

 set @sql = 'select @vCnt = cnt from openquery( {servername},''select count(*) cnt from ' + @vTbl +''')'

 exec sp_executesql @sql,

Not knowing what the output looks like but...

isql has a default output width of 80. If the output line is longer isql will split it into several lines.

So,

isql -Q "select...

I used to get this with SQL7.

I installed SQL7 SP4 and MDAC 2.7 (on both client and server) and problem went away.

Why not use DTS or BCP. You can schedule either in sql server agent.