[AccessD] SQL Server - Time only in date field

Hi Stuart

Sorry for the confusion. I assumed that John is operating in Access where the numeric value will be converted by the driver to match that of SQL Server by shifting the value by -2. Thus:

  insert into dbo_timetable (timefield) values (0)

will insert the date 1899-12-30.

However, if John operates within SQL Server - for example by executing a pass-through query like:

  insert into dbo.timetable (timefield) values (0)

you are of course right - that date will be 1900-1-1.

/gustav

>>> stuart at lexacorp.com.pg 01-04-2007 12:03 >>>
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