Custom Date Format

[Windrider6]

I'm a new user, trying out Alpha Five version 9. My focus is "desktop" databases. I do not make database applications, or make databases to use on the Internet. I'm used to using Paradox, but because it is a dead end, I'm looking for a database program that is always in development.

I'll likely ask many questions, but I will do searches of the help files and this forum before asking them.

First question:

How do I define a custom date format (for use in forms, browses, reports, etc.)? I want YYYY-MMM-DD, for example, 2009-Apr-18.

In Paradox, and the spreadsheet in OpenOffice.org (Calc), defining a custom date format is simple and straightforward. AND then you can enter dates in the new format.

I understand that date entry in Alpha Five is severely limited, and that is one huge black mark against Alpha in my book. On my Vista computer, I have my system locale as English-United States, and have set YYYY-MMM-DD as my short date format, but Alpha only seems to allow date entry in the format DD-MM-YYYY, which I never use. Very frustrating.

----

I apologize for using an alias for my username. That is generally the custom in forums, but now I see that is not the case here. I don't see any way of changing my username, or setting an option to show my real name instead of my username. I suppose I could create a new user, but I would rather focus on figuring out how to use Alpha.

[MikeC]

Re: Custom Date Format

Bruce,
You can create whatever mask you want using the date/other functions (search for Date Mask or just Mask maybe in the forums also) or use what Alpha gives you within a form/browse for formats...I count 26 different ways there--see screenshot.

From the help file--
http://support.alphasoftware.com/alp..._Functions.htm
http://support.alphasoftware.com/alp...reating_a_Mask

[Windrider6]

Re: Custom Date Format

Bruce,
You can create whatever mask you want using the date/other functions (search for Date Mask or just Mask maybe in the forums also) or use what Alpha gives you within a form/browse for formats...I count 26 different ways there--see screenshot.

From the help file--
http://support.alphasoftware.com/alp..._Functions.htm
http://support.alphasoftware.com/alp...reating_a_Mask

[ATTACH]20782[/ATTACH]

Creating a mask does not appear to be useful, or work, for date fields. Also, that is only for data entry.

What I'm supposed to do with Date & Time functions, I don't know.

I just want to display the date in the format like 2009-Apr-19 in the date field in a form.

It's fairly simple in Paradox to set the display format for a date to be the short or long Windows format, or to create your own format. See attachment. That's all I'm asking for.

I'm not a stranger to programming and scripts, but it seems to me that something as straightforward as this shouldn't require extra effort.

[Tim Kiebert]

Re: Custom Date Format

We have been given the ability to make custom display formats a little while ago. See this item in the whats new in V9

As for entering a custom date format I am sure it would be possible to get there but may be more work than just entering it the way Alpha wants. You could add it to the wishlist forum.

[bvandyke]

Re: Custom Date Format

Define 2 calculated fields below.....then put the one below named "datewanted" on your form....works for me when i try it, if i'm understanding what you are wanting.


monthwanted = cmonth(date())
datewanted = year(date())+"-"+left(calc->monthwanted,3)+"-"+day(date())

[bvandyke]

Re: Custom Date Format

for some reason, the "datewanted" field above endned up different in the post...it should be as below:


monthwanted = cmonth(date())
datewanted = year(date())+"-"+left(calc->monthwanted,3)+"-"+day(date())

[bvandyke]

Re: Custom Date Format

too late at nite.......the way you will see if before calculation is as below, sorry for the confusion.

monthwanted = cmonth(date())
datewanted = year(date())+"-"+left(monthwanted,3)+"-"+day(date())

[Windrider6]

Re: Custom Date Format

I took a break from thinking about databases (partly because I got busy). Now I'm back, with a clearer head, but only to say goodbye.

If such a basic thing of formats for entering and displaying dates is too complicated to do, then I fear for the other issues that I'll inevitably have. I figure, "Why bother?"

The fact that this limitation on displaying and entering dates has existed for years, makes clear that the programmers care more about their coding, than they do about the plain desktop database user.

It appears to me that Alpha Five is designed for those who want to make their own custom database applications, not for a plain desktop database user.

I can continue to use Paradox for at least the lifetime of my new computer. After that, I may look around for another desktop database program, but I won't bother looking at Alpha again.

Good-bye.

[Tom Cone Jr]

Re: Custom Date Format

Drop back by and let us know when you find the perfect database. Til then, fare well and safe travels. -- tom

[SMARTII]

Re: Custom Date Format

duplic

[SMARTII]

Re: Custom Date Format

I really don't mind having to write my own functions. For this task of date format switching that has a special design need, I'd rather.
1. I remember them more easily since they are my "baby".
2. It's not dependent on how one sets a setting outside my control, like a windows setting that anyone can change without me realizing it.
So,
In my locale, the comfortable, intuitive entry and reading of dates by a user is mm/dd/yyyy or mm/dd/yy.
However, the delivery of that date to a 3rd party or sorting or report, etc. has to be something else such as yyyy-Mon-dd.

I make functions to do this and it converts (reformats or transforms) it either "onExit" from the field or some other convenient time.

The function is:
Y_Mon_Dy(your_date)
Or, in my case:
Y_Mon_Dy(DTE), which has a meaningful name for me.

FUNCTION Y_MON_DY AS C (DTE AS D ) 
Y_MON_DY = YEAR(DTE)+"-"+LEFT(CMONTH(DTE),3)+"-"+RIGHT(DTOS(DTE),2) 
END FUNCTION  

In the case of today being the date needed:
Y_MON_DY(date())

Yields: "2009-May-04"

For a specific date:
Y_MON_DY( {12/25/09} )
"2009-Dec-25"

It doesn't matter whether you input a date of mm/dd/yy or mm/dd/yyyy or m/d/yy
you get the desired result.
This is one of those external library functions that get used anywhere, anytime I want it, just like a native A5 function.

In summary:
Let the user enter a format that is quick, and comfortable and easily checked by sight.
Do the transforming where it is needed.
In my locale, making a user enter 2009-Jul-14 is somewhat cumbersome and even error prone compared to 07/14/09
with the slashes already put in for them by a mask, so they just type 071409

I might be able to think of some reason to overthrow A5, but this wouldn't be one of them.

[SMARTII]

Re: Custom Date Format

Define 2 calculated fields below.....then put the one below named "datewanted" on your form....works for me when i try it, if i'm understanding what you are wanting.


monthwanted = cmonth(date())
datewanted = year(date())+"-"+left(calc->monthwanted,3)+"-"+day(date())

This would give you a "single" character DAY from day 1 - day 9.
If he wanted a consistent 2-digit day, Change the end part of day(date()) to:
RIGHT(DTOS(DTE),2)

[jennygong]

Re: Custom Date Format

I have a tagNo field, which has value 000000000, and I used mask to set it as 000-000-0000, but when i go search this field, it will show no record....Please help with that. Thank you!

[amorris]

Re: Custom Date Format

Best to post a new thread and specify please how your search is? Like 1112223333 or 111-222-3333 and where did you apply the mask? field rules? form? etc? . if field rules, did you specify to save the "-" in the field of the table?

[Tom Henkel]

Re: Custom Date Format

I do hope Bruce checks back. Dates are store in the tables as yyymmdd. How a user wishes to display them is completely up to them.

The Date_format() built-in function will easily accomplish what he needs

?date_format(date(),"yyyy-Mon-0d")
= "2013-Sep-24"

Simple and elegant.

Alpha will do pretty much anything you ask of it. You just have to know what to ask...

TOm