DB Data Storage by Variable Type

[David]

Of interest are Date Type and String (Text) Type.

1)
Since the DB stores Dates as a double is it best to use
the default or go with a string in the format YYYYMMDDHHMMSS,
or some other way

When saving / seeking (finding) or whatever are there any advantages /
disadvantages to either storage method? Is there a better format?

2)

For strings (text) is -- All -- lower case, upper case, Title Case
offer any advantages or disadvantages again in regard to saving, seeking
(finding) etc.?

Thanks
David

 

[Dirk Goldgar]

Of interest are Date Type and String (Text) Type.

1)
Since the DB stores Dates as a double is it best to use
the default or go with a string in the format YYYYMMDDHHMMSS,
or some other way

When saving / seeking (finding) or whatever are there any advantages /
disadvantages to either storage method? Is there a better format?

Store date/time values in Date fields. Not only will it be more compact
than a string in the format you suggest, but in the normal way of things
you'll have fewer conversions to do between real date values and dates
encoded as strings.

2)

For strings (text) is -- All -- lower case, upper case, Title Case
offer any advantages or disadvantages again in regard to saving, seeking
(finding) etc.?

I don't believe it makes any difference as far as comparisons (seeking and
finding) are concerned, unless you need to enforce exact, same-case
comparisons. Forcing a binary comparison (identical case) instead of a text
comparison can add overhead that slows a query down a lot.

 

[Arvin Meyer [MVP]]

If you want to do date calculations, you need to use the Double datatype.
While you can convert a date string into a numeric value, it adds an extra
step and the corresponding overhead.

Windows doesn't care, when searching, what case you are using. Store strings
the way you expect to use them so that you do not need to use extra code to
render them the way you want.

 

[David]

Thanks Mr. Goldgar and Mr. Meyer

Helps clear up some "lingering" question as to best approach.

Have a nice evening.

David

 

[David W. Fenton]

I don't believe it makes any difference as far as comparisons
(seeking and finding) are concerned, unless you need to enforce
exact, same-case comparisons. Forcing a binary comparison
(identical case) instead of a text comparison can add overhead
that slows a query down a lot.

I you need to distinguish case, then you probably shouldn't be using
Access.

 

[David W. Fenton]

Store date/time values in Date fields. Not only will it be more
compact than a string in the format you suggest, but in the normal
way of things you'll have fewer conversions to do between real
date values and dates encoded as strings.

It also means you can do date math, and get all the benefit of the
fact that the date functions understand the calendar.

 

[Dirk Goldgar]

It also means you can do date math, and get all the benefit of the
fact that the date functions understand the calendar.

Yes, but you can do those things when your dates are stored as strings, so
long as you convert to a Date data type first, do your date math and use
your date functions, then convert from Date to string again on the way out.
Those are the sort of extra conversions I was talking about.