[AccessD] SQL Server - Time only in date field

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:

? Format(<SQL Server DT-field>, "yyyy-mm-dd hh:nn:ss")

Official doc is here:

  http://msdn2.microsoft.com/en-us/library/ms141036.aspx 

which also could imply that the data type of DT_DBTIME would fit your purpose.

/gustav

>>> stuart at lexacorp.com.pg 01-04-2007 01:40 >>>
On 31 Mar 2007 at 14:33, JWColby wrote:

> Can SQL Server handle a date with just a time portion in the field?  IOW a
> date of 12:03 am without a date?

Yes. Does it just the way that Access does. 
It defaults to date 0 if no date part is specified. Date 0 is
January 1, 1900 

To retrieve the time as a string use Convert(char(8),myDate,8) for hh:mm:ss
or  Convert(char(12),myDate,14) if you want milliseconds.
(108 and114 return the same thing)

-- 
Stuart