ba_trainer
asked on
How do I format the dates when creating table?
How do I properly format the dates in the following?
Date_Added & Date_Modified
Thanks!
Beth
Date_Added & Date_Modified
Thanks!
Beth
CREATE TABLE CUSTOMER_SHIPPING_ADDRESS
( CUSTOMER_SHIP_ID (8,0) NOT NULL
, CUSTOMER_NO NUMBER(8,0) NOT NULL
, CUSTOMER_SHIP_TYPE CHAR(1)
, DATE_ADDED DATE(1)
, DATE_MODIFIED DATE(30)
, ADDRESS_1_SHIP VARCHAR2(50)
, ADDRESS_2_SHIP VARCHAR2(50)
, CITY_SHIP VARCHAR2(30)
, STATE_SHIP VARCHAR2(20)
, ZIPCODE_SHIP NUMBER(5,0)
, COUNTRY_SHIP VARCHAR2(30)
, CONSTRAINT CUSTOMER_SHIPPING_ADDRESS_PK PRIMARY KEY(CUSTOMER_SHIP_ID)
, CONSTRAINT CUST_SH_TYPE CHECK (CUSTOMER_SHIP_TYPE IN ('W', 'M', 'D', 'O'))
, CONSTRAINT CUSTOMER_SHIPPING_ADDRESS_FK FOREIGN KEY (CUSTOMER_NO)
, REFERENCES CUSTOMER(CUSTOMER_NO))
;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Normally you don't format dates in the data structure. It will store the date as the system needs it. Now what matters is what format / language your system is setup to handle as that is how it will interpret literal dates if not specified. You will want to format dates on output.
BTW, don't believe you need (1) or (30) with DATE data type. Don't believe that even does anything, but maybe I am wrong as I am more of a MS SQL Server guy myself.
BTW, don't believe you need (1) or (30) with DATE data type. Don't believe that even does anything, but maybe I am wrong as I am more of a MS SQL Server guy myself.
DATE type in Oracle has fixed internal format and length and format.
The designer uses the DATE or TIMESTAMP type to declare them.
Because the input of the DATE is in character string we use
TO_DATE conversion function like
TO_DATE('12-7-2008 14:15:55','mm-DD-yyyy hh24:MI:SS')
The designer uses the DATE or TIMESTAMP type to declare them.
Because the input of the DATE is in character string we use
TO_DATE conversion function like
TO_DATE('12-7-2008 14:15:55','mm-DD-yyyy hh24:MI:SS')
See attached.
comments.txt
comments.txt
http://infolab.stanford.ed
P.