How to convert char 10/2013 to use the filter >=

Question

How to convert char 10/2013 to use the filter >=

Golden_Eye

SSC-Addicted

Points: 463
More actions
February 2, 2014 at 6:50 am

#285134

Hello,
I have a probllem with one part of my script which is:
where BillingMonth = '10/2013'
It is working fine now, but I need to change it to
where BillingMonth >= '10/2013'
and now it is not working, because BilingMonth is datatype char(7) and I can not use >=
Any idea/workaround how to do that?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Dwain Camps SSC Guru Points: 86908 More actions · Answer 1

The best advice you're going to get is that you should not be storing your billing month as CHAR(7). Instead, a better choice would be to store it as data type DATE and save in it '2012-05-01' instead of '05/2013'

I know, I know. You can't change the data type of that column. I hear it all the time. But that doesn't mean it is not the best advice you can get.

In the meantime, something like this will work.

DECLARE @BillingMonth CHAR(7) = '10/2013';

WITH SampleData (ID, BM) AS

(

SELECT 1, '10/2013' UNION ALL SELECT 2, '11/2013' UNION ALL SELECT 3, '09/2013'

UNION ALL SELECT 4, '01/2014'

)

SELECT *

FROM SampleData

WHERE CAST(STUFF(BM, 3, 1, '/01/') AS DATE) >= CAST(STUFF(@BillingMonth, 3, 1, '/01/') AS DATE)

But it does require that your DATEFORMAT be set to MDY.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Golden_eye (2/2/2014)
Hello,
I have a probllem with one part of my script which is:
where BillingMonth = '10/2013'
It is working fine now, but I need to change it to
where BillingMonth >= '10/2013'
and now it is not working, because BilingMonth is datatype char(7) and I can not use >=
Any idea/workaround how to do that?
Thanks

Yes. Whom ever designed the table really didn't do anyone any favors. Basically, they stored a formatted display value and that causes "Death by SQL". The BillingMonth column should be a DATETIME datatype and October of 2013 should be represented as 2013-10-01. Because of the poor design of that column, your code is going to end up doing a table scan or, at best, an index scan and that could slow things down quite a bit.

There are couple of ways to fix this but, let me guess, you're not allowed to change the column or even add a column to the table. That means that you're going to have to take the performance hit of having non-SARGable code.

WHERE RIGHT(BillingMonth,4)+RIGHT('00'+LEFT(BillingMonth,CHARINDEX('/',BillingMonth)-1),2) >= '201310'

If you want the literal to be exactly the way you have it, then there will be that little bit of hell to pay, as well.

--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: 1003863 More actions · Answer 3

Hmmmm... there IS a way to do this in a SARGable fashion (although fixing the column is still the best idea). I just need to know how Jan thru Sep are represented in the column that currently exists. For example, would Jan of 2013 be '01/2013' or '1/2013'?

--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)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

Jeff Moden (2/2/2014)
Hmmmm... there IS a way to do this in a SARGable fashion (although fixing the column is still the best idea). I just need to know how Jan thru Sep are represented in the column that currently exists. For example, would Jan of 2013 be '01/2013' or '1/2013'?

Computed, PERSISTED column with an INDEX?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Golden_Eye SSC-Addicted Points: 463 More actions · Answer 5

Hello,

Yes, it doesn't make sence for this fiedl to be char(7), but that is so in the source system. I guess I can contact the admin, but just in case this doesn't work, it is good to have a woraround. So, thanks a lot!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

dwain.c (2/2/2014)
Jeff Moden (2/2/2014)
Hmmmm... there IS a way to do this in a SARGable fashion (although fixing the column is still the best idea). I just need to know how Jan thru Sep are represented in the column that currently exists. For example, would Jan of 2013 be '01/2013' or '1/2013'?
Computed, PERSISTED column with an INDEX?

My first choice, of course, would be for them to fix the source data.

My second choice would be what you just outlined above.

Assuming that neither of those is possible because of a "sacred cow" table, generating a range of the CHAR(7) values based on the >= value would produce a SARGable solution.

--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)

Golden_Eye SSC-Addicted Points: 463 More actions · Answer 7

Jeff Moden (2/2/2014)
Hmmmm... there IS a way to do this in a SARGable fashion (although fixing the column is still the best idea). I just need to know how Jan thru Sep are represented in the column that currently exists. For example, would Jan of 2013 be '01/2013' or '1/2013'?

Jan is 01/2013 🙂

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

Golden_eye (2/3/2014)
Jeff Moden (2/2/2014)
Hmmmm... there IS a way to do this in a SARGable fashion (although fixing the column is still the best idea). I just need to know how Jan thru Sep are represented in the column that currently exists. For example, would Jan of 2013 be '01/2013' or '1/2013'?
Jan is 01/2013 🙂

Thanks. I give it a crack after work tonight. It could mean a substantial increase in performance depending on the size of your table. If a computed column could be added to the table, that would be even better.

--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)