blueagle,

 

You posted...

>You can create UDF to get the whole structure based on the specified root ID. That's easy and much more flexible. 

 

Mind telling us how to do that?  

All well and good...

But the "poster" said that he wanted to do this as a VIEW!   I asked the question a while back, "How...

pbaldy,

Would you mind sharing what you did?

>I would like to do this in a view if possible becuase it is going to be used as the data source to a report designer.

How would you pass a...

Tac,

 

Haven't tested this but should (may?) work...

 

declare @db SYSNAME

set @db =

(select TOP 1 Name from dbo.sysdatabases

where name like 'CCM%'

order by crdate DESC )

EXEC ('USE '+ @db

Brendon,

 

If your dates are, in fact, stored as SQL DateTime datatypes, there is no need to convert the dates to numeric representations to have them sort correctly.  Just do an...

noeld had it right but swapped the @StartDate and @EndDate... his SELECT statment should have looked like this and THEN you would probably have the correct answer...

select Convert(varchar(5), @ENDDate -...

Actually, I think you're using the wrong function... in most cases, CAST and CONVERT work the same way...

...except for dates and then CONVERT works very nicely for converting DateTime values...

First, thanks for pointing out the difference there, Butch.  Guess I better get some glasses!

And, thank you pnewhart, for pointing out just exactly...

Maybe it's just me and I really am going blind... would someone tell me what the difference is between the two lines that pnewhart put in his last post?

My experience has been that 2 DELETE queries will run much faster than a single query with a BOOLEAN "OR".  Also, even with the "SIMPLE" recovery method set, the deletes...

As I alluded to in my original posting...

Casting (or Converting) the date as INT or BIGINT may give you the wrong (next day)...

The basis of sp_executesql is that it is calling a Stored Procedure in your database, somewhere.  Stored Procedures are "precompiled". That is, the execution plan for the Stored Procedure was created...

Jason,

Just in case you still don't have an answer...

If you want to do the join as if TIME where not included in the...

Be careful... if any NULLS, answer will be NULL unless "Concatenate Nulls Yields Nulls" is turned off.