[AccessD] SQL Server - Time only in date field

In fact my question was more simple than that.  I have values in the date
fields that are #12:00:00 AM# for example.  Those values are NOT valid in
SQL Server, or at least will not upsize correctly.  I had to find and fix
all such values by adding some date to them to make them #1/1/1800 12:00:00
AM#.  Once I did that, the table with such fields would upsize.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Sunday, April 01, 2007 7:45 AM
To: accessd at databaseadvisors.com
Subject: Re: [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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com