Re: Obtaining hundredths of a second data from time data types

On 03/05/2016 12:45 PM, Scott Ferrett wrote:
> Interesting. It looks like PostgreSQL is conforming to the
> specification (unlike Microsoft SQL or SQL Anywhere). However, it does
> not help me with the underlying problem and that is "How do I get the
> fractional part of a TIME field via ODBC?".
>
> I have tried SQL_C_TYPE_TIMSTAMP, SQL_C_TYPE_CHAR and
> SQL_C_TYPE_DEFAULT. None of these return the fractional part of the
> time field. I've also tries SQL_C_TYPE_BINARY and get an error
> szSqlState = "07006", *pfNativeError = 14, *pcbErrorMsg = 43,
> MessageText = "Received an unsupported type from Postgres."
>
> So how can I get the fractional information?

Use?:

SQL_C_TYPE_TIME

>
> Note that I do not have any control of the SQL statement. My code needs
> to handle any statement and return all information about the query.
>
> Cheers,
> Scott
>
> On 05/03/2016 09:03, Adrian Klaver wrote:
>> On 03/04/2016 01:20 PM, Scott Ferrett wrote:
>>> Here is a screen shot from ODBCTest after Issuing "SELECT ts, t FROM
>>> TestTable" and binding an SQL_C_TYPE_TIMESTAMP to each of the two
>>> columns:
>>>
>>>
>>>
>>> As you can see, the timestamp has the hundredths part filled in where as
>>> the time part does not.
>>>
>>> I also tried binding to SQL_C_CHAR as a work-around. But this made no
>>> difference, producing:
>>>
>>>
>>>
>>> This is the result of the same select statement run from pgAdmin III
>>> showing that there the hundredths part is returned for both fields:
>>>
>>>
>>
>> Looks like this is the reason:
>>
>> https://msdn.microsoft.com/en-us/library/ms712436%28v=vs.85%29.aspx
>>
>> The identifier for the time ODBC SQL data type is:
>>
>> SQL_TYPE_TIME
>>
>> to
>>
>> SQL_C_TYPE_TIMESTAMP None[b] Data[c] 16[d]
>>
>> [c] The date fields of the timestamp structure are set to the
>> current date, and the fractional seconds field of the timestamp
>> structure is set to zero.
>>
>>
>>>
>>>
>>> Cheers,
>>> Scott
>>>
>>
>>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com