Paul,

Thats a great question! hmmm some spreadsheets use it as the native
dateformat. It is also a smaller datatype field if you are tight on space.
You can get away with using a [int] data type for the next 33 or so years...
then you would need a bigint. Saves you about half the space of a datetime
field 12(19) vs. 24. vs 16 for a char(8), and you have higher granularity
witht he int field. If you have lots of dates stored...that can add up. Of
course you have the overhead of making them readable. You also have the
benefit of being able to do simple math to get the difference between 2
dates, which in many cases is used to calculate some other number. This
allows you to do the math without an additional call to a function. Another
plus is finding a point between the two dates. I personally don't use this
format, but I'm questioning why as I type my thoughts here :)

i.e. halfway between today and 2038 (when the integer field is obsolete),
you could simply do (2147483647-1114059600)/2+1114059600 to get the epoch
time for that day.

2147483647 max integer value
1114059600 seconds since 1/1/1970
result
1630771623 2021-09-04 16:07:03.000

select dateadd(s,(2147483647-1114059600)/2+1114059600,'1/1/1970')


Database Administrator
United States Liability Insurance Group
190 S. Warner Rd.
Wayne, Pa 19087-2191

1.888.523.5545 x191
Fax 610.688.4319
[Email address protected]

Alternate Contacts:
Kevin Howell (DBA)
[Email address protected]
x549

Ben Lowry
[Email address protected]
x309


Ability is what you're capable of doing. Motivation determines what you do.
Attitude determines how well you do it.
Lee Holtz



MS Sql Server LazyDBA home page