Inserting date (mmyy) into table

[mmclancy]

I'm using Oracle's SQL Developer to do the following:

CREATE TABLE Account (
AccountID INT NOT NULL,
CreditID INT NOT NULL,
CardType VARCHAR(15) NOT NULL,
ExpiryDate DATE,
PRIMARY KEY (AccountID),
CONSTRAINT fk_credit_account FOREIGN KEY (CreditID) REFERENCES CreditCard
)
-- Table created
-- ExpiryDate not returning as mm/yy 
INSERT INTO Account (AccountID,CreditID,CardType,ExpiryDate);
VALUES (2001,1,'Visa',to_date('06/16','MM/YY'));

Have also tried:

INSERT INTO Account(AccountID,CreditID,CardType,ExpiryDate)
VALUES (2001,1,'Visa',to_date('0616','MM/YY'));

but both bring back a mmddyy result - 01/06/16, if I remember correctly.

What do I do to have only mmyy output?
I checked the Oracle documentation & check extensively online but could not find the answer.

Can anyone help?

Thanks in advance

[Rabbit]

You didn't check the documentation extensively enough. The to_date() function is to convert a string from the specified format to a date. A date will always have day month and year. And since the field you are inserting into is a date field, then that's what you want, a date with day month and year.

What you want is to store a date, which should have day month and year, but you want to see a different format. In which case you use a SELECT statement with a to_char() to convert the date into a string in the format you want to see it in. But you would never store it as a string.

[Luuk]

If you want this to be a MONTH, and not a date,
i would store this as a string

just because to_date('11/2012','MM/YY') does not specify which DAY should be used.
Storing this as date might lead to differences between the different days of that month.

[mmclancy]

Thank you, Rabbit. I'm new to sql, it's true I had seen TO_CHAR when I looked at the documentation but always preceded by SELECT instead of INSERT. I only now figured out how to make it all work!! I really appreciate you pointing me in the right direction.

[Rabbit]

Not a problem, good luck with your project.