Decimal Date
Moderators: chulett, rschirm, roy
Decimal Date
Hi,
Input has dates in yyyymmdd or yyyymdd format like :
20100430
2009718
20101009
20090831
IF these dates have been read as decimal, how can we find maximum and minimum dates from this as well as reject the invalid dates out??
Input has dates in yyyymmdd or yyyymdd format like :
20100430
2009718
20101009
20090831
IF these dates have been read as decimal, how can we find maximum and minimum dates from this as well as reject the invalid dates out??
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Maximum and minimum functions in the Aggregator stage work happily with decimal data types, or you can use stage variables in a Transformer. In a Transformer you can use IsValid() functions to test whether the string (an implicit conversion) is valid as a date.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
No Ray,ray.wurlod wrote:Maximum and minimum functions in the Aggregator stage work happily with decimal data types, or you can use stage variables in a Transformer. In a Transformer you can use IsValid() functions to test w ...
dates has been read in as decimeal. So take example: 2009815, 20090109
now ideally second date is smaller than first one btu since it has been read as decimal, so simple comparison would give that first one is smaller.
and Isvalid()- how it would work when data hasn't been read as date?
Since the month can be m or mm you can't do a direct numeric or string comparison. I'd convert the number to a string using the following logice:
1. stage variable svTempStringDate = In.DecimalDate
2. stage variable svStringDate = IF LEN(svTempStringDate)=7 THEN svTempStringDate[1,4]:'0':svTempStringDate[5,3] ELSE svTempStringDate
Then you can do a direct string comparison on svStringDate or convert it to a date type.
1. stage variable svTempStringDate = In.DecimalDate
2. stage variable svStringDate = IF LEN(svTempStringDate)=7 THEN svTempStringDate[1,4]:'0':svTempStringDate[5,3] ELSE svTempStringDate
Then you can do a direct string comparison on svStringDate or convert it to a date type.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Although you can test it for yourself in a couple of minutes, the answer is that you can do string comparisons, e.g. "2010-01-23" < "2010-01-26". In your case the format is YYYYMMDD so a greater/less than comparison will work.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
But for that variable is to be defined as date right?ArndW wrote:Although you can test it for yourself in a couple of minutes, the answer is that you can do string comparisons, e.g. "2010-01-23" < "2010-01-26". In your case the format is YYYYMMDD so a greater/less than comparison will work.
what if variable is defined as char?
Sorry my installation has got some problem and our admin is trying to fix it so I can't test it now
You are confusing me. At first it was a decimal value, then string and now you are asking about date data types. Comparing date type against each other will correctly handle < and > values regardless of the display format.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Date and Timestamp columns have no format. Only when converting or displaying these data types is a format needed.
Internally these data types are stored so that comparisons on greater or lesser or equality are easily done.
What is the data type of your input columns?
Internally these data types are stored so that comparisons on greater or lesser or equality are easily done.
What is the data type of your input columns?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>