Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Comparing string with date Expand / Collapse
Author
Message
Posted Tuesday, January 28, 2014 6:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:18 AM
Points: 27, Visits: 96
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.

Post #1535416
Posted Tuesday, January 28, 2014 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 8:21 AM
Points: 7, Visits: 46
everything needs to be a date in your second query.
Post #1535447
Posted Tuesday, January 28, 2014 8:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 6,869, Visits: 14,178
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1535475
Posted Tuesday, January 28, 2014 8:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 21, 2014 7:37 AM
Points: 2,127, Visits: 1,485
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:

FromDate	ToDate
20140122 20140129
19000101 20140228
20140115 99991231

EDIT: Removed an unnecessary CAST from the WHERE clause.

Regards
Lempster
Post #1535502
Posted Tuesday, January 28, 2014 12:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,589, Visits: 32,179
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:

FromDate	ToDate
20140122 20140129
19000101 20140228
20140115 99991231

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1535582
Posted Tuesday, January 28, 2014 9:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:18 AM
Points: 27, Visits: 96
THANK YOU FOR THE REPLY LEMPSTER
IT WORKED:)
Post #1535701
Posted Tuesday, January 28, 2014 9:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:18 AM
Points: 27, Visits: 96

THANK YOU FOR THE REPLY
Post #1535702
Posted Wednesday, January 29, 2014 9:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,589, Visits: 32,179
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1535972
Posted Wednesday, January 29, 2014 9:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 6,869, Visits: 14,178
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1535976
Posted Wednesday, January 29, 2014 9:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,589, Visits: 32,179
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1535984
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse