[AccessD] Updating DateTime Field IN SQL Server With Just The Time

DateTime and SmallDateTime data in SQL Server are stored  in two parts, the 
hi bytes store the date as an offset from '1/1/1900" and the low bytes 
store the time as a number of seconds or milliseconds after midnight.  

Unfortunately this differs from Access which uses "31-12-1899" as Day 0.  
So, if you try to store "08:30" through an Access recordset or it directly 
into a linked table, Access stores you "time only" as "31-12-1899 08:30".
If you do it as through an SP, SQL Server stores it as "1-1-1900 08:30"

When working between Access and SQL Server, you need take this one day 
difference into account if you are manipulating just the time parts of the 
data, either by adding/subtracting 1 from the data or more generally, 
taking only the fractional part of the data when it is Read.

On 18 Oct 2006 at 16:00, paul.hartland at fsmail.net wrote:

> Gustav,
> 
> Here's a test one I have been playing with, can't get more simple
> really....Even tried using CONVERT in the stored procedure and Format from
> Visual Basic 6.0 CREATE PROCEDURE [insert_timetable_1]
>  (@TestTime_1  [datetime])
> AS INSERT INTO [Genesis].[dbo].[timetable] 
>   ( [TestTime]) 
> 
> VALUES 
>  ( @TestTime_1)
> GO
> 
> The date is always 01/01/1900
> 
> Paul
> 
> 
> 
> 
> Message Received: Oct 18 2006, 01:44 PM
> From: "Gustav Brock" 
> To: accessd at databaseadvisors.com
> Cc: 
> Subject: Re: [AccessD] Updating DateTime Field IN SQL Server With Just The
> Time
> 
> Hi Paul
> 
> So what does your stored procedure look like?
> Which date is stored with the time? Today's date?
> 
> /gustav
> 
> >>> paul.hartland at fsmail.net 18-10-2006 13:51:46 >>>
> To all,
> 
> I have a little problem which I am sure this group can tell me the answer
> to....I have a datetime field in SQL Server 2000 I have an insert stored
> procedure which I pass the time to to put into the table, however it always
> puts the date in front of the time....I can input the time directly and no
> date gets put in front, I can also use a recordset with an update which
> works (but is slower)....Is there anyway I can insert a time into a datetime
> field in SQL Server 2000 without it putting the date in front using a stored
> procedure.
> 
> Thanks in advance for any help on this as it's driving me nuts.
> 
> 
> Paul Hartland 
> paul.hartland at fsmail.net 
> 07730 523179
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> 
> 
> 
> Paul Hartland 
> paul.hartland at fsmail.net 
> 07730 523179
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

-- 
Stuart