英文:
How to get next available year from DD-MON date format and have it in DD-MM-YYYY format?
问题
如何将下一年的日期添加到以下日期格式中。例如，如果日期是七月，而今天是六月，那么结果将是2023年。但如果日期是一月，而今天是六月，那么结果将是2024年，因为一月已经过去。日期以非常奇怪的格式DD-MON表示，而我需要上传到的系统不接受这种格式，而是需要根据日期提供正确的年份。
Select '07-Jul-2023'
From sys.dual
Select '08-Oct-2024'
From sys.dual
我正在使用Oracle SQL。
英文:
How do I add the next available year to the following date format first. So for example if the date is July and today it is June it will be 2023. But if the date is January and today is June then it will be 2024 because January has already passed. The dates are in a very strange format of DD-MON and the system I need to upload to won't take this format and required the correct year depending on the date.
Select '07-Jul'
From sys.dual
Select '08-Oct'
From sys.dual
The desired result will be 07-07-2023 and 08-10-2024.
I am using Oracle SQL
答案1
得分: 5
使用 TO_DATE、CASE 表达式和 ADD_MONTHS：
SELECT CASE
WHEN TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English') >= SYSDATE
THEN TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English')
ELSE ADD_MONTHS(
TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English'),
12
)
END
FROM dual
如果您希望将输出作为格式化字符串（而不是作为 DATE，因为它是二进制数据类型且没有格式），那么可以将其包装在 TO_CHAR 中：
SELECT TO_CHAR(
CASE
WHEN TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English') >= SYSDATE
THEN TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English')
ELSE ADD_MONTHS(
TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English'),
12
)
END,
'DD-MM-YYYY'
)
FROM dual
英文:
Use TO_DATE, a CASE expression and ADD_MONTHS:
SELECT CASE
WHEN TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English') >= SYSDATE
THEN TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English')
ELSE ADD_MONTHS(
TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English'),
12
)
END
FROM dual
If you want the output as a formatted string (rather than as a DATE, which is a binary data-type and has no format) then you can wrap it in TO_CHAR:
SELECT TO_CHAR(
CASE
WHEN TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English') >= SYSDATE
THEN TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English')
ELSE ADD_MONTHS(
TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English'),
12
)
END,
'DD-MM-YYYY'
)
FROM dual
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论