Re: DateTime, variants and double

In case it helps:
Access stores date/times as Doubles.
SQL Server stores them as paired integers - see BOL

Geoff Cohen wrote:
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"

--
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: DateTime, variants and double
    ... The table has a field of type DateTime which for various reasons I ... SELECT CAST AS FLOAT) ... btw I'm using SQL Server 2000 and VC++ 6 ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: number to datetime
    ... > You missed one cast. ... > select cast) as datetime) ... Yeah, I know. ... This email account is my spam trap so I ...
    (microsoft.public.sqlserver.programming)
  • Re: DateTime, variants and double
    ... The table has a field of type DateTime which for various reasons I ... SELECT CAST AS FLOAT) ... btw I'm using SQL Server 2000 and VC++ 6 ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: Problem with date formatting in SQL command
    ... DATETIME)" which is supposed to be the right syntax for SQL Server, ... CAST ... single quote, double quote & dateTest & double quote, single quote ...
    (comp.databases.ms-access)
  • Re: Execution order of WHERE clause
    ... However, even with a derived table, SQL Server can decide to push the outer WHERE predicate into the ... >> Is there an order of execution of the WHERE clause? ... >> I am tring to validate that a date value is within a date range. ... >> CAST to convert the character to a datetime type I get an error. ...
    (microsoft.public.sqlserver.programming)