|
View previous topic :: View next topic
|
| Author |
Message |
candyboy
New User
Joined: 13 Nov 2006
Posts: 10
|
|
|
|
Hi,
I have a sql query where I have to use the following in the where clause:
A.EFFECTIVE_DATE = B.EFFECTIVE_DATE
A.EFFECTIVE_DATE has data type DATE while B. EFFECTIVE_DATE has data type DEC(8,0).
How can I use the above condition in my query.
Thanks
CB |
|
| Back to top |
|
 |
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| When the "effective date" is 1 July 1930, what will B.EFFECTIVE_DATE be? |
|
| Back to top |
|
|
candyboy
New User
Joined: 13 Nov 2006
Posts: 10
|
|
|
|
Sorry....here's the example:
A.EFFECTIVE_DATE = '2009-12-01'
B.EFFECTIVE_DATE = 20091201. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
candyboy,
you can cast from one datatype to another.
suggest you RTFM and perform some self-education with SPUFI. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
Well, that's a distinctly sub-optimal design 
In any case, this ought to do it:
| Code: |
WHERE A.EFFECTIVE_DATE =
DATE(SUBSTR(CHAR(B.EFFECTIVE_DATE),1,4) || '-' ||
SUBSTR(CHAR(B.EFFECTIVE_DATE),5,2) || '-' ||
SUBSTR(CHAR(B.EFFECTIVE_DATE),7,2))
|
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Akatsukami wrote: |
Well, that's a distinctly sub-optimal design
In any case, this ought to do it:
| Code: |
WHERE A.EFFECTIVE_DATE =
DATE(SUBSTR(CHAR(B.EFFECTIVE_DATE),1,4) || '-' ||
SUBSTR(CHAR(B.EFFECTIVE_DATE),5,2) || '-' ||
SUBSTR(CHAR(B.EFFECTIVE_DATE),7,2))
|
|
or
| Code: |
b.effective_date = year(a.effective-date) * 10000
+ month(a.effective_date) * 100
+ day(a.effective_date) |
depending on which accesspath you want |
|
| Back to top |
|
|
Mukesh Pandey
Active User
Joined: 11 Nov 2008
Posts: 143
Location: India
|
|
|
|
Please try below condition in your where clause if it works.
where
SUBSTR(A.EFFECTIVE_DATE,1,4) = SUBSTR(B.EFFECTIVE_DATE,1,4)
AND
SUBSTR(A.EFFECTIVE_DATE,6,2) = SUBSTR(B.EFFECTIVE_DATE,5,2)
AND
SUBSTR(A.EFFECTIVE_DATE,9,2) = SUBSTR(B.EFFECTIVE_DATE,7,2) |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
You can convert A.effective_date to the other format by using
REPLACE(CHAR(A.EFFECTIVE_DATE,ISO),'-','') |
|
| Back to top |
|
|
|
|
