Comparing string with date

Question

Post reply

Comparing string with date

kishorefeb28

Ten Centuries

Points: 1087
More actions
January 28, 2014 at 6:51 am

#285049

Hi Experts,
i have to write a where condition where i need to compare string with date
i have 2 columns FROMDATE and TODATE with datatype varchar(9)
the strings in the columns looks like YYYYMMDD+'1' or YYYYMMDD+'2' here 1 is Am and 2 is PM
i.e., 201401011 or 201401012
so i need to chop off last character before using them in WHERE condition.
now i need to write a where condition like [if todays date is in between fromdate and todate columns then return rows.
if FROMDATE column is null it should take minimum date 1900/01/01 if TODATE is null then date should be 9999/01/01
the query i wrote is
select * from Table where ISNULL(SUBSTRING(VALIDTO,1,8),'19000101') > = getdate()
AND ISNULL(SUBSTRING(VALIDTO,1,8),'99991231') < convert(varchar, getdate(), 112)
but it shows no data
please help me pass through this
Thank you.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

andrew.edgar SSC Veteran Points: 225 More actions · Answer 1

andrew.edgar

SSC Veteran

Points: 225

January 28, 2014 at 7:32 am

#1683922

everything needs to be a date in your second query.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Use the source column for the string-dates, as discussed here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lempster SSCoach Points: 15651 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kishorefeb28 Ten Centuries Points: 1087 More actions · Answer 5

kishorefeb28

Ten Centuries

Points: 1087

January 28, 2014 at 9:36 pm

#1684132

THANK YOU FOR THE REPLY LEMPSTER

IT WORKED:)

kishorefeb28 Ten Centuries Points: 1087 More actions · Answer 6

kishorefeb28

Ten Centuries

Points: 1087

January 28, 2014 at 9:37 pm

#1684133

THANK YOU FOR THE REPLY

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

Post deleted. I made a mistake. I'll be back.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Jeff Moden (1/29/2014)
Post deleted. I made a mistake. I'll be back.

A little history Jeff. Might save you some time.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

Heh... crud. I posted the same mistake... I'll be back.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

ChrisM@Work (1/29/2014)
Jeff Moden (1/29/2014)
Post deleted. I made a mistake. I'll be back.
A little history Jeff. Might save you some time.

You're right. That's insane and now I know what you meant in your original post. The OP changes perfectly good data to denormalized data and then tries to do a search on the denormalized data. I just don't get that and your original suggestion on this post is correct.

Too bad because I finally learned how to copy'n'paste my SARGable solution correctly. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lempster SSCoach Points: 15651 More actions · Answer 11

Jeff Moden (1/29/2014)
Too bad because I finally learned how to copy'n'paste my SARGable solution correctly. :blush:

Could you post your solution anyway Jeff?

Regards

Lempster

andrew.edgar SSC Veteran Points: 225 More actions · Answer 12

andrew.edgar

SSC Veteran

Points: 225

January 30, 2014 at 2:43 am

#1684566

..

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 13

Lempster (1/29/2014)
Jeff Moden (1/29/2014)
Too bad because I finally learned how to copy'n'paste my SARGable solution correctly. :blush:
Could you post your solution anyway Jeff?
Regards
Lempster

Sure. With the understanding that such data should never be stored in a table and that it should be normalized as a real DATETIME column and a separate column for the "time of day slot" indicator, here's how to solve this problem in a SARGable fashion. For those that don't know, "SARGable" has come to basically mean "can do an Index Seek if the correct supporting index is available".

--=============================================================================

-- Create a larger test table with the appropriate index

-- Only adding the appropriate index is a part of the solution

--=============================================================================

--DROP TABLE dbo.#DateTest

GO

--===== Create the table, as before

CREATE TABLE dbo.#DateTest

(

FromDate varchar(9)

,ToDate varchar(9)

)

;

--===== Insert the original 4 rows in the test data

INSERT INTO dbo.#DateTest

SELECT '201401221','201401292' UNION ALL

SELECT '201401092','201401161' UNION ALL

SELECT NULL ,'201402282' UNION ALL

SELECT '201401152',NULL

;

GO

--===== Insert another 16380 similar rows

INSERT INTO #DateTest

SELECT * FROM #DateTest

GO 12

--===== Add the expected index

CREATE INDEX IX_#DateTest

ON #DateTest (FromDate,ToDate)

;

--=============================================================================

-- Demonstrate the the current solution will NOT do an Index Seek

-- and a method that will. The Index Seek is followed by a nice

-- high performance range scan

--=============================================================================

--===== Setup the variable for @Today to make testing easy

DECLARE @Today DATETIME

SELECT @Today = GETDATE() --Or whatever

;

--===== This CANNOT do an Index Seek because of the formulas

-- on the FromDate and ToDate columns (non-SARGable).

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

;

--===== This DOES do an Index Seek because there are no forumulas

-- on the table columns in the WHERE clause (SARGable).

SELECT FromDate = FromDate -- ISNULL(FromDate,'19000101')

,ToDate = ToDate -- ISNULL(ToDate ,'99991231')

FROM dbo.#DateTest

WHERE (FromDate <= CONVERT(CHAR(8),@Today ,112)+'9' OR FromDate IS NULL)

AND (ToDate >= CONVERT(CHAR(8),@Today ,112)+'0' OR ToDate IS NULL)

;

Notice the neither FromDate or ToDate is contained in a formula.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lempster SSCoach Points: 15651 More actions · Answer 14

Thanks Jeff and I totally agree with you about doing this the proper way using DATETIME data type which is what I prefaced my first reply to the OP with.