date-time format wrong!! - Misunderstanding.

[Ruslan Zasukhin]

On 11/12/07 5:31 PM, "Danny Lewkin" <daniel.lewkin at cognosis.be> wrote:

> It's even worse than that!!
> 
> A string from RB, stored in the DB through a cursor, is also stored
> in the wrong way!!
> What I mean is, the string in RB is 2007-12-11 14:35 (on debug
> level), when I store it in the DB and do a select, it shows as
> 11-12-2007 14:35 !!
> 
> The function NOW() should NOT be influenced by de dateformat on a pc!
> It should look at the dateformat of the DB, and work with that setting!
> Well, in my opinion anyway!

AND 

On 11/12/07 11:40 PM, "Mr. Bart Pietercil" <bart.pietercil at gmail.com> wrote:

> The database IS set to YYYYMMDD
> 
> So at least the NOW function is exhibiting the problem.
> But also running a trigger that uses the now function inserts non
> yyyymmdd formated dates (and screws up all date handling we are doing
> in the backend)
> HUGUE problem for us as the release date is approaching fast


-----------------------
Hi Bart,
Hi Danny,

Today is morning, more fresh head, we have think a little :-)

First of all I want discuss some Misunderstanding how Valentina works with
Dates and Time.

1) function NOW() returns the DATETIME value.

    DATETIME value this is 8 byte NUMERIC LLONG value.

    DATETIME value _always_ have YMD format packed into BITs.

    DATETIME value is ALWAYS platform independent !!!


2) Bart, says that you use Triggers which do INSERT of NOW().
    I assume you have in the table some DateTime fields for this.
    right?

    This means that on disk you have correct YMD value.


3) When you change db.DateFormat variable,
    you change only THIS parameter.

    This DO NOT make any changes on disk for Date/Time columns or their
    indexes, because these fields ARE in the INDEPENDENT format ALWAYS.

4) So what happens when we change db.DateFormat  ?
    And what is affected by this parameter?

ANSWER:

    db.DateFormat parameter affects only Conversions
    
            Datetime Value <=> STRING.


5) For example, you do

        SELECT NOW()

This returns cursor with ONE DateTime field. Note, this is not Vstring
field. So if you in REALbasic code will do

    dim dtFld as VDateTime

    dtFld = curs.DateTimeField(1)

    y = dtFld.year
    m = dtFld.Month
    ...

You will get correct values.

But if you do 

    curs.Field(1).GetString()       <<<<< here works db.DateFormat


6) Next, important to note that values to strings can convert as VSERVER so
VCLIENT. 

   * WHEN you use Triggers they work on VSERVER. And they save DATETIME
values. Here cannot be wrong format !!!

   * Only you can see "wrong" values in Vstudio, because Vstudio to display
values need convert them to STRINGS.
    

And yes we now see issues that in Vstudio 3.5 we have switch to SQL way.
Or the same problem can be from SQL Editor. If we do SQL command as
    SET PROPERTY DateFormat

Then SQL string go to VSERVER, is parsed there and executed, db changes
format parameter BUT !!!  Vstudio itself knows nothing about this. Because
Vstudio do not parse SQL string ...

We now return back to API way in Vstudio, and thinking that it should work,
because when you do API Way

     DateFormat = EVDateFormat.kYMD
     
VCLIENT get info about CHANGE of this parameter.

7) and this fact makes us wonder that API way not works for Danny:

> 2) And this is how it's written in our db-creation-program :
> 
>      DateFormat = EVDateFormat.kYMD
>      DateSep = "-"
>      TimeSep = ":"
> 
> these settings are accepted and shown in the same way when we open
> the DB with VStudio...

Or may be I miss-read this?

Danny? Do you mean that if you assign DateFormat to db from RB code, then
such db works fine in Vstudio also?

Then where is URGENT problem to fix for you guys??
    
    * Triggers + NOW() works on server side.             GOOD.
    * RB code via API can change format date of db       GOOD.
    * Vstudio can show such db correctly after RB        GOOD ?

Only issues if try change format of VSERVER DB via Vstudio.
Do you mean this?


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]