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)
(c) 1981 - 1993, Gupta Technologies
All Rights Reserved
sql> !echo $DATEFMT
I have tried setting the DATEFMT environment variable in a ksh to
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,
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.
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 **/**/**.
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
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.
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.
Who is online
Users browsing this forum: [Ccbot] and 5 guests