Conversion problem Date/Time Unify to datetime MS SQLServer

[bmoiroud]

I use SQLServer 2008 and TD2005.1 or TD 5.2
I have conversion problem between Date/Time ( unify ) and datetime or datetime2 ( MS SQLServer ) with oledb driver when i bind variable

For example:
1- I execute the following query in Microsoft SQL server Management Studio
insert into mytable( mydate , mycondition , data) select sysdatetime(), 1, 'my test'

in TD2005.1 or TD 5.2

select mydate from mytable into: dtDateField where mycondition = :mycondition
After FetchNext dtDateField have the correct value. no problem for bind out . Its working

2- in TD2005.1 or TD 5.2
select data from mytable where mydate =:dtDateField

with dtDateField = result from previous query.

the last query return NO result because the date value sent from unify to MS SQL server is not correct ( i checked it with SQL profiler )
in UNify i have 2010-06-23-16.32.32.406250 and the value send to MS SQL server is '2010:06:23 16:32:32' so we lose the end of the date 406250

Thanks for help ...

[Jeff Luther]

Trying to figure out what you think the bug is... Is it this?

the last query return NO result because the date value sent from unify to MS SQL server is not correct ( i checked it with SQL profiler )
in UNify i have 2010-06-23-16.32.32.406250 and the value send to MS SQL server is '2010:06:23 16:32:32' so we lose the end of the date 406250

I believe it's the router that is not including microseconds when you bind the datetime field. It is interesting that you can fetch an MS SS datetime with microseconds INTO the TD datetime field.

I see the same issue in SQLBase. Given this script:
create table d( dt datetime);
insert into d values(sysdatetime);

return from: select dt, @microsecond(dt) from d;
is:

Code: Select all

DT                   @MICROSECOND(DT)
==================== ================
23-JUN-2010 16:05:58    62000

and when I run this: select * from d where dt = 23-JUN-2010 16:05:58;
it returns 0 rows.

If I run this (using ">="): select * from d where dt >= 23-JUN-2010 16:05:58;
it returns 1 row.

SOLUTION: If you need to fetch that one row and it includes microseconds, then composing your select like this works (note ">=" and "<="):

Code: Select all

select * from d
where dt >= 23-JUN-2010 16:05:58 AND
      dt <= 23-JUN-2010 16:05:59;

That is, I got the row by adding one second to second DT value. This also works for the same reason:

Code: Select all

select * from d
where dt BETWEEN 23-JUN-2010 16:05:58 AND 23-JUN-2010 16:05:59;

[bmoiroud]

OK but in my software i must create log event and i can have more than one event in one seconds

i create a table in database like this

Code: Select all

create table test 
(
evtDate datetime2 not null,
comment nvarchar(50) null
)

IN TD4.1

Code: Select all

Set dtEvtDate  = SalDateCurrent(  )
Set slComment = 'This is a test'
Call SqlConnect( hlSql )
Call SqlPrepareAndExecute( hlSql, 'insert into test ( evtDate, comment ) values( :dtEvtDate, :slComment ) ' )
Call SqlCommit( hlSql )
Call SqlDisconnect( hlSql )

In TD dtEvtDate = '2010-06-24-10.04.03.930000'
the value inserted in database is 2010-06-24 10:04:03.0000000
and '2010-06-24-10.04.03.930000' != 2010-06-24 10:04:03.0000000 no ?

[Jeff Luther]

(BTW, this is not a 'bug' in v4.1. Just a 'lack of feature' in not sending on microseconds.)

You wrote:

i must create log event and i can have more than one event in one second

I can think of two solutions for this:
1) Have a round-up routine that converts a DT to the next whole second;

2) Have another DB column that keeps the microseconds as a second number. Be aware, though, that even if you fetch both DT values -- the full date without microseconds & the microseconds -- I see that the TD function SalDateConstruct (nYear, nMonth, nDay, nHour, nMinute, nSecond)
does not include being able to construct a DT value with microseconds. However, see text following for how to do this:

I did a little testing and will attach a sample in v40. I set a breakpoint last line in the function to see what vars. were set to and see this:

test.png

Couple interesting things to me:
* ALL 3 of the dt values set with SalDateCurrent() have the same time, including microseconds, even though there's some code run between 2nd and 3rd assignments.

* Note how I deconstructed the dt first by parsing off microseconds ("820000")

* Note too that in TD SalDateCurrent() only returns hundreds of a seconds (first 2 digits). Last 4 digits are always 0000.

* As a test I made a dtConstructed that has everything but no microseconds. Then I converted it to a string, removed ".000000" and concatenated the sMicroseconds, then converted that back to a DT value.

CONCLUSION: If you are willing to have a MILLISECONDS column in your DB (string or integer type) then you can combine values to get full DT with microseconds back in your app.