Becky Edwards
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?
w-1 = last week. Does anyone know where there is a list of all these?
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?
ASKER
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?
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
)
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.techonthenet.com/oracle/functions/index.php