| View previous topic :: View next topic |
| Author |
Message |
THRIVIKRAM
Beginner
Joined: 03 Oct 2005
Posts: 70
Topics: 34
|
 Posted: Thu Feb 18, 2010 1:55 pm Post subject: Compare date field with Varchar |
 |
|
Hi,
I have a DB2 table where one of the colum is Text_Area (Datatype: Varchar). This Colum stores the date field(format: YYMMDD).I want to query this table to fetch all the rows greater than
a particular date.
Ex:
Select *
From TABLEA
where Text_Area > Current Date
Since Text_Area is a varchar field, I am not able to do the date comparision.
Any ideas please...
Thanks! |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Feb 18, 2010 2:06 pm Post subject: |
|
|
THRIVIKRAM,
How long is the varchar column defined? post a sample of the column data
Kolusu |
|
| Back to top |
|
|
THRIVIKRAM
Beginner
Joined: 03 Oct 2005
Posts: 70
Topics: 34
|
| Posted: Thu Feb 18, 2010 2:50 pm Post subject: |
|
|
Hi,
I got the solution. Below is the query I used:
(Test_Area is 40 bytes and last 6bytes is the date)
| Code: |
SELECT *
FROM TABLEA
WHERE
(SUBSTR(Test_Area,34,2) > '50'
AND '19'|| SUBSTR(Test_Area,34,6) <= '20100203'
OR SUBSTR(Test_Area,34,2) < '50'
AND '20'|| SUBSTR(Test_Area,34,6) <= '20100203')
|
Thanks!! |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Feb 18, 2010 6:25 pm Post subject: |
|
|
THRIVIKRAM,
I believe even this would give you the desired results
| Code: |
SELECT *
FROM TABLE
WHERE (CASE WHEN SUBSTR(TEST_AREA,34,2) > '50'
THEN CHAR('19') || SUBSTR(TEST_AREA,34,6)
ELSE CHAR('20') || SUBSTR(TEST_AREA,34,6) END) <= '20100203'
; |
Kolusu |
|
| Back to top |
|
|
THRIVIKRAM
Beginner
Joined: 03 Oct 2005
Posts: 70
Topics: 34
|
| Posted: Fri Feb 19, 2010 10:03 am Post subject: |
|
|
| Thanks Kolusu! |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
