Re: Typed dataset: Time only data in SQL dateTime Field

I think that you're still missing the point Jeff.

The point is that, while inserting a value into a Jet database and then
viewing the data in MS Access will give you the results you are observing,
if you view the data via any aother 'mechanism' then you NOT observe that
same behaviour.

There is code built into MS Access that, for a Jet Date/Time data type,
basically says "Aha! I know all about a value where the integral part is 0
or -0 (#12/30/1899#) so I will hide the date part from you and only show you
the time part unless you instruct me otherwise".

That behaviour in MS Access may be all well and good for a column where you
are only interested in the time part, but it will also behave in the same
way for a column containing birth dates and the birth date for a particular
person happens to be December 30 1899 (sure that would make them 107). MS
Access, unless instructed otherwise, will still hide the date part from you
because it doesn't know the difference between an actual date of
#12/30/1899# and a value where you have only inserted the time part.

When you migrate the data from Jet to SQL Server then you will find that SQL
Server has no such date part suppressing mechanism by default, and even if
it did, it would work on the date 1900-01-01 and not 1899-12-30. That is
because 0 or -0 represents a different date on SQL Server than it does in
Jet.

When, in a .NET environment, you retrieve the value from either Jet or SQL
Server, you are, whether you realise it or not, populating an instance of a
..NET DateTime structure. If you only want to see the time part then you MUST
explicitly suppress the date part. There are a number of methods of the
DateTime structure that you can use to do this.

If you only use the database datetime column for storage retrieval purposes
and NEVER (I repeat NEVER) do any maths on the values or sort the column
then what the date part actually is is irrevalent and can be any old date
that is valid for the environment (#0100/01/01# to #9999/12/31# for Jet or
'1753-01-01' to '9999-12-31' for SQL Server).

The moment that you need to do any maths on the values or sort the column
then the date parts MUST be values that are contextually correct for
whatever operation you are applying.


"JeffDotNet" <JeffDotNet@xxxxxxxxxxxxxxxxx> wrote in message
news:5244D31B-6A43-4CE6-A4E3-E1829C06CBC7@xxxxxxxxxxxxxxxx
Thanks Wen and Stephany,

This is indeed what I was looking for.

Dim SqlTimeOnly As Date = #12/30/1899#
SqlTimeOnly = SqlTimeOnly.Add(MyTime.TimeOfDay)

MyTableAdapter.Insert(sqlTimeOnly,yada,yada,yada)

This does in fact produce the outcome I wanted. When displaying the table
after insert only the time appears.

Thanks Again,

Jeff
"WenYuan Wang" wrote:

Hi Jeff,

I agree with Stephany. The DateTime filed always contains both date and
time parts. I'm afraid that it is not possible for us to only insert time
into DateTime type column. SQL Server inserts midnight (00:00:00) when
time
information is missing, and 1900-01-01 when date information is missing.

But, under my test, in SQL 2000 if we set the date information to
"1899-12-30" SQL database will only display the time part of the field.
This is similar that you directly insert time into table by SQL Server
Enterprise Manager. I think maybe this is what you want.

For example:
Insert TableName into ([c1,c2]) values (1,' 1899-12-30 18:00')

In SQL database, the table will only show the time part of the field.
If you don't want to insert the day part into table, we suggest you can
set
the date information to "1899-12-30".

I think this is the workaround to your issue.

Hope this will help.
Sincerely,
Wen Yuan




.

Relevant Pages

  • Re: Im very confused about the different databases!
    ... There's DAO, ADO, ADO.NET, Jet, SQL, SQL Server ... What database & version comes with VB6 LE? ... What is the most current database that I can use with VB6 LE? ... Why isn't Jet or whatever VB6 LE uses listed in Add-Remove Programs? ...
    (microsoft.public.vb.general.discussion)
  • Re: Database Connectivity
    ... Never was Jet mentioned anywhere at anytime. ... >> Microsoft Access Database Solutions, with not a single mention of Jet. ... front end, and the underlying database engine, JET. ... like SQL Server, Sybase, MySQL, etc. ...
    (comp.lang.python)
  • Re: Duplicate Values Question
    ... My general approach when working with SQL server does NOT include the use of ... If you think that Access 2007 means that Jet ... database code will remain the same forever... ... constraints correct and maintain the data integrity... ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Why not Access...?
    ... The first point is, yes, Jet, not Access, is a file based, Pseudo-RDBMS. ... Jet database will corrupt and become ... MS Jet/Access, MSDE, and SQL Server 2005 Express Edition. ... > I am developing a desktop application using VB as front-end for some clients> where the a large number of regular entries are done but there is a rare need> to delete old customers. ...
    (microsoft.public.sqlserver.server)
  • Please confirm my design
    ... Please could someone confirm my design approach: ... I am designing a Windows service that processes rows in a SQL Server ... The table has a datetime column. ... against the idea of the service constantly polling the database every ...
    (microsoft.public.dotnet.languages.csharp)