Month/Year Variable

[Alan Dicenha]

Here's the problem, i need to create a date range for the variable below.

define variable s_date as character format "99/9999" no-undo.

There is a start_date column in my db (format "99/99/9999"(dd/mm/yyyy)) the date when a customer sales order was entered.

What i need to do is set the variable to create the date range, search and display the results of every sales order of the defined month and year.

PS: I 'm using a form , so the user can define the month for the variable.

 

[Cringer]

Not sure I completely understand. So they give a month and a year and you want to return all dates in that month? Does DATE(mm,dd,yyyy) help?

 

[Cringer]

To get the last day of the month by the way:

addinterval(01/01/2015,1,"month") - 1.

 

[Keith G.]

I've seen something similar, where an accounting period (which was a calendar month) was stored as a 6-digit number in the format YYYYMM. Or, to use a DATE data type, always use the first of the month for each stored value (MM/01/YYYY).

 

[KrisM]

What is the data type of this start_date db column ?
Is it also character ?

 

[rajendran]

function lastDay returns date ( input d as date ):
return add-interval( date( month( d ), 1, year( d )), 1, "month" ) - 1.
end.

def var v-startdate as date no-undo.
def var v-enddate as date no-undo.
def var ip_date as date no-undo.

set ip_date.

v-startdate = date(month(ip_date),01,year(ip_date)).

v-enddate = lastday(v-startdate).

display v-enddate.

You can use startdate and enddate in your query to get the date range of records of that particular month.

 

[Alan Dicenha]

Thanks guys, but this is what i want.

def var wDate         as char    format "99/9999" no-undo.
def var wStdate       like       start_date       no-undo.
def var wEndate      like       start_date       no-undo.

form
skip(1)
wDate      colon 15 label "Period (MM/YYYY)"
skip(1)

with frame a down.

assign wDate = string (month(today), "99") + string (year(today), "9999").

update
  wDate  validate (length (wDate) = 6 , "error: Invalid Date")
  with frame a no-error.

wStdate = date (int(substring(wDate,1,2)),1, int(substring(wdate, 3,4))).
wEndate = date (int(substring(wDate,1,2)) + 1,1,int(substring (wDate,3,4))) - 1.

.....