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
DATEFMT - what am I missing?
Re: DATEFMT - what am I missing?
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?
DATEFMT allows formatting of DATE and HUGE DATE columns. Are you sure that's what your column types are?
Re: DATEFMT - what am I missing?
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
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
Re: DATEFMT - what am I missing?
This is a fairly old release, and it may only support DATETP and LDATEFMT (for long dates). Try using those.
Re: DATEFMT - what am I missing?
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
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
Re: DATEFMT - what am I missing?
Ken,
DATEFMT was introduced in the very next release of DataServer/ELS. Is there any chance that you can upgrade?
DATEFMT was introduced in the very next release of DataServer/ELS. Is there any chance that you can upgrade?
Who is online
Users browsing this forum: [Ccbot] and 0 guests