The boolean predicate is NOT behaving as expected! I expected it to return TRUE or FALSE based on the argument...it does not do...

No. The boolean predicate is working as expected. However when dealing with NULLs you face the Three-valued logic of NULL. Anything compared with NULL yields NULL (or UNKNOWN, for that...

Check out LEN() and/or DATALENGTH() in BOL.

select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, '1900-1-1', getdate()) + 1, '1900-1-1'))

won't catch any row on that day with a time portion > 00:00:00.000. If your data contains a time portion, such...

CREATE TABLE t

(

 k1 INT NOT NULL

 , c1 CHAR NOT NULL

 CONSTRAINT pk_t PRIMARY KEY(k1)

)

GO

CREATE FUNCTION dbo.CloseMyGaps() RETURNS INT

AS

BEGIN

 RETURN

  CASE

   WHEN EXISTS

        (SELECT *

           FROM t

          WHERE k1 = 1)

  ...

Something like this?

DECLARE @a VARCHAR(20)

SET @a = 'TW11 7PR'

SELECT LEFT(@a,PATINDEX('%[0-9]%',@a)-1)

SET @a = 'TESTME_All OVER11 7PR'

SELECT LEFT(@a,PATINDEX('%[0-9]%',@a)-1)

--------------------

TW

(1 row(s) affected)

--------------------

TESTME_All OVER

(1 row(s)...

Check this out: http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0761.mspx

midan1,

not to sound offending here, but I remember several threads started by you about this issue or *very* similar issues. Haven't all the answers you got there helped?

Please think about your exact...

Caution with the time portion of GETDATE()!

SELECT GETDATE()-14 

------------------------------------------------------

2005-11-11 11:36:06.320

(1 row(s) affected)

If you have any time portion in your data you might not catch data from 2005-11-11 entered earlierer...

Oh, btw, Jeff, I'm not adding 31 days. I only use a different base date.

...and finally...

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)  

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

Since you already have a good solution for the first day in a month with

select DATEADD(m,DATEDIFF(m,0,GETDATE()),0)

Why not use this to get the last day in a month, too?

select DATEADD(m,DATEDIFF(m,0,GETDATE()),31)

Dynamic SQL. Basically you build your query string at runtime and execute it via sp_ExecuteSQL or EXEC(). See if this helps: http://www.sommarskog.se/dynamic_sql.html

Btw, are you the guy who has developed the Mambo/Joomla Bookmarks component?

DECLARE @a VARCHAR(30)

SET @a = 'like if we do it for: i'+'x'

SELECT LEN(@a), LEN(REPLACE(@a, 'i', ''))

----------- -----------

24          20

(1 row(s) affected)

Caution when there is a...