将yyyy-mm-dd转换为mm/dd/yyyy [英] converting yyyy-mm-dd into mm/dd/yyyy

问题描述

我正在尝试将YYYY-MM-DD HH24:MM:SS转换为MM/DD/YYYY HH24:MM:SS

I'm trying to convert YYYY-MM-DD HH24:MM:SS INTO MM/DD/YYYY HH24:MM:SS

最后一次尝试是:

SQL> select to_date(to_date('2016-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS') from dual

我遇到了错误消息:无效的货币， 我对此感到非常困惑和厌倦，有人可以看看吗?

I got error which says :Invalid monhts, I'm very confused and tired of that, could anyone take a look ?

推荐答案

日期没有格式-它们在内部由 7表示-或8个字节.只有在客户端程序传递日期时，该客户端程序(可能)才为其指定格式.

Dates do not have a format - they are represented internally by 7- or 8- bytes. It is only when a client program is passed a date that that client program (potentially) gives it a format.

SQL/Plus或SQL Developer中日期的默认字符串格式由NLS_DATE_FORMAT会话参数设置.其他客户端通常将具有可为默认日期格式设置的参数(如果它们也未使用NLS设置).但是，请注意NLS_DATE_FORMAT是会话参数，因此它属于用户的会话，并且多个用户对于参数的每个值都可以具有与他们设置方式相对应的不同值.

The default string format for dates in SQL/Plus or SQL Developer is set by the NLS_DATE_FORMAT session parameter. Other clients will typically have parameters that you can set for the default date format (if they don't also use the NLS settings). However, beware that the NLS_DATE_FORMAT is a session parameter so it belongs to the user's session and multiple users can each have a different value for the parameter corresponding to how they have set it.

如果要给日期指定特定格式，则需要将其转换为字符串:

If you want to give a date a specific format then you will need to convert it to a string:

SELECT TO_CHAR( DATE '2016-05-01', 'MM/DD/YYYY HH24:MI:SS' )
FROM   DUAL;

为什么查询不起作用:

TO_DATE( value, frmt )需要一个字符串值和一个格式掩码.内部的TO_DATE('2016-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS')是完全有效的，并且将返回DATE '2016-05-01'.

TO_DATE( value, frmt ) expects a string value and a format mask. The inner TO_DATE('2016-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS') is perfectly valid and will return the DATE '2016-05-01'.

但是，外部的TO_DATE()然后会传递给您刚生成的DATE类型和字符串格式(而不是期望的两个字符串). Oracle将在该日期上隐式调用TO_CHAR()，并使用NLS_DATE_FORMAT会话参数作为格式掩码.这会从日期生成一个字符串，并且由于错误，NLS_DATE_FORMAT的值不是MM/DD/YYYY HH24:MI:SS，因此当外部TO_DATE()尝试解析隐式创建的字符串时，它将失败，因为它读取的第一个数字对于一个月.

However, the outer TO_DATE() is then being passed the DATE type you have just generated and the string format (instead of the two strings it is expecting). Oracle will implicitly call TO_CHAR() on the date and use the NLS_DATE_FORMAT session parameter as the format mask. This is generating a string from the date and, given the error, the value of NLS_DATE_FORMAT is not MM/DD/YYYY HH24:MI:SS so when the outer TO_DATE() tries to parse the implicitly created string it fails as the first number it reads is invalid for a month.

这篇关于将yyyy-mm-dd转换为mm/dd/yyyy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！