如何格式化yyddd格式的日期没有前导零-Howtoformatdatesinformatyydddhavingnoleadingzeros

2  

What does January 1, 2001 look like?

2001年1月1日是什么样的？

If it looks like "1001", you can pad on the left with zeros to 5 digits and then extract the 2-digit year as the first two digits and the day-of-year number as the last 3. It should then be a simple matter to convert the day-of-year number to a month and day; if nothing else you can do it with a bunch of if statements on day ranges.

如果它看起来像“1001”，你可以在左边用零填充到5位数，然后将2位数年份作为前两位数字提取，将年份数字作为最后3位数。然后应该是将日期数转换为月和日的简单事项;如果没有别的，你可以在日期范围内用一堆if语句来做。

If it looks like "11" because there are no leading zeros in the day number, you're just out of luck as there is no way to differentiate between many dates, such as January 1, 2001 and January 11, 2000.

如果它看起来像“11”，因为日期中没有前导零，那么你只是运气不好，因为没有办法区分许多日期，例如2001年1月1日和2000年1月11日。

P.S. These aren't Julian dates, they're a variation on ordinal dates.

附：这些不是朱利安日期，它们是序数日期的变化。

2  

IF your dates are always of the format 'yyddd':

如果您的日期始终采用'yyddd'格式：

If you can write your SQL statement directly, the following will work...

如果您可以直接编写SQL语句，以下内容将起作用...

SELECT CAST('20' || julianDate as date)
FROM table

If you don't, consider writing a view that incorporates that behaviour (that's one of the reasons views exist...).

如果不这样做，请考虑编写一个包含该行为的视图（这是视图存在的原因之一......）。

For obvious reasons, all dates will be considered year 2000 and later...

由于显而易见的原因，所有日期都将被视为2000年及以后......

IF (for whatever reason) it's removing any leading zeros in each part (as pointed out in comments for @Anomie's answer), you are indeed simply toast. Frankly, the entire dataset is probably a loss, as I'm not sure how even RPG would be able to differentiate between certain dates properly at that point.

如果（无论出于什么原因）它正在删除每个部分中的任何前导零（正如@ Anomie的答案中的评论所指出的那样），你确实只是敬酒。坦率地说，整个数据集可能是一个损失，因为我不确定RPG在这一点上是否能够正确区分某些日期。

2  

IBM defines *JUL date format as yy/ddd. It is not commonly used, but is is an available standard format supported on the AS/400. You say you have a string, so the assumption here is that it is stored as CHAR(5), or 5A in DDS.

IBM将* JUL日期格式定义为yy / ddd。它不常用，但是AS / 400支持可用的标准格式。你说你有一个字符串，所以这里的假设是它在DDS中存储为CHAR（5）或5A。

If you column is called jdt, get the right number of digits in your string, in SQL, with:

如果您的列名为jdt，请在SQL中使用以下内容在字符串中获取正确的位数：

  RIGHT(('00000' || TRIM(jdt)),5)

Now put the slash in:

现在把斜线放入：

  INSERT( RIGHT(('00000'||TRIM(jdt)),5) ,3,0,'/')

DB2/400 can cast this to a real date field, but it will only work properly if you can SET OPTION DATFMT=*JUL. How to do this from C# on Windows would depend on how you are connecting.

DB2 / 400可以将其转换为实际日期字段，但只有在SET OPTION DATFMT = * JUL时它才能正常工作。如何在Windows上使用C＃进行此操作取决于您的连接方式。

Let's suppose you can't find the way to set the date format on your connection.

假设您无法找到在连接上设置日期格式的方法。

Solution: Create a user defined function [UDF] in DB2.

解决方案：在DB2中创建用户定义的函数[UDF]。

First, choose an appropriate library to store the function in, and set that as your current library. In OS/400 CL, CHGCURLIB yourlib, or in SQL, SET SCHEMA = yourlib. Thus by default anything you create will go into this library.

首先，选择适当的库来存储函数，并将其设置为当前库。在OS / 400 CL，CHGCURLIB yourlib或SQL中，SET SCHEMA = yourlib。因此，默认情况下，您创建的任何内容都将进入此库。

I recommend storing the SQL definition for your UDF in a source member. (Ask if unfamiliar) You can execute the source with the RUNSQLSTM command.

我建议将UDF的SQL定义存储在源成员中。 （询问是否不熟悉）您可以使用RUNSQLSTM命令执行源。

Your function definition could look something like this:

您的函数定义可能如下所示：

CREATE FUNCTION  CvtJul2Date( jdtin char(5) ) RETURNS DATE            
        LANGUAGE SQL                                                  
        SPECIFIC CVTJUL2DT                                            
        DETERMINISTIC NO EXTERNAL ACTION                              
        CONTAINS SQL                                                  
        RETURNS NULL ON NULL INPUT                                    
        SET OPTION DATFMT = *JUL                                      
  BEGIN                                                               
    RETURN(                                                           
          date( insert( right(('00000'||trim(jdtin)),5) ,3,0,'/') )   
    );                                                                
  END                                                                 

The *JUL option is scoped to the UDF. Any SQL query that runs on the AS/400 should be able to do this conversion, regardless of the DATFMT of the job (assuming you have put this function in a library which is on that job's library list).

* JUL选项的作用域是UDF。无论作业的DATFMT如何（假设您已将此函数放在该作业的库列表中的库中），在AS / 400上运行的任何SQL查询都应该能够执行此转换。

1  

Oops... My bad. A method will still probably have to be written.

糟糕...我的坏。仍然可能必须编写一种方法。

Based on your description, an increase of 1 is a new day? Looks like you will have to do some math to calculate the date. Maybe create a function like

根据您的描述，增加1是新的一天？看起来你必须做一些数学来计算日期。也许创建一个像

public DateTime ConvertDate(int julianDate)
{

}

This is untested and may need some changes. But this would be my suggestion.

这是未经测试的，可能需要进行一些更改。但这是我的建议。