ok here is break down of the code:

--Month

WHERE [YourDate] BETWEEN

DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())), 0)

AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0))

The from part of the date:

Step 1: DATEADD(mm,-1,GETDATE() = get the date from last...

Hi Grant,

I can't actually access that link to your book 🙁

this should work according to your criteria:

--Month

WHERE [YourDate] BETWEEN

DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())), 0)

AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0))

--Year

WHERE [YourDate] BETWEEN

DATEADD(yy, DATEDIFF(yy,0,DATEADD(yy,-1,GETDATE())), 0)

AND DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0))

Just so you know I done...

When you say last year and last month .

Do you mean the following

today's date = 23/03/2009

Therefore Last year = any day in 2008

Therefore Last month = andy day in...

Look up on BOL for the following datetime functions

DATEADD()

DATEDIFF()

see how you get on...

In SSMS Right click the db, properties then on the options tab, you will see at the top Compatibility level:

still no error on my side.

Are you sure you using compatability 90 and not 80?

very strange, I ran that code on SQL 2005 Compatibility 90 and it worked a charm, in fact it's my preferred solution.

what was the actual code you ran and the...

Hi ,

I'm interested to know why bob's solution didn't work?

What version of SQL are you using?

Please note the the "working" solution that ismail gave you return with a trailing comma...

Hi,

Have you tried my NOT EXISTS solution in 2005?

I found that to be the fastest...

select *

from Table1

where NOT EXISTS

(

SELECT 1

FROM Table2

WHERE substring(BIBNAC,patindex('%[^0]%', BIBNAC),len(BIBNAC) - patindex('%[^0]%', BIBNAC)...

yeah I think you will be waiting a while, I was going for 10minutes and then my stupid fat thumbs hit the stop button 🙁

so I'm still waiting 5minutes and...

OK here is another solution that seems super fast, it also looks like less IO than lynns.

select *

from Table1

where NOT EXISTS(

SELECT 1

FROM Table2

WHERE substring(BIBNAC,patindex('%[^0]%', BIBNAC),len(BIBNAC) -...

He he he he thanks Steve,

If only I had taken the chance clicked the subscribe button under options I would have seen what you are saying...

thanks so much sorry for...

oh and here is some sample stuff that I'm using:

CREATE TABLE Table1

(BIBNAC VARCHAR(100),Col1 INT,Col2 INT ,Col3 INT)

CREATE TABLE Table2

(ACCOUNTNUM VARCHAR(100))

CREATE CLUSTERED INDEX PK_1 ON Table1

(BIBNAC)

CREATE CLUSTERED INDEX PK_1 ON...

mmm I'm going crazy here...

Ok with the following query I can confirm that on 200 it takes milliseconds and in 2005 it takes well over 5 minutes and waiting.

I've done...