AUGForums.com

An Acumatica User Group

  • Forums
  • Podcast
  • Blog
  • Rolodex
  • Login
  • Start Here
  • Consulting
  • Courses
  • Register
Acumatica Forums

By using this website, you agree to our Terms of Use (click here)

  • Overview
  • Member List
  • All-Stars
  • Stats
  • Recent Posts
  • Help
Forums
AUG Forums
Acumatica Generic I...
Convert String to D...
 
Notifications
Clear all

Convert String to Date?  

    Last Post
RSS

Rob Cushen
Posts: 22
 Rob Cushen
September 13, 2020 7:53 am
(@robcushen)
21-50 Posts
Joined: 10 months ago

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!

3 Replies
Tim Rodman
Posts: 2488
 Tim Rodman     ★★ All-Star ★★
October 14, 2020 1:31 am
(@timrodman)
Over 200 Posts
Joined: 5 years ago

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".

Reply
Wyatt.ERP
Posts: 102
 Wyatt.ERP     ★★ All-Star ★★
October 16, 2020 4:39 pm
(@wyatt-erp)
101-200 Posts
Joined: 10 months ago

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.

Reply
Tim Rodman
Posts: 2488
 Tim Rodman     ★★ All-Star ★★
December 30, 2020 11:47 pm
(@timrodman)
Over 200 Posts
Joined: 5 years ago

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])$
image

 

You can see that it catches the bad 20201041 date:

image

 

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:

image
Reply
  All forum topics
  Previous Topic
Next Topic  
  Forum Statistics
11 Forums
1,656 Topics
7,693 Posts
3 Online
1,110 Members

Latest Post: Timecard reports/queries Our newest member: Janetteraab Recent Posts Unread Posts

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

Online Members

Acumatica Forums

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2021 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.

‹›×

    ‹›×