DATEFMT - what am I missing?

General discussion forum about all databases other than SqlBase.
claritee

DATEFMT - what am I missing?

Post by claritee » 31 Mar 2008, 13:28

All,

Apologies for the simple question, I must be missing something here.

I am trying to dump the contents of a table to a text file using interactive SQL. My problem is that I always get two digit years in the date columns. I understand the DATEFMT environment variable can be set to change the date output format. I am running this on a Unify database on SCO Unix. See the details of the session below...

$ SQL -version
UNIFY DataServer ELS 6.0AC SYS5LCK USSLCK M8BIT (international)

$ SQL
UNIFY TURBO/SQL
(c) 1981 - 1993, Gupta Technologies
All Rights Reserved

sql> !echo $DATEFMT
"MM/DD/YYYY"
sql>

I have tried setting the DATEFMT environment variable in a ksh to

"MM/DD/YYYY"
MM/DD/YYYY
YYYY-MM-DD

all to no avail. I have read in the documentation that user evironment changes can be prevented through the database or application level configuration files (.cf) but I do not see any .cf or .cfg files on the server related to Unify.

What am I missing here?

Thanks in advance,
Ken

caa

Re: DATEFMT - what am I missing?

Post by caa » 31 Mar 2008, 16:28

The default DATEFMT is MM/DD/YY, which is what I assume that you're seeing in output?

DATEFMT allows formatting of DATE and HUGE DATE columns. Are you sure that's what your column types are?

claritee

Re: DATEFMT - what am I missing?

Post by claritee » 01 Apr 2008, 14:17

Chris,

Thanks for your response.

Yes the date format I see in the query results is MM/DD/YY.

Yes according to the fields command I execute in SQL, for the column I am interested in I see

Ptnt_Birth_DT DATE 2

The database is a third party vendor system. I do not manage the database but have full query ability.

Any ideas or suggestions appreciated.

Thanks,
Ken

caa

Re: DATEFMT - what am I missing?

Post by caa » 01 Apr 2008, 16:44

This is a fairly old release, and it may only support DATETP and LDATEFMT (for long dates). Try using those.

claritee

Re: DATEFMT - what am I missing?

Post by claritee » 02 Apr 2008, 14:08

Thanks for the information. Now that I have read the correct documentation (Unify DataServer /ELS Developer Reference) here is the complete answer as I see it at this point.

Version 6 offers both DATE and LDATE database types.

From the documentation

DATE - This is a short date, with a display length of 8 characters. Its default format is MM/DD/YY, but you can set the DATETP environment variable to specify a different order for month, day, and year. A null value is stored as **/**/**.
Use a DATE data type for a date between 1 January, 1900 and 31 December 2077. This data type takes half the physical storage space of the LDATE data type.

LDATE - This is a long date, with a display length of 11 characters. Its default format is MM/DD/YY (DD/MMM/YYYY in PAINT to permit three-letter months). You can set the LDATEFMT environment variable to specify a different order for month, day, and year. You can also specify a different type of month (numeric or alphabetic) and year (2 or 4 characters). A null value is stored as **/**/**.
doco end

Use the LDATE data type for dates between October 1, 1752 and December 31, 9999. That is, use LDATE fields for any long-term events, like business plan data forecaster into the next century, or historical records that span centuries.

You use the DATETP for formatting DATE columns and the LDATEFMT for formatting LDATE columns.

The column I am interested has type DATE so I am limited to using the DATETP environment variable.

From the documentation

DATETP

The format in which to accept and display dates from 1 January 1900 to 31 December 2077. This environment variable can also be used to change all brackets that display on menus and screen forms to parentheses.
The default date format is MM/DD/YY, where M stands for month, D stands for day, and Y stands for year. The default format also displays brackets on menus and screen forms. The valid values for DATETP are the following:

AM American format, MM/DD/YY and brackets.
EU European format, DD/MM/YY and parentheses.
IN International format, YY/MM/DD and parentheses.
US United States format, MM/DD/YY and parentheses.

If you need to format long dates (four-digit year) from 1 October 1752 through 31 December 9999, use the LDATEFMT environment variable, also described in this section.
doco end

I am limited to two digit years at this point. One option would be to copy the key and date column to a new table where the date type is LDATE. I should then be able to use LDATEFMT to get a four digit year.

Too bad there does not appear to be any way to cast from DATE to LDATE in the SQL. If anyone knows how to do this please let me know.

Thanks,
Ken

caa

Re: DATEFMT - what am I missing?

Post by caa » 02 Apr 2008, 14:56

Ken,

DATEFMT was introduced in the very next release of DataServer/ELS. Is there any chance that you can upgrade?

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 5 guests