Working with Dates

[WesOlfert]

I am having trouble getting the right output when I try to get the number of years calculated in a report. Rather than try to explain too much I have attached a small test database to ilustrate the problem. I have two questions (at the moment!)--
1. How can I get "Member Years" to remain blank when there the "Member Date" field in a particular record is blank. It is currently giving me "2005" as a result of the formula "age(Mbr_Date)" I tried some "if..." calculations but no success yet.

2. How can I get the Birthday to display only the month/day without the year, and to pick it up from the Birthdate field IF the Birthdate field is not blank, BUT also to allow me to enter it manually if the Birthdate field is blank. Reasopn: sometimes I have a birthday, but not the birthdate, and I would like to be able to record it.

[Stan Mathews]

RE: Working with Dates

Your attachment didn't make it, but in general

1. if you want to avoid a calculation when one of the two fields is empty

if(isblank("fieldname"),this,that)

where "this" and/or "that" can be values or expressions that return values of the same type

like

if(isblank("member date"),0,calculation for member years)

2. There is no format that displays part of a date, so you have to parse the character representation of the date.

? date()
= {01/24/2005}

? dtoc(date())
= "01/24/2005"

? left(dtoc(date()),5)
= "01/24"

[Steve Andrews]

RE: Working with Dates

?date1
= {01/24/2005}

?date_format(date1,"M:dd")
= "1:24"

?date_format(date1,"MM/dd")
= "01/24"

?date_format(date1,"MMdd")
= "0124"

[Robert Picard]

RE: Working with Dates

Hi Wes

To set a blank valued for a date field you need to use {} in your expression.

Robert

[Stan Mathews]

RE: Working with Dates

Thanks STeve. I was thinking of the options in the object properties format selection box, and even then you can select month and year.

I think we still missed the problem of wanting to store a "partial" date such as "12/21" meaning Dec 21st, year unknown. I believe Wes will have to have another field for storing this partial date as text, as even with the available formatting options, there must be a year involved in a date field.

I suppose one could default all unknown years to something like 9999 in order to filter them out when necessary, but a second field seems to be the better idea. One could then have a calculated field

if(isblank("datefield"),character field,dtoc(datefield))

[davej]

RE: Working with Dates

Stan,

You are corrent regarding your comments on parts of a date. Quite often I want to display a date in a format other than what Alpha offers 'out of the box'. There are functions that will grab parts of a date in numeric or chartacter format and allow you to display it however you want. You can pull the name of the month (either full name or partial by use of the left function) and the number of the date. Include or exclude the year as you wish. Also, you can use the following in your autoexec script and then have the date components available anywhere you want.

DIM GLOBAL vMon AS C
vMon = cmonth(date())
DIM GLOBAL vDay AS N
vDay = day(date())
DIM GLOBAL vDayname AS C
vDayname = cdow(date())
DIM GLOBAL vYr AS C
vYr = cyear(date())
DIM GLOBAL vtoday AS C
vtoday = Var->vDayname+" "cmonth(date())+" "+day(date())+", +cyear(date())  - NOTE: vtoday should be all on one line

You could also use the following as a calculated field whereever you want it.
Today = "Today is "+ltrim(cdow(System->Date))+" "+ltrim(cmonth(System->Date))+" "+day(System->Date)+", "+cyear(System->Date)
This yields something like 'Today is Monday January 24, 2005'

You cal play with these to get and format the date components however you want.
Dave

[WesOlfert]

RE: Working with Dates

THank you all for your ideas. I'll try some ot them and see how it goes as ASAP. Sorry about the attachment problem -- don't know hwy that happened. But you got the point anyway.

[WesOlfert]

RE: Working with Dates

Thanks for your input. Still having a problem --
Here's the calculation setup I tried but it is an invalid expression: if(isblank(Birthdate),0,age(Mbr_Date)

I am not sure why except that TYPES are not the same. Maybe that's the reason. If so, how do I get around that. I tried the following but it didn't help:
if(isblank(Birthdate),0,dtoc(age(Mbr_Date))

ALOS, I have tired to attach the database again. Hope it goes this time.

Thanks for the help from you and all the others.

[Stan Mathews]

RE: Working with Dates

if(isblank("fieldname"),this,that)

the quotes around fieldname are not to show that you substitute your fieldname, they are required by the isblank() function.

if(isblank("Birthdate"),0,age(Mbr_Date)

if birthdate is your fieldname

[WesOlfert]

RE: Working with Dates

Thanks again! Got it this time. Details, details, details!!
Now I'll work on the other issue -- question 2.

[WesOlfert]

RE: Working with Dates

You suggested --
? left(dtoc(date()),5)

That works great -- got date to show 5 digits in a calculated field using
left(dtoc(Birthdate),5)

Question -- Why, in your example, did you add the ? in front of each line?

Regading keeping a birthday without having a year -- I think I'll just do as you suggested and justuse an additonal field to keep things simpler -- but this raises another question:

-- Can you record dates without adding the zeros in front of single digit dates and still get a correct sort? That is, to get 12/5 to come out before 12/20. If I am going to manualy enter dates (just m/d -- no year) I would like to just enter them without using zeros and still get an correct order when sorting. Is that possible?

[martinwcole]

RE: Working with Dates

The question mark is used when you are in an "interactive" window

no, it won't sort correctly if you have single digit days


.