助け合いフォーラム
Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26808
問題を開く
現在の日付は2012年06月23日です。
1999年4月1日から現在までの日数を求めるには、どのSQL文を実行しますか(該当するものをすべて選択してください)。
ただし、実行環境は英語環境とし、デフォルトの日付の表示形式は「RR-MM-DD」とします。
1999年4月1日から現在までの日数を求めるには、どのSQL文を実行しますか(該当するものをすべて選択してください)。
ただし、実行環境は英語環境とし、デフォルトの日付の表示形式は「RR-MM-DD」とします。
正解
SELECT SYSDATE - TO_DATE('99-04-01', 'RR-MM-DD') FROM dual;
SELECT SYSDATE - TO_DATE('1999-Apr-01', 'YYYY-Mon-DD') FROM dual;
解説
日付値から日付値を減算すると、2つの日付間の日数を求めることができます。
設問では「1999年4月1日」から現在までに経過した日数を求めるので、2つの日付の日付値を取得し、減算を行います。
現在の日付の日付値はSYSDATE関数で取得することができ、「1999年4月1日」の日付値はTO_DATE関数を使用して日付値を取得します。
以上より、
・SELECT SYSDATE - TO_DATE('99-04-01', 'RR-MM-DD') FROM dual;
・SELECT SYSDATE - TO_DATE('1999-Apr-01', 'YYYY-Mon-DD') FROM dual;
が正解となります。
正解のSQL文の実行結果は次のようになります。
※日本語環境の場合は事前に以下のSQL文を実行して、セッションを英語環境で日付の表示形式を「RR-MM-DD」に変更して下さい。
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_DATE_FORMAT = 'RR-MM-DD';
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20247/k49199.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=4a73e1d92d2c6a8f0a35a549d1f054f4e295d1520c188da5e5af84618ab56e8c)
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20248/kk49199.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=edb4586e5c684812d2f0218a1d62af3e27e013552da4de631242c11dba5b9280)
その他の選択肢については次のとおりです。
・SELECT TO_CHAR(SYSDATE) - '99-04-01' FROM dual;
文字列同士の演算はできませんので、エラーとなります。
・SELECT SYSDATE - TO_DATE('99-04-01', 'YY-MM-DD') FROM dual;
日付書式の「YY」を指定すると、現在の世紀の下2桁の年のを返すので、TO_DATE関数の結果「2099年4月1日」に変換されます。
・SELECT SYSDATE - TO_DATE('01-Apr-99') FROM dual;
TO_DATE関数の日付書式が省略された場合は、デフォルトの日付書式が使用されます。設問では、デフォルトの日付書式は「RR-MM-DD」ですので、文字列と日付書式が一致せず、エラーとなります。
設問では「1999年4月1日」から現在までに経過した日数を求めるので、2つの日付の日付値を取得し、減算を行います。
現在の日付の日付値はSYSDATE関数で取得することができ、「1999年4月1日」の日付値はTO_DATE関数を使用して日付値を取得します。
以上より、
・SELECT SYSDATE - TO_DATE('99-04-01', 'RR-MM-DD') FROM dual;
・SELECT SYSDATE - TO_DATE('1999-Apr-01', 'YYYY-Mon-DD') FROM dual;
が正解となります。
正解のSQL文の実行結果は次のようになります。
※日本語環境の場合は事前に以下のSQL文を実行して、セッションを英語環境で日付の表示形式を「RR-MM-DD」に変更して下さい。
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_DATE_FORMAT = 'RR-MM-DD';
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20247/k49199.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=4a73e1d92d2c6a8f0a35a549d1f054f4e295d1520c188da5e5af84618ab56e8c)
SQLを表示
SELECT SYSDATE - TO_DATE('99-04-01', 'RR-MM-DD')
FROM dual;
FROM dual;
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/20248/kk49199.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=edb4586e5c684812d2f0218a1d62af3e27e013552da4de631242c11dba5b9280)
SQLを表示
SELECT SYSDATE - TO_DATE('1999-Apr-01', 'YYYY-Mon-DD')
FROM dual;
FROM dual;
その他の選択肢については次のとおりです。
・SELECT TO_CHAR(SYSDATE) - '99-04-01' FROM dual;
文字列同士の演算はできませんので、エラーとなります。
・SELECT SYSDATE - TO_DATE('99-04-01', 'YY-MM-DD') FROM dual;
日付書式の「YY」を指定すると、現在の世紀の下2桁の年のを返すので、TO_DATE関数の結果「2099年4月1日」に変換されます。
・SELECT SYSDATE - TO_DATE('01-Apr-99') FROM dual;
TO_DATE関数の日付書式が省略された場合は、デフォルトの日付書式が使用されます。設問では、デフォルトの日付書式は「RR-MM-DD」ですので、文字列と日付書式が一致せず、エラーとなります。
参考
TO_DATE関数は、文字列を指定された書式に従って日付値に変換する関数です。
書式は以下の通りです。
TO_DATE(文字列 [, '日付書式'] [, NLSパラメータ])
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19482/kk48939.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=72fb90a2d977eadfad57c96efa2dea033de0c8cd638b7e9a0e24705b8f437499)
日付書式は文字列を日付値に変換する際のフォーマットです。日付書式に使用できる主な要素は次のとおりです。
![<img src="/mondai3/img/jpg/kkkk48938.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19472/kkkk48938.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=feb51afa00ed89177ecfd8f795fbbcacfc90dc63a766c779e6d8b560f2ba5c99)
日付書式には上記の要素の他に「-」,「/」,「.」,「:」などの半角記号を含めることができ、それらはそのまま表示されます。
アルファベットやひらがな、漢字などを日付書式に含めたい場合は二重引用符(")で囲んで指定します。二重引用符(")で囲んでいない場合はエラーとなります。
![【図を表示6】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19483/kkkkkk48939.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=73528bb6acc8ec80ca78b3b6ec307d910482a5651d0a3cd5ecad63e73a87d5c1)
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19475/k48939.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=5bacfb349470acfe0a0c914c486ba489dfd5cb0cd0fcff8d8429a551b2b8269c)
なお、日付書式が省略された場合は、セッションのデフォルトの日付書式が使用されます。NLSパラメータが省略された場合も、セッションのデフォルト値が使用されます。
[日付書式要素 YY と RR の違い]
日付書式の要素である「YY」と「RR」はどちらも年の下2桁を表しますが、世紀の取扱に違いがあります。
![<img src="/mondai3/img/jpg/kk49198.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19479/kk49198.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=87d1e6ba2b017ec144c7f3f7961f95584382c9d02957d960f194f7b7d48b5f3b)
現在の年が2012年の場合に、日付書式を「YY」、「RR」、値を15、75とすると、それぞれ次のようになります。
![<img src="/mondai3/img/jpg/kkkk49198.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19480/kkkk49198.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=b10f5eafe58c8789ff224bdfee188a39537b831aff39f310fb65880468c6b626)
![<img src="/mondai3/img/jpg/kkk49198.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19481/kkk49198.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=fb71bf3de302b525bfa3ed7388e2e6ad032b0cb7d8512c063d744f798666a992)
書式は以下の通りです。
TO_DATE(文字列 [, '日付書式'] [, NLSパラメータ])
![【図を表示2】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19482/kk48939.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=72fb90a2d977eadfad57c96efa2dea033de0c8cd638b7e9a0e24705b8f437499)
SQLを表示
SELECT TO_DATE('2012-5-21', 'YYYY-MM-DD')
FROM dual;
FROM dual;
日付書式は文字列を日付値に変換する際のフォーマットです。日付書式に使用できる主な要素は次のとおりです。
![<img src="/mondai3/img/jpg/kkkk48938.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19472/kkkk48938.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=feb51afa00ed89177ecfd8f795fbbcacfc90dc63a766c779e6d8b560f2ba5c99)
日付書式には上記の要素の他に「-」,「/」,「.」,「:」などの半角記号を含めることができ、それらはそのまま表示されます。
アルファベットやひらがな、漢字などを日付書式に含めたい場合は二重引用符(")で囲んで指定します。二重引用符(")で囲んでいない場合はエラーとなります。
![【図を表示6】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19483/kkkkkk48939.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=73528bb6acc8ec80ca78b3b6ec307d910482a5651d0a3cd5ecad63e73a87d5c1)
SQLを表示
SELECT TO_DATE('2012年5月21日', 'YYYY"年"MM"月"DD"日"')
FROM dual;
FROM dual;
![【図を表示】](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19475/k48939.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=5bacfb349470acfe0a0c914c486ba489dfd5cb0cd0fcff8d8429a551b2b8269c)
SQLを表示
SELECT TO_DATE('2012年5月21日', 'YYYY年MM月DD日')
FROM dual;
FROM dual;
なお、日付書式が省略された場合は、セッションのデフォルトの日付書式が使用されます。NLSパラメータが省略された場合も、セッションのデフォルト値が使用されます。
[日付書式要素 YY と RR の違い]
日付書式の要素である「YY」と「RR」はどちらも年の下2桁を表しますが、世紀の取扱に違いがあります。
![<img src="/mondai3/img/jpg/kk49198.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19479/kk49198.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=87d1e6ba2b017ec144c7f3f7961f95584382c9d02957d960f194f7b7d48b5f3b)
現在の年が2012年の場合に、日付書式を「YY」、「RR」、値を15、75とすると、それぞれ次のようになります。
![<img src="/mondai3/img/jpg/kkkk49198.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19480/kkkk49198.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=b10f5eafe58c8789ff224bdfee188a39537b831aff39f310fb65880468c6b626)
SQLを表示
SELECT TO_CHAR(TO_DATE('15/12/31', 'YY/MM/DD'), 'YYYY/MM/DD')
FROM dual;
SELECT TO_CHAR(TO_DATE('75/12/31', 'YY/MM/DD'), 'YYYY/MM/DD')
FROM dual;
FROM dual;
SELECT TO_CHAR(TO_DATE('75/12/31', 'YY/MM/DD'), 'YYYY/MM/DD')
FROM dual;
![<img src="/mondai3/img/jpg/kkk49198.jpg">](https://ping-t-production-strg.s3.ap-northeast-1.amazonaws.com/uploads/question_image/file/19481/kkk49198.jpg?X-Amz-Expires=600&X-Amz-Date=20240727T003545Z&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEAcaDmFwLW5vcnRoZWFzdC0xIkgwRgIhAJ7mkC5JfvSdLioYGKPvL4LNMvPwvSDw5e6Trr2wGIteAiEA3PTHM4A2nrwe%2FsKtasucFN4M2bX8SdlW8y7dOzQ7puAqsQQI4P%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MjM0Mjk4MjcyNzUiDCdr3QPeyekWGLgFlSqFBKpCSz3SbveHUOGK2zeHLuVO2cItM42HqGJgxnLQS8wor%2BLtL3EVqtpbI8I26UG8TqsQ8ROdervHWNtEF2b0YDJeJ9OA1lTqIYRGfXT%2F0vga3BjTreZymRtCJxCBhuiBlLx6LQiOAsHh%2FqccRNGloBAcTGOzTHzFySnilRiwHeUfF7A5ENm3WS77BOMgNIZfQ%2BqOl8DiCoR%2F4pFqTREpbE2newnXyJLC2naNMvWg1VqNXBmsUkeGPKModBV7IXhjp1KuIo9uTCVLHTNNGeQ4QqJXaMCzwY2TCu7f5A%2B2AI9RzVu2xa8dHndhkOWtL3p4ayYMuYoNaeoGWRjuvKAfvdYlpLmHd%2BI8qb%2FC3r%2B5QEGEwE6pzDvX9RBseNeDx8xV0oY3WH47HS2TwZrFSsSIXfbkWRUtqnwGPsh6HHGcZ%2BttEmkB0id8owV%2F%2BkDcKcBkVSnEZ14UIczdWOipIytNI88HHsMahQhW0FL%2FV8DANDrF%2B0iLCjVNlTQpqDd0TBecXiGaUeXwyxJJtZBty4IzeiiGodEmdZvMXRG5J8BWMG5rjxSe2gTe%2BZWtFkw9B0DCFxZM%2Bz2LeV%2BDRXD0Ztn4Rjqo4pKn1IJTo2XPksV3q%2FS25FfXPCV%2BgYiqfEWMhZEBnMMp%2FlJQQ9O5lQWu%2FO3Rpbv0WmlwCYOqfaxafD26T5iJNCIiFkcw0N6QtQY6pQHbqvxSzEhMcFvQGQ8HD8a0AHu3DdZP%2FFuA131Jli24oxUqgn74dE0s2tjZNl10mAmXJ5spI8Xq5uL5E8yYKCD3dkwfq5sjHkAWdViw1VYA64tvvmrYdURs3iermXNq44RnmgovPmGbpOgB4SNjrt6dNp7tl00mC8JEZu%2BNsrX97GRXuPviHi2WTlfk2%2FmfdOlDwvz8NsaG50T63jElW1sev%2BBZ0UU%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAZCJ2QHLF74BLTX7P%2F20240727%2Fap-northeast-1%2Fs3%2Faws4_request&X-Amz-SignedHeaders=host&X-Amz-Signature=fb71bf3de302b525bfa3ed7388e2e6ad032b0cb7d8512c063d744f798666a992)
SQLを表示
SELECT TO_CHAR(TO_DATE('15/12/31', 'RR/MM/DD'), 'YYYY/MM/DD')
FROM dual;
SELECT TO_CHAR(TO_DATE('75/12/31', 'RR/MM/DD'), 'YYYY/MM/DD')
FROM dual;
FROM dual;
SELECT TO_CHAR(TO_DATE('75/12/31', 'RR/MM/DD'), 'YYYY/MM/DD')
FROM dual;
日付書式が違うのに、なぜ減算できる
y
yuchunsu
公開日 2023/04/19
デフォルトの日付の表示形式は「RR-MM-DD」なので、現在の日付の日付値はSYSDATE関数も「RR-MM-DD」で取得していると思いますが、
「YYYY-Mon-DD」との日付書式が違うのに、なぜ下記のSQL文が正解となりますでしょうか。
SELECT SYSDATE - TO_DATE('1999-Apr-01', 'YYYY-Mon-DD') FROM dual;
この投稿に対して返信しませんか?