Re: DateTime, variants and double

You are right: SQL converts 0.0 to 1.1.1900, whereas COleDateTime takes 0 as
30.12.1899 - difference 2 days.
Nevertheless I thought that ADO did the magic so that there was a consistent
view of the data. Well, I guess that a date is not really a double!
I know I could change the (existing) code (which uses a 'SELECT * FROM..')
to do the explicit cast, but I really wanted to understand what was
happening. I think I see it now.
Thank you.

"Bob Barrows [MVP]" wrote:

Geoff Cohen wrote:
I have C++ code that reads a recordset from a table, reading the
fields into a variable of type _variant_t.
The table has a field of type DateTime which for various reasons I
want to handle in code as a double, which should be possible.
Debugging the code, after reading the datetime field the _variant_t
variable (vt0) is displayed by debug as has having type VT_DATE and
having value 05-02-2008 (DD-MM-YYYY European) which is correct. But
extracting the corresponding double value double dbldt = (double) vt0
gives 39483.0 (this is the dblVal field in the variant).

If I use Query Analyser, and try
SELECT CAST (CAST ('02-05-2008' AS DATETIME) AS FLOAT)
It shows 39481.0
and, to confirm,
SELECT CAST (39481.0 AS DATETIME)
shows 02-05-2008

There is therefore a discrepancy of 2 days between the database value
and the value returned by GetValue()
Note that all times are 00:00 in this example.

Can anyone shed any light on this?
btw I'm using SQL Server 2000 and VC++ 6

I would suspect that C++ uses a different seed date than SQL Server. It
should be easy to verify: cast 0 as a datetime in both systems.

I'm curious why you would not do the conversion in the sql statement used to
retrieve the data ...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"



.

Relevant Pages

  • Re: Select mit IF
    ... > Cast as Datetime) as Einsatzdatum, ... > aeinsatz.pause) as PauMinuten, Cast as ... > AEINSATZ.BKNZARBZEIT as BerechKZ ...
    (microsoft.public.de.sqlserver)
  • Re: DateTime, variants and double
    ... SQL Server stores them as paired integers - see BOL ... The table has a field of type DateTime which for various reasons I ... SELECT CAST AS FLOAT) ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: IF Klausel
    ... > So habe folgenden Select erstellt: ... > Cast as Datetime) as Einsatzdatum, ...
    (microsoft.public.de.sqlserver)
  • Re: Convert.ToDateTime
    ... and you will need the cast (or the conversion) in order for the code ... If you know that the 16th field is a DateTime, ... or a string which represents a date? ...
    (microsoft.public.dotnet.languages.csharp)
  • ATL OLEDB Consumer Templates, SQL CE
    ... I am receiving a string by doing: ... CString str = cmd.GetValue; for getting a string from column 1 ... I am not sure what kind of cast I have ... to do to access a "datetime" field. ...
    (microsoft.public.sqlserver.ce)