Oracle-sql和Mysql的通用日期格式函数 [英] Common date format function for Oracle-sql and Mysql

问题描述

我需要编写MySQL和Oracle数据库的常见查询。当我必须放置日期条件时出现问题。
例如：在MySQL中有一个字段 Txn_date ，格式为'20150116' code> '16 -JAN-2015'在Oracle中。

我使用 date_format（now（） ，'％Y％m％d'）和 to_char（sysdate，'dd-MON-YYYY'） p>

有什么常见的功能，我可以在Oracle和MySQL中使用相同的功能？

我尝试 Txn_date in（date_format（now（），'％​​Y％m％d'）OR to_char（sysdate，'dd-MON-YYYY'））但没有工作因为 to_char（）在MySQL中无法识别。

解决方案

隐含转换时，MySQL日期通常具有以下格式 - 2015-01-16 - 而不是 20150116 。我认为你可以在MySQL和Oracle中进行以下操作（它是标准的SQL） - 我已经在Oracle（10g）中检查过它，它的工作原理似乎是在在我的MySQL中 ：

  SELECT * FROM mytable 
 WHERE mydate IN（DATE'2015-01-16'，DATE'2015-01-18'）; 
  

要转换为DATE的字符串字面值必须为 yyyy -mm-dd 。如果您的日期是日期，并且没有时间部分，那么这将会起作用。现在如果你的日期确实有一个时间部分，那么事情会变得更加困难，因为MySQL使用 DATE（）函数获取日期部分，而Oracle会使用 TRUNC（）。但是，您可以明智地使用> = 和 ，例如：

  SELECT * FROM mytable 
 WHERE（mydate> = DATE'2015-01-16'AND mydate< DATE'2015-01 -17'）
 OR（mydate> = DATE'2015-01-18'AND mydate< DATE'2015-01-19'）; 
  

现在，如果要使用 SYSDATE 最好的办法是使用ANSI标准 CURRENT_DATE 或 CURRENT_TIMESTAMP 。这些可以直接比较而不需要格式化，并且应该在MySQL和Oracle中工作。您也可以使用 INTERVAL 进行日期算术，在这种情况下，您可以尝试以下操作：

 code> SELECT * FROM mytable 
 WHERE mydate> CURRENT_DATE  -  INTERVAL'1'DAY; 
  

更新我一直在想这件事。如果您想要获取今天中输入的所有行，上面的查询将无法正常工作。难度在于Oracle将ANSI日期文字识别为日期（即没有时间部分），但是据我所知，没有一种ANSI标准的转换日期/时间的方式。 >值（其中Oracle DATE 是）到日期。也就是说，Oracle和MySQL都支持EXTRACT（）函数，所以您应该可以执行以下操作来获取 的记录：

  SELECT * FROM mytable 
 WHERE EXTRACT（YEAR FROM mydate）= EXTRACT（YEAR FROM CURRENT_DATE）
 AND EXTRACT（MONTH FROM mydate）= EXTRACT（MONTH FROM CURRENT_DATE ）
 AND EXTRACT（DAY FROM mydate）= EXTRACT（CURRENT_DATE DAY）; 
  

绝对笨拙，特别是如果有一个以上的日期考虑（我假设你自己做了'使用 IN 运算符），但应该在两个平台上工作。 请参阅这里的SQL Fiddle演示（MySQL）和 here（Oracle）。

I need to write the common queries for MySQL and Oracle databases. Problem occurs when I have to put date conditions. For example: there is one field Txn_date which is in format of '20150116' in MySQL and '16-JAN-2015' in Oracle.

I use date_format(now(),'%Y%m%d') for MySQL and to_char(sysdate,'dd-MON-YYYY') for Oracle.

Is there any common function of way by which I can use the same function in both Oracle and MySQL?

I tried Txn_date in ( date_format(now(),'%Y%m%d') OR to_char(sysdate,'dd-MON-YYYY') ) but did not work because to_char() not recognized in MySQL.

解决方案

First, in MySQL dates usually have the following format when converted implicitly - 2015-01-16 - rather than 20150116. I think you can do the following in both MySQL and Oracle (it is standard SQL) - I've checked it in Oracle (10g) and it works, and it seems to work in my fiddling with MySQL:

SELECT * FROM mytable
 WHERE mydate IN ( DATE '2015-01-16', DATE '2015-01-18' );

The string literal to be converted to DATE has to be of the form yyyy-mm-dd. Now this will work if your dates are dates and don't have a time portion. Now if your dates do have a time portion, then things become more difficult since MySQL uses the DATE() function to get the date portion, while Oracle would use TRUNC(). But you can get around that with judicious use of >= and <, e.g.:

SELECT * FROM mytable
 WHERE ( mydate >= DATE '2015-01-16' AND mydate < DATE '2015-01-17' )
    OR ( mydate >= DATE '2015-01-18' AND mydate < DATE '2015-01-19' );

Now if you want to use SYSDATE, the best thing to do would be to use the ANSI standard CURRENT_DATE or CURRENT_TIMESTAMP. These can be compared directly with no need for formatting and should work in both MySQL and Oracle. You can also do date arithmetic using INTERVAL, in which case you could try the following:

SELECT * FROM mytable
 WHERE mydate > CURRENT_DATE - INTERVAL '1' DAY;

UPDATE I've been doing some thinking about this. The query immediately above doesn't really work if you want to get all the rows that have been entered today. The difficulty is that Oracle recognizes ANSI date literals as dates (that is, with no time portion), but there isn't, as far as I know, an ANSI-standard way of converting a date/time value (which an Oracle DATE is) to a date. That said, both Oracle and MySQL support the EXTRACT() function, so you should be able to do the following to get today's records:

SELECT * FROM mytable
 WHERE EXTRACT(YEAR FROM mydate) = EXTRACT(YEAR FROM CURRENT_DATE)
   AND EXTRACT(MONTH FROM mydate) = EXTRACT(MONTH FROM CURRENT_DATE)
   AND EXTRACT(DAY FROM mydate) = EXTRACT(DAY FROM CURRENT_DATE);

Definitely unwieldy, especially if one has more than one date to consider (which I assume you do since you're using the IN operator), but should work on both platforms. See SQL Fiddle Demo here (MySQL) and here (Oracle).

这篇关于Oracle-sql和Mysql的通用日期格式函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！