Skip to main content
Solved

Format Date Field to MMDDYY in A Generic Inquiry


alexk95
Freshman II

Hello,

I am trying to format a date field on the results grid on a GI to be MMDDYY with no dashes or slashes I.e.

06/01/2022  should be formatted 060122, is there a way to convert with formatting or function?

Any help would be greatly appreciate it.

  

Best answer by alexk95

Naveen,

Thank you so much for your prompt reply! , just what I needed , it was almost there with the exeption that I needed something like this , for 06/15/2022  I needed to display 061522 so I modified the statement slightly , I am posting this so it might help someone else in the future.

New revised statement 

=Concat(IIf(Month([APPayment.DocDate]) < 10, '0'+CStr(Month([APPayment.DocDate])), CStr(Month([APPayment.DocDate]))) + IIf(Day([APPayment.DocDate]) < 10, '0'+CStr(Day([APPayment.DocDate])), CStr(Day([APPayment.DocDate]))) + Substring(CStr(Year([APPayment.DocDate])),3,2) )

It returns values as below ……

 

View original
Did this topic help you find an answer to your question?

5 replies

Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3381 replies
  • June 16, 2022

Hi @alexk95  We can do that with the below formula. You can replace the SOOrder.OrderDate with APPayment.DocDate.

 

= Concat( CStr(Month([SOOrder.OrderDate])) , CStr(Day( [SOOrder.OrderDate])) ,CStr(Year([SOOrder.OrderDate])))

 

 


alexk95
Freshman II
  • Author
  • Freshman II
  • 7 replies
  • Answer
  • June 16, 2022

Naveen,

Thank you so much for your prompt reply! , just what I needed , it was almost there with the exeption that I needed something like this , for 06/15/2022  I needed to display 061522 so I modified the statement slightly , I am posting this so it might help someone else in the future.

New revised statement 

=Concat(IIf(Month([APPayment.DocDate]) < 10, '0'+CStr(Month([APPayment.DocDate])), CStr(Month([APPayment.DocDate]))) + IIf(Day([APPayment.DocDate]) < 10, '0'+CStr(Day([APPayment.DocDate])), CStr(Day([APPayment.DocDate]))) + Substring(CStr(Year([APPayment.DocDate])),3,2) )

It returns values as below ……

 


Forum|alt.badge.img

Thanks for this.  I have to create the same thing for us.  Our difference is ours requires a / between the dates.  So mine is:

Returns:  05/04/2023

=Concat(IIf(Month([APPayment.DocDate]) < 10, '0'+CStr(Month([APPayment.DocDate])), CStr(Month([APPayment.DocDate]))) + '/' + IIf(Day([APPayment.DocDate]) < 10, '0'+CStr(Day([APPayment.DocDate])), CStr(Day([APPayment.DocDate]))) + '/' + CStr(Year([APPayment.DocDate]) ))

 


  • Freshman I
  • 1 reply
  • May 22, 2023

Here is another option using PadLeft. It simplifies the formula slightly.

=Concat(PadLeft(CStr(MONTH([APPayment.DocDate])), 2, '0'), PadLeft(CStr(DAY([APPayment.DocDate])), 2, '0'), Substring(CStr(Year([APPayment.DocDate])),3,2))

 

Breaking this down into the different parts:

=Concat(  -- Combines the various strings together

PadLeft(CStr(MONTH([APPayment.DocDate])), 2, '0'), -- MONTH: Extracts the month from DocDate, CSTR: Converts it to a string value, PADLEFT: Ensures it is 2 characters wide with ‘0’ placed at the left to accomplish this

PadLeft(CStr(DAY([APPayment.DocDate])), 2, '0'), -- DAY: Extracts the day from DocDate, CSTR: Converts it to a string value, PADLEFT: Ensures it is 2 characters wide with ‘0’ placed at the left to accomplish this

Substring(CStr(Year([APPayment.DocDate])),3,2) -- YEAR: Extracts the year from DocDate, CSTR: Converts it to a string value, SUBSTRING: Extracts the characters starting at position 3, 2 characters

)

 

For those looking to follow the ISO 8601 standard, here is a formula for that.

=Concat(PadLeft(CStr(YEAR([APPayment.DocDate])), 4, '0'), PadLeft(CStr(MONTH([APPayment.DocDate])), 2, '0'), PadLeft(CStr(DAY([APPayment.DocDate])), 2, '0'))


Forum|alt.badge.img+1
  • Semi-Pro I
  • 177 replies
  • October 31, 2024

I assume there is no way to not change the Data type to String?


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings