Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Tips and Tricks
Message Icon Topic: How to translate date codes into dates Post Reply Post New Topic
Author Message
dbodell
Groupie
Groupie
Avatar

Joined: 08 Nov 2011
Location: United States
Online Status: Offline
Posts: 53
Quote dbodell Replybullet Topic: How to translate date codes into dates
    Posted: 01 Feb 2012 at 3:29am
I run a lot of reports off of BOE scheduler. Most of them have to have date ranges entered. Instead of using specific dates and scheduling the reports multiple times, I like to use what I call interpreted date codes. These are codes like:
t = today
t-1 = yesterday
t+1=tomorrow
w = current week
m = current month
mb = beginning of current month
me = end of current month
mb-1 = beginning of previous month
mb+1 = beginning of next month
me-1 = end of previous month
etc.
 
That way I just enter a parameter date code and the report always interprets the code into the correct date.
 
The formula for this is:
 

// Accepts a date as a string in mm/dd/yyyy, yyyy-mm-dd, or

// relative (t-45) format. Returns a standard Crystal date.

 

local numberVar NRange;

local numberVar NOperator;

local stringVar SInterval;

local stringVar STemp;

local numberVar NPos;

 

if IsDate({?EndDate}) then Date({?EndDate})                     //If proper Crystal date was entered, don't change

else

(

    if InStr({?EndDate},"+")>0 then NOperator:=1                //Plus or minus?

    else NOperator:=-1;

 

    STemp:=Replace({?EndDate},"+","-");                         //Convert + to -, for easier string manipulation

 

    NPos:=Instr(STemp,"-");

 

    If NumericText(Mid(STemp,NPos+1)) then          //Extract and make sure valid number

        NRange:=toNumber((Mid(STemp,NPos+1)))

    else NRange:=0;

 

    if NPos>0 then

        SInterval:=Left(STemp,NPos)                    //Extract first part. Ex. "T" or "W"

    else SInterval:=STemp;

 

    if Left(SInterval,1)="T" or SInterval="TODAY" then          //Dates relative to Today

        CDate(DateAdd("d",(NOperator*NRange),DataDate))

 

    else if Left(SInterval,1)="W" or SInterval="WEEK" then      //Dates relative to this week

        CDate(DateAdd("ww",(NOperator*NRange),DataDate))

 

    else if Left(SInterval,2)="MB" then                         //Dates relative to beginning of this month

        CDate(DateAdd("m",(NOperator*NRange),

        DateValue(DatePart("yyyy",DataDate), DatePart("m",DataDate), 1)))

 

    else if Left(SInterval,2)="ME" then                         //Dates relative to end of this month

        DateSerial(year(DataDate), month(DataDate) + (NOperator*NRange) + 1, 1 - 1)

 

    else if Left(SInterval,1)="M" or SInterval="MONTH" then     //Dates relative to this month

        CDate(DateAdd("m",(NOperator*NRange),DataDate))

 

    else if Left(SInterval,2)="YB" then                         //Dates relative to beginning of this year

        CDate(DateAdd("yyyy",(NOperator*NRange),DateValue(DatePart("yyyy",DataDate), 1, 1)))

 

    else if Left(SInterval,2)="YE" then                         //Dates relative to end of this year

        CDate(DateAdd("yyyy",(NOperator*NRange),DateValue(DatePart("yyyy",DataDate), 12, 31)))

 

    else if Left(SInterval,1)="Y" or SInterval="YEAR" then      //Dates relative to this year

        CDate(DateAdd("yyyy",(NOperator*NRange),DataDate))

 

    else if Left(SInterval,1)="Q" or SInterval="QUARTER" then   //Dates relative to this quarter

        CDate(DateAdd("q",(NOperator*NRange),DataDate))

    else                                                        // Error trap

        DataDate

)

So you create parameters called EndDate  and StartDate making them strings. I usually enter in the list of basic values like the ones I have above.
Then you use the above code to create two formulas: InterpretedEndDate and InterpretedStartDate. 
 
You use the InterpretedStartDate and InterpretedEndDate to do your record selections.
 
I also have created a SQL function that does the same thing only this allows you to pass a date code to a SQL stored procedure and to have the stored procedure interpret the date code. If interested, I can send it to you.
Hope this helps someone.
 
Thanks,
D. Bodell
IP IP Logged
send2steph
Newbie
Newbie


Joined: 24 Mar 2015
Location: United States
Online Status: Offline
Posts: 1
Quote send2steph Replybullet Posted: 07 Apr 2015 at 11:33am
I developed QB and QE variations to add to this code today. Posting here for others to see.


    else if Left(SInterval,2)="QB" then                         //Dates relative to beginning of this quarter
         DateSerial(YEAR(DateAdd("m", (-(((MONTH(DataDate) mod 3) + 2) mod 3)+(3*(NOperator*NRange))), DataDate)),MONTH(DateAdd("m", (-(((MONTH(DataDate) mod 3) + 2) mod 3)+(3*(NOperator*NRange))), DataDate)),1)

    else if Left(SInterval,2)="QE" then                         //Dates relative to end of this quarter
         DateSerial(YEAR(DateAdd("m", (-(((MONTH(DataDate) mod 3) + 2) mod 3)+3+(3*(NOperator*NRange))), DataDate)),MONTH(DateAdd("m", (-(((MONTH(DataDate) mod 3) + 2) mod 3)+3+(3*(NOperator*NRange))), DataDate)),1)

IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.