Date format yyyy

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Seth - 21 Jul 2005 23:06 GMT

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

Reply to this Message

Ken Snell [MVP] - 22 Jul 2005 00:01 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

>I have set the data type of a field to date/time and the format to yyyy.
>The
[quoted text clipped - 9 lines]
> Thanks,
> Seth

Reply to this Message

peregenem@jetemail.net - 22 Jul 2005 08:41 GMT

> > I want only the year to be stored in this field eg "2005".
> >
[quoted text clipped - 3 lines]
> 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

> > I'd rather do it the proper way.

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.

Reply to this Message