Best Way To Get A Charater Field using SQL

Question

Best Way To Get A Charater Field using SQL

JonJon

Say Hey Kid

Points: 676
More actions
July 7, 2009 at 2:16 pm

#72383

I am grabbing data from an INFORMIX database and putting a constraint on a field that is data type CHAR. The field represents a date of MMYY (0709). I just found out it was a character field.
example: select * from table where field >= '0709'
I would like to get all data that is greater than 0709 but because it is a data type CHAR I get all data of previous years also like 0799.
What can I do to get only the data after 0709 from a character field.
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

What you'd want to do is extract out the month and date using substring. Then you can cast this as a number, and sort appropriately.

So

Where cast( substring( field, 1, 2) as int) > 7 -- to get things after July

and cast( substring( field, 3, 2) as int) > 9 -- get years greater than 09

and cast( substring( field, 3, 2) as int) < 80 -- set some limit to handle the 2 digit year.

You'd have to play with the specifics you want, and it will be hard to figure this out with a 2 digit year. SQL Server has a setting to decide when a year is the 20th century and when the 21st for this very reason.

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

Provided that you will always have a 2 digit month and a 2 digit year, all you have to do is flip the MMYY to YYMM, add the DD part and do a normal date comparison. You might want to add a calculated column to the table to produce such dates so you don't need to constantly figure this out...

DECLARE @Date CHAR(4)

SELECT @Date = '0709'

SELECT CAST(RIGHT(@Date,2)+LEFT(@Date,2)+'01' AS DATETIME)

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