Hi all,

   I am working on an ActiveXForm project that uses data in an SQL
table that is a combination of dates and time data imported from an
Access97
table into an SQL 7 table and data that was directly entered into the
SQL
database.

   The problem that I am having is that the date/time formats are not
the
same for the data entered into the Access tables as they are for data
entered into the SQL tables and I am having a hell of a time displaying
dates and time in a consistant manner in the ActiveX component.

  I have therefore decided that I will clean up the data in the SQL
table
before I continue developing the ActiveXForm.

   I have been able to clean up the dates using an SQL script that
utilizes
cursors to replace certain dates with others, e.g:

    WHILE @@fetch_status = 0
    BEGIN
        IF @old_date = CONVERT(DATETIME, '1/1/99', 1)
        UPDATE table1
        SET EntryDate = '1/1/1900'
        WHERE CURRENT OF date_curs

        FETCH NEXT FROM date_curs INTO @old_date
    END

      The above code replaces all '1/1/99' dates with '1/1/1900'.

      My main problem however, is with time:

      I need to add dates to time fields that contain only time
information.

      For example, some of the time data that was entered directly into
the
SQL database tables are in this format :

        '9/11/98 1:15:45 PM'

        The above contains both date and time although I only need time
in
this field.

        The Access 97 time data however, is in the format :

        12:00:00 AM

        Without the date.

        I need to have all times in the SQL format which includes both
date
and time.

        Can anyone help me devise the correct SQL cursor syntax that
will
take a time in the '12:00:00 AM' format and add a date to it to create
the
SQL format ' 1/1/1900 12:00:00 AM' ?

        I can specifically replace '12:00:00 AM' using the above code,
however, how would I deal with '2:12:43 PM' ?

        Thanks in advance for any and all help.

jeff alerta
j...@nestworks.com

...
Hi.
I personally allways use Params in my Query, so I let BDE/ODBC take care of
all that, and I only have to deal with TDateTime's.
Works perfect.
Regards
Stig Johansen.

Stig,

    I use FieldValues['fieldname'] to populate fields from SQL Queries and
ParamByName('input_value').As(Datatype) to pass data to stored procedures for
input into the SQL tables.

    Due to the different requirements of the fields in the ActiveXForm app that
I am building , retrieveing data or inputing data or filling new records with
existing data or creating new data on the fly and the different date/time
formats (Access97 or SQL Server) that exist in the SQL tables themselves and
add to that the differrent way of formatting date/time in Delphi ( AsDateTime,
AsDate, AsTime, StrToDate, StrTotime, StrToDateTime, VarToDateTime,
FormatDateTime, etc..)  I end up having to write code that needs to account for
every variation and permutation of date/time that is possible.

    This can be very confusing to say the least.

     Therefore, I want to start with all date/time data in MS SQL Server format
and then write Delphi code that will deal with that format.

     That should save me a lot of headaches.

jeff