Exists

Lose the 'as date' when using it in a query.

select * from T1
where not exists(select 1 from T2 where T2.Name=T1.Name and cast([Time])='2013-10-02')

Select allOpen in new window

Also, just in case Time is the actual column name, it's not considered a best practice to name columns the same name as functions, data types, etc. as this will cause confusion and possibly the query to fail.

The 'as date' is used when you're declaring and assigning variables in one step.

Declare @big_stuff bit = 1

Select allOpen in new window

?
DWTime exists in which table t1 or t2? (we don't know this)
DWTime isn't referenced in you query either. Is it the last line you are referring to?

& is [Time] a time data type?

plus, there could be overall more efficient way of doing this.

Perhaps you could you provide some sample data (just a small sample) from t1 and t2?

jimhorn, I tried it but no result

 PortletPaul,
DWTime is DateTime type.
sorry it was typo, the correct query is

select * from T1
where not exists(select 1 from T2 where T2.Name=T1.Name and cast(DWTime as date)='2013-10-02')
And Type=2
and cast(DWTime as date)='2013-10-02'

making quite a few assumptions, this might work:

SELECT
        T1.*
FROM T1
LEFT JOIN T2
        ON t1.name = t2.name
        AND (t2.[DWTime] >= '2013-10-02' AND t2.[DWTime] < '2013-10-03') -- sargable equivalent of cast([field] as date)

WHERE t2.name IS NULL --<< the equivalent of your 'not exists'
AND (t1.[DWTime] >= '2013-10-02' AND t1.[DWTime] < '2013-10-03') -- sargable equivalent of cast([field] as date)
;

Select allOpen in new window

by using date ranges as shown here casting the data is avoided. It's generally better for performance to avoid changing the data to suit a filter; instead change the method of filtering to suit the data. (the number of filters in way smaller than the number of rows, so the query is doing less work, PLUS the query can utilize indexes)

see: http://en.wikipedia.org/wiki/Sargable

Rules of thumb:
Avoid functions using table values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.

Thanks your reply confirms some of my assumptions.

Note '2013-10-02' and '2013-10-03' might be parameter driven, like this:

declare @from as datetime
set @from = '2013-10-02'

SELECT
        T1.*
FROM T1
LEFT JOIN T2
        ON t1.name = t2.name
        AND (t2.[DWTime] >= @from AND t2.[DWTime] < dateadd(day,1,@start))

WHERE t2.name IS NULL --<< the equivalent of your 'not exists'
AND (t1.[DWTime] >= @from AND t1.[DWTime] < dateadd(day,1,@start)) 
;

Select allOpen in new window

oops, you need to add back this in the where clause:

and t1.type = 2

Thanks for reply.
Forgot @start ;),
The date and Type filter work fine.
But I need all name in T1 not exists in T2.

>>But I need all name in T1 not exists in T2.

a 'left outer join' between t1 and t2, PLUS:

where t2.name IS NULL

is the same - functionally - as your 'NOT EXISTS' correlated subquery

you can continue to use the NOT EXIST() construct if you prefer it, but the results should be the same.

Otherwise: I'm not understanding your comment

you may need to provide some sample data and an expected result from that data to illustrate what should be achieved.

Excellent, it works.
Thanks