[AccessD] SQL Server - Time only in date field

On 1 Apr 2007 at 11:40, Gustav Brock wrote:

> Hi John and Stuart
> 
> It's easier than that; it behaves exactly like in Access (JET) except for
> two things:
> 
>  - SQL Server time is limited to real linear dates only, back to 1753-1-1,
>  where JET goes back to an artificial value of 100-1-1. - SQL Server
>  millisecond resolution is only 3.33 ms while JET goes down to 1 ms.
> 
> Thus, the date of numeric date value zero is the same for both: 1899-12-30.
> This can be easily shown if you format a date/time field from SQL Server to
> a string which always include the date:

Unfortunately, that's not  true. 

In Access Date 0 is 1899-12-30, but in SQL server it is 1900-1-1.

>From Transact SQL Help in BOL:
Values with the datetime data type are stored internally by Microsoft SQL 
Server as two 4-byte integers. The first 4 bytes store the number of days 
before or after the base date, January 1, 1900. The base date is the system 
reference date. Values for datetime earlier than January 1, 1753, are not 
permitted. The other 4 bytes store the time of day represented as the 
number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less 
precision than datetime. SQL Server stores smalldatetime values as two 2-
byte integers. The first 2 bytes store the number of days after January 1, 
1900. The other 2 bytes store the number of minutes since midnight. Dates 
range from January 1, 1900, through June 6, 2079, with accuracy to the 
minute.



-- 
Stuart