Date format yyyy

[Guest]

I have set the data type of a field to date/time and the format to yyyy. The
format box contains yyyy but the field will only accept dates in the format
dd mm yy.

I want only the year to be stored in this field eg “2005â€.

Do I have to set data type to number between 1900 and 9999 (if that is
possible)? Which would do the job but I’d rather do it the proper way.

Thanks,
Seth

 

[Ken Snell [MVP]]

To store just a year value, use a Number data type and set it to Integer
size.

 

[peregenem]

To store just a year value, use a Number data type and set it to Integer
size.

.... and use a CHECK constraint (Validation Rule) for the value range

CREATE TABLE Test
(year_nbr INTEGER NOT NULL,
CHECK (year_nbr BETWEEN 1900 AND 9999));

INSERT INTO Test VALUES (9999);
-- insert succeeds

INSERT INTO Test (year_nbr) VALUES (9999 + 1);
-- CHECK bites, insert fails

That's the right attitude. If you want data integrity (and who
doesn't?!) you need to ensure the database layer only accepts valid
data. Only then should you start writing implementation code in front
end applications.