Showing full date in Crystal Report

[former_member736866]

Hello everyone,

I have a Crystal Report that has a formula field to get the data from three fields. The order is if {AJ_D31.ALPHAVALUE} is blank then {PPJOBD.DATE_PROMISED} else currentDate.

{AJ_D31.ALPHAVALUE} is formatted in the sql server as Datetime(2022-04-26 00:00:00:000)

{PPJOBD.DATE_PROMISED} is formatted in the sql server as yyyymmdd(20220426)

Here is the Planned Ship Date code I have:

if (not IsNull({AJ_D31.ALPHAVALUE})) AND toText({AJ_D31.ALPHAVALUE}) <> "" then
        date(Trim(toText({AJ_D31.ALPHAVALUE}))) else 
If not IsNull({PPJOBD.DATE_PROMISED}) and                
({PPJOBD.DATE_PROMISED}) <> "" 
 then        
Date (Truncate(toNumber({PPJOBD.DATE_PROMISED})/10000),                    format
      Truncate(Remainder(toNumber({PPJOBD.DATE_PROMISED}),10000)/100),
      Remainder(toNumber({PPJOBD.DATE_PROMISED}),100)) else
currentDate

With this code its dropping the zero in date and causing another field that uses this field to get the yyyymm not work because it is pull the "/" and not the zero that should be there.

Here is the code for field yyyymmField

Right(toText({@Planned Ship Date}),4) + Left(toText({@Planned Ship Date}),2)

Example:

Date: 20220426

Display in the report: 4/26/2022

yyyymmField: 2022/4 (This is the error)

Can you help me add the leading zero to the fields that have one month digit?

[DellSC · Accepted solution]

Change your second formula to this:

Crystal Syntax:
ToText({@Planned Ship Date}, 'yyyy/MM')

Basic Syntax:
CStr({@Planned Ship Date}, 'yyyy/MM')

This should get you the format you're looking for.

-Dell