SQL Oracle – YY from date and add a character

I am asking your help about a join I have to do.

I have a field Date
as DD/MM/YYYY. I have to join it on an other table with a field with data like A14
, for year 2014 for example.

Do you know how can I get only the two last characters from field Date
, add to it the character A
, to to the join with my other table ?

Thanks for your help !

If your datefield is of date data-type, then

SELECT 'A' || TO_CHAR (DATEFIELD, 'YY') FROM MY_TABLE;

Example:

SELECT 'A' || TO_CHAR (SYSDATE, 'YY') FROM MY_TABLE;

But I assume from your example, DATE is a string in format DD/MM/YYYY You can probably try something like,

  1. Convert the String to Date and get the year alone:

    SELECT * FROM OTHER_TABLE WHERE FIELD = (SELECT 'A' || TO_CHAR (TO_DATE(DATEFIELD,'DD/MM/YYYY'), 'YY') FROM MY_TABLE;

  2. Just use string – substring function as below:

    SELECT * FROM OTHER_TABLE WHERE FIELD = (SELECT 'A' || substr (DATEFIELD, -2) FROM MY_TABLE;

For using join, You can do something like:

SELECT * FROM MY_TABLE, OTHER_TABLE
 WHERE OTHER_TABLE.FIELD = ('A' || SUBSTR(MY_TABLE.DATEFIELD, -2);

or

SELECT * FROM MY_TABLE JOIN OTHER_TABLE
 ON OTHER_TABLE.FIELD = ('A' || SUBSTR(MY_TABLE.DATEFIELD, -2);
Hello, buddy!责编内容来自:Hello, buddy! (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » SQL Oracle – YY from date and add a character

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录