Link to home
Start Free TrialLog in
Avatar of Becky Edwards
Becky EdwardsFlag for United States of America

asked on

Where do I get relative date list?

I have some of the relative dates such as t-1 = yesterday
w-1 = last week.  Does anyone know where there is a list of all these?
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

I think this page has a lot of what you're looking for.  
http://www.techonthenet.com/oracle/functions/index.php
Avatar of Becky Edwards

ASKER

Not that I could find.  I tried searching that page for "mb-1" - which is the first day of last month in relative terms.  "T-1" = yesterday, etc.
 
What database or programming language are you using?
I am creating filters in Crystal Reports, that users pick from.  I have attached the code that interprets whatever date the user fills in.
I want the user to be able to specify the start date as the first day of the current month.  
I did not create this code, and I do not understand it.  
There must be a way to say "start this report from the first day of the current month" but I do not know what it is.
Perhaps someone reading the code below can decipher what we are supposed to put in the start date field to get the first day of the current month.
Like I said = I know m-1 is 30 days ago.  mb-1 is the first day of the last month.  What is the first day of the current month?

// 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({?Interpreted Start Date}) then Date({?Interpreted Start Date})        //If proper Crystal date was entered, don't change
else 
(
    if InStr({?Interpreted Start Date},"+")>0 then NOperator:=1     //Plus or minus?
    else NOperator:=-1;

    STemp:=Replace({?Interpreted Start Date},"+","-");              //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
        DateAdd("d",(NOperator*NRange),CurrentDate)

    else if Left(SInterval,1)="W" or SInterval="WEEK" then      //Dates relative to this week
        DateAdd("ww",(NOperator*NRange),CurrentDate)

    else if Left(SInterval,2)="MB" then                         //Dates relative to beginning of this month
        DateAdd("m",(NOperator*NRange),
        DateValue(DatePart("yyyy",CurrentDate), DatePart("m",CurrentDate), 1))

    else if Left(SInterval,2)="ME" then                         //Dates relative to end of this month
        DateAdd("m",(NOperator*NRange),
        DateAdd('d',-1,DateAdd('m',1,DateValue(DatePart("yyyy",CurrentDate), DatePart("m",CurrentDate), 1))))

    else if Left(SInterval,1)="M" or SInterval="MONTH" then     //Dates relative to this month
        DateAdd("m",(NOperator*NRange),CurrentDate)

    else if Left(SInterval,2)="YB" then                         //Dates relative to beginning of this year
        DateAdd("yyyy",(NOperator*NRange),DateValue(DatePart("yyyy",CurrentDate), 1, 1))

    else if Left(SInterval,2)="YE" then                         //Dates relative to end of this year
        DateAdd("yyyy",(NOperator*NRange),DateValue(DatePart("yyyy",CurrentDate), 12, 31))

    else if Left(SInterval,1)="Y" or SInterval="YEAR" then      //Dates relative to this year
        DateAdd("yyyy",(NOperator*NRange),CurrentDate)

    else if Left(SInterval,1)="Q" or SInterval="QUARTER" then   //Dates relative to this quarter
        DateAdd("q",(NOperator*NRange),CurrentDate)

    else                                                        // Error trap
        CurrentDate
)

Open in new window

Still not exactly what I need.  According to that website, I would put in M for current month, but that doesn't work in this formula.
ASKER CERTIFIED SOLUTION
Avatar of Becky Edwards
Becky Edwards
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial