Using Query Analyzer,

Select Tools | Options | Results

check the "Output Query" checkbox.

End of month logic for "YourDate":

DATEADD(d, -1, DATEADD(m, 1 + DATEDIFF(m, 0, @YourDate), 0))

Also, first of month logic:

DATEADD(m, DATEDIFF(m, 0, @YourDate), 0)

Your statement

select IDENT_CURRENT([linkedserver].database.table) 

should be

select IDENT_CURRENT([linkedserver].database.dbo.table) 

The owner is a required piece of the four-part name.

select * from t

where DATEDIFF(m, testdate,  GetDate()) > 0

DATEADD(d, DATEDIFF(d, 0, @YourDate), 0)

This works for smalldatetime and datetime, and does not involve conversions to other datatypes.

What about this in the ORDER BY clause...

ORDER BY CASE WHEN tbl_CompanyCategories.vcName = 'other' THEN 1 ELSE 0 END, tbl_CompanyCategories.vcName

There is no sense in updating a Field to its current value.

Update MyTable set AlteredDate = case td

when 'N' then dateadd(d, -2, ed)

else dateadd(d, -1, ed) end

where td IN ('I', 'N')

In place of

WHERE     (data.tblBooking.DateD >= GETDATE())

use

WHERE  (DATEDIFF(d, GETDATE(), data.tblBooking.DateD) > 0)

No conversions, no substrings, works with or without non-midnight time portion.

Or, simply replace the offending zero in the divisor with NULL:

NULLIF((TOTAL_LOGIN_TIME - TOT_WAIT_TIME), 0)

This will cause the entire calculation to return NULL.

Then, if necessary, deal with the resulting NULL afterwards.

It appears Microsoft is doing something like "WITH TIES" behind the scenes for 100 PERCENT.  However, for values other than 100 PERCENT, the resultset CAN change, as your test data...

A correction to logic posted earlier...

create view dbo.foobar

as

select top 100 percent WITH TIES * from authors order by au_lname

go

select * from dbo.foobar

drop view dbo.foobar

December 1, 2004 at 8:31 am