Oracle SQL将日期格式从DD-Mon-YY转换为YYYYMM [英] Oracle SQL convert date format from DD-Mon-YY to YYYYMM

问题描述

我可以比较2个表中的日期，但是问题是一个表的日期为DD-Mon-YY格式，而另一个表的日期为YYYYMM格式.

I have a to compare dates in 2 tables but the problem is that one table has the date in DD-Mon-YY format and the other in YYYYMM format.

为了比较，我需要将它们都设为YYYYMM.

I need to make both of them YYYYMM for the comparison.

我需要创建如下内容:

SELECT * FROM offers 
WHERE offer_date = (SELECT to_date(create_date, 'YYYYMM') FROM customers where id = '12345678') 
AND offer_rate > 0

其中create_date类似于2006年3月12日，offer_date类似于200605

where create_date is something like 12-Mar-2006 and offer_date is something like 200605

我需要在哪里修改此查询的任何想法?

Any ideas where I need to adapt this query??

推荐答案

由于offer_date是一个数字，并且比实际日期的准确性低，所以这可能行得通...
-将您的实际日期转换为格式为YYYYMM
的字符串 -将该值转换为INT
-将结果与您的offer_date

As offer_date is an number, and is of lower accuracy than your real dates, this may work...
- Convert your real date to a string of format YYYYMM
- Conver that value to an INT
- Compare the result you your offer_date

SELECT
  *
FROM
  offers
WHERE
    offer_date = (SELECT CAST(to_char(create_date, 'YYYYMM') AS INT) FROM customers where id = '12345678')
AND offer_rate > 0 

此外，通过对create_date进行所有操作，您只对一个值进行处理.

Also, by doing all the manipulation on the create_date you only do the processing on one value.

此外，如果您操作过offer_date，则将无法利用该字段上的任何索引，因此强制使用SCAN而不是SEEK.

Additionally, had you manipulated the offer_date you would not be able to utilise any index on that field, and so force SCANs instead of SEEKs.

这篇关于Oracle SQL将日期格式从DD-Mon-YY转换为YYYYMM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！