| determining last date entered using TO_DATE on VARCHAR(2) datatype [message #373637] |
Fri, 27 April 2001 15:18  |
Toyn
Messages: 36
Registered: April 2001
|
Member |
|
|
I'm trying to fashion a query that will provide the last date entered into a column that has been created with a datatype of VARCHAR(2). Here's what I've been able to come up with:
SELECT * FROM BICS_TEMP_ORDERS WHERE TRANS_CODE='O' AND
max(TO_DATE(DATE_FILED,'YYMMDD'));
The DATE_FILED column data type has been stored
varchar(2). (ex. 990325). If a procedure occurs in 2000, then naturally the query will not work unless the data type is defined as a date. When I use the 'max', I get 'group function not allowed here', and without it I get 'invalid relational operator'. Has anyone run into this before? Thanks.
|
|
|
|
| Re: determining last date entered using TO_DATE on VARCHAR(2) datatype [message #373638 is a reply to message #373637] |
Fri, 27 April 2001 16:04  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
See the difference between RR and YY.
SELECT TO_CHAR (TO_DATE ('990405', 'YYMMDD'), 'yyyy/mm/dd') FROM dual;
SELECT TO_CHAR (TO_DATE ('990405', 'RRMMDD'), 'yyyy/mm/dd') FROM dual;
SELECT TO_CHAR (TO_DATE ('000405', 'YYMMDD'), 'yyyy/mm/dd') FROM dual;
SELECT TO_CHAR (TO_DATE ('000405', 'RRMMDD'), 'yyyy/mm/dd') FROM dual;
SELECT *
FROM bics_temp_orders b1
WHERE b1.trans_code = 'O'
AND b1.date_filed =
(SELECT TO_CHAR (MAX (TO_DATE (b2.date_filed, 'RRMMDD')))
FROM bics_temp_orders b2
WHERE b2.trans_code = 'O');
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
