Lempster (1/28/2014)
As the first reply states, it would be easier to this using date or datetime data types, but assuming that you are locked in to using the varchar data type, you could do the following.I've included the table creation and inserting sample data steps for clarity.
CREATE TABLE dbo.#DateTest
(
FromDate varchar(9)
,ToDate varchar(9)
)
Insert some sample data:
INSERT INTO dbo.#DateTest VALUES ('201401221','201401292'),('201401092','201401161'),(NULL,'201402282'),('201401152',NULL)
Declare a variable to hold today's date in integer format:
DECLARE @Today int = CAST(CONVERT(varchar,getdate(),112) AS int)
In your post you've got your condition round the wrong way...unless you system date is set to 19000101 or earlier!
SELECT ISNULL(CAST(SUBSTRING(FromDate,1,8) AS int),19000101) AS FromDate, ISNULL(CAST(SUBSTRING(ToDate,1,8) as int),99991231) AS ToDate FROM dbo.#DateTest
WHERE ISNULL(SUBSTRING(FromDate,1,8),19000101) <= @Today
AND ISNULL(SUBSTRING(ToDate,1,8),99991231) > @Today
Result:
FromDateToDate
2014012220140129
1900010120140228
2014011599991231
EDIT: Removed an unnecessary CAST from the WHERE clause.
Regards
Lempster
I'm just taking a short break so I don't have the time to write some code for that but the code above guarantees than an index seek is impossible (ie. Non-SARGable). If someone doesn't beat me to it, I'll try to get back to this tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.