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

Hi all

If anyone wonders, MySQL uses the same base date as Access. Thus, in this context, MySQL may make up a friendlier backend than SQL Server (and Sybase I guess - don't know about Oracle etc.).

/gustav

>>> Gustav at cactus.dk 19-10-2006 11:30:55 >>>
Hi Stuart and Paul

Stuart, Access uses the 30. not the 31. of December as base date, so isn't the difference 2?

Aside, having my SQL Server back in service, if I link a date field to Access and insert a date/time like 1899-12-30 23:45:00, it displays as 23:45:00 in Access. 

If I enter 1900-01-01 23:45 and later extract the numeric value, it is 2. This is not done by the ODBC driver but is the actual numeric stored value which can be seen be running a pass-through query like this:

  select 
    yourdatefield, 
    cast(yourdatefield as float) 
  from 
    yourtable

If I insert a time with a pass-through query, it will be inserted the same way as in Paul's SP and that is with a base date of 1900-01-01 and that is Paul's problem when he extracts the time in Access.

The solution, as I see it, is to convert the date/time value to a time-only string expression when extracted in the SP like this:

  select 
    yourdatefield, 
    convert(varchar, yourdatefield, 108) 
  from 
    yourtable

or, for a date/time value and not a string expression:

  select 
    yourdatefield, 
    cast(convert(varchar, yourdatefield, 108) as datetime)
  from 
    yourtable

In Access:

  select 
    yourdatefield, 
    CDate(Format(yourdatefield, "hh:nn:ss")) 
  from 
    yourtable

So, as Paul has shown, when dealing with time only it is important to be careful if values are inserted both at client (Access) and server (SQL Server). One could choose to select a base date of your own, like 2000-01-01 - this could indeed be needed if the time values can exceed 24 hours. Or simply, whenever a time value is extracted, always to expect a date part to be present and always convert the value to the client's base date.

/gustav


>>> stuart at lexacorp.com.pg 19-10-2006 00:47 >>>
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