By using this website, you agree to our Terms of Use (click here)
Hi,
Have the following situation...
Lot numbers are currently entered in the format YYYYMMDD
I am trying to convert this value to a Date (i.e., 20200101 would represent Jan 1, 2020).
The following formula yields a very date-like looking string with all rows being returned.
=Concat(CStr(CInt(Substring( [INLotSerialStatus.LotSerialNbr], 5, 2))), '/',CStr(CInt(substring([INLotSerialStatus.LotSerialNbr], 7, 2))),'/',substring([INLotSerialStatus.LotSerialNbr], 1, 4) )
I tried to wrap the formula above in a CDate() function (which passes as a valid formula), but yields a "No records found as 'All records'" message.
Any thoughts on how to format the value as a "Date" and return data?
Thanks!
Seems like it should work. What about removing the CInt formulas since I think that strips off any leading zeros that the CDate formula might need?
Also, maybe try - instead of /
Finally, I'm not sure on this, but I think the SQL collation might have affect what it considers a "date".
Hi @robcushen,
=CDATE('20201015')
Try running this as a data field value in your results grid. I am getting a column with values of "10/15/2020 12:00:00 AM". Could you try just using CDate([INLotSerialStatus.LotSerialNbr])?
On a side note, you will get an error if you have Lot numbers that sneak in with any non-numerics, also if they are outside the min/max date range or if your month/day numbers aren't valid (Something like '20201041' will give you an error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value"). You should add good validation to your Lot number entry, cause I think it would be difficult for Acumatica to catch on the back end.
Taking the post from @wyatt-erp a step further, you could add a regular expression to the attribute.
Here's one that does a pretty good job of checking for dates:
^(19|20|21)\d\d(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])$
You can see that it catches the bad 20201041 date:
But there are some caveats:
1. It will only work from January 01, 1900 to December 31, 2199 (which will outlive any of us)
2. It doesn't will allow any month to go up to 31 days as you can see here when it allows me to enter February 31, 2020:
Online Members
