如何在Oracle SQL中编写代码(例如将"CCYYMMDD"写入102)

问题描述

如何在Oracle SQL中编写代码(例如'CCYYMMDD'到102)?如果有人将在前端输入日期，则该值应返回为102而不是日期.为此，如何在Oracle PLSQL中编写函数.

https://www.unece.org/trade/untdid /d00a/tred/tred2379.htm 这些是ANSI标准格式.

解决方案

您可以验证字符串是否适合这样的日期格式:

create or replace function is_CCYYMMDD 
    (p_str in varchar2)
    return boolean
is
    rv boolean;
    dt date;
begin
    begin
        dt := to_date(p_str, 'YYYYMMDD');
        dt := to_date('2000' || substr(p_str, 5), 'YYYYMMDD');
        rv := true; 
    exception
        when others then
            rv := false;
    end;
    return rv;
end;

第一个to_date()仅检查整个字符串是否为有效日期.第二个to_date()检查字符串的后半部分是否是有效的月份和日期组合.这部分解决了 @MTO观察关于在某些字符串可以容纳多种格式时强制执行严格格式的问题. >

请注意，尽管存在歧义，例如，有可能通过该测试的有效日期是完全可能的. 'YYYYMMDD'或'DDMMYYYY'中的20111012是?除非您通过使用日期选择器小部件或年，月，日的单独输入框在前端输入中强制执行严格的日期格式设置，否则无法确定.

第二个检查将验证后四个字符在月和日中是否有效.每当我们进行这种测试时，我们都会遇到leap年的问题.如果仅应用to_date(str, 'MMDD')，Oracle会将年份默认为当年:即使20160229的原始输入有效，则2018029的错误也不是有效的日期.我的函数通过将year元素固定为2000年来避免这种情况，这是a年.

---

顺便说一句，如果要在SQL中使用此函数，则需要将返回数据类型更改为varchar2(Y/N标志)或数字(1/0)，因为SQL不支持Booolean.

How to write the code in Oracle SQL (like 'CCYYMMDD' into 102 )?If someone will enter the date in the frontend, the value should return as 102 instead of date.For that, how will write the function in Oracle PLSQL.

https://www.unece.org/trade/untdid/d00a/tred/tred2379.htmThese are the ANSI standard format.

解决方案

You can verify that a string fits a date format like this:

create or replace function is_CCYYMMDD 
    (p_str in varchar2)
    return boolean
is
    rv boolean;
    dt date;
begin
    begin
        dt := to_date(p_str, 'YYYYMMDD');
        dt := to_date('2000' || substr(p_str, 5), 'YYYYMMDD');
        rv := true; 
    exception
        when others then
            rv := false;
    end;
    return rv;
end;

The first to_date() just checks that the whole string is a valid date. The second to_date() checks that the second half of the string is a valid month and day combo. This partly addresses @MTO observations about the problems of enforcing a strict format when some strings can fit more than one format.

Note that it is perfectly possible to have valid dates which pass this test despite being ambiguous e.g. is 20111012 in 'YYYYMMDD' or 'DDMMYYYY'? There is no way to be sure unless you enforce strict date formatting in the front-end input by using a date picker widget or separate input boxes for year, month and day.

The second check verifies that the last four characters are valid as month and day. Whenever we do this sort of test we run into the problem of leap years. If we just apply to_date(str, 'MMDD') Oracle will default the year to the current year: the snag is 2018029 is not a valid date even though the original input of 20160229 is valid. My function avoids this by fixing the year element to 2000, which was a leap year.

---

Incidentally, if you want to use this function in SQL you'll need to change the return datatype to varchar2 (Y/N flag) or a number (1/0) because SQL doesn't support Booolean.

这篇关于如何在Oracle SQL中编写代码(例如将"CCYYMMDD"写入102)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！