Kbase 21674: SQL-92: How to INSERT and SELECT formatted DATE fields?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
03/03/2005 |
|
Status: Unverified
GOAL:
SQL-92: How to INSERT a date field which is not in the default Progress date format of yyyy-mm-dd?
FIX:
Use the SQL-92 TO_DATE function to store the date value of April 4, 1999 into the DateVar field of the TempDate table:
DROP TABLE TempDate;
CREATE TABLE TempDate (DateVar DATE);
INSERT INTO TempDate VALUES (TO_DATE('04-16-1999')) ;
DROP TABLE TempDate;
CREATE TABLE TempDate (DateVar DATE);
INSERT INTO TempDate VALUES( TO_DATE('1999-04-16')) ;
DROP TABLE TempDate;
CREATE TABLE TempDate (DateVar DATE);
INSERT INTO TempDate VALUES( TO_DATE('16-04-1999', 'DD-MM-YYYY')) ;
DROP TABLE TempDate;
CREATE TABLE TempDate (DateVar DATE);
INSERT INTO TempDate VALUES( TO_DATE('16.04.1999', 'DD.MM.YYYY')) ;
DROP TABLE TempDate;
CREATE TABLE TempDate (DateVar DATE);
INSERT INTO TempDate VALUES( TO_DATE('16hello!04hello!1999', 'DDhello!MMhello!YYYY'));
DROP TABLE TempDate;
CREATE TABLE TempDate (DateVar DATE);
INSERT INTO TempDate VALUES( TO_DATE('16/1999/04', 'DD/YYYY/MM')) ;
Use the SQL-92 TO_CHAR function to retrieve the above DateVar and display it in the specified format:
SELECT DateVar FROM TempDate;
Returns: 1999-04-16
The German ( Euro ) Date format:
SELECT TO_CHAR (DateVar, 'DD.MM.YYYY') FROM TempDate;
Returns: 16.04.1999
The "hello!" format:
SELECT TO_CHAR (DateVar, 'DDhello!MMhello!YYYY') FROM TempDate;
Returns: 16hello!04hello!1999
Some other date format:
SELECT TO_CHAR (DateVar, 'DD/MM/YYYY') FROM TempDate;
Returns: 16/04/1999