SQL Server DateTime != SqlWindows Datetime

Discussion forum about all things Team Developer 7.x
david@paramax.ca
Canada
Posts: 2
Joined: 15 Apr 2019, 16:52
Location: Canada

SQL Server DateTime != SqlWindows Datetime

Post by david@paramax.ca » 30 Apr 2019, 22:52

TD 7.1.1 and SQL native Client 11 - I select contents of a datetime value from a table into a bind variable defined as datetime in SQL Windows. I then use this bind variable as a condition in an update statement to ensure the row has not changed

Create table test (Testid int, LastModifyTIme datetime,status int)
Select status, LastModifyTime from test where testid =1 into :sStatus, :dtLastUpdateTime
Update test set status = 2 where testid =1 and LastModifyTime = :dtLastUpdateTime

If the value in SqlServer looks like this 2019-07-30 13:22:15.200 - It works
but If the value in SqlServer looks like this 2019-07-30 13:22:15.212 - It does not work -
Seems to be dependent on the value in the bind variable in the milliseconds portion- we played by setting this to 000 and 100 it worked - 101 failed - I think 110 worked - ie 1 or 2 positions is ok but not 3

On windows 7 using ODBC Driver 11 for Sql Server on SQL server 2008 - it works all the time

I tried defining column in database as Datetime2 - But I get a sql error 241 Conversion failed when converting date and/or time from character string

This problem seems to be specific to versions of the ODBC Driver Native Client 10 seems to work but not Native Client 11

Thanks
David Pollock
Paramax Solutions

FRBhote
India
Posts: 1326
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: SQL Server DateTime != SqlWindows Datetime

Post by FRBhote » 01 May 2019, 04:57

Have you tried timestamp instead - works perfectly.

david@paramax.ca
Canada
Posts: 2
Joined: 15 Apr 2019, 16:52
Location: Canada

Re: SQL Server DateTime != SqlWindows Datetime

Post by david@paramax.ca » 01 May 2019, 12:15

Thanks - But no as I need to have the date and time in this column for audit reasons - As well I am upgrading from 2.1 to 7 - and this would be a massive change to the code - but might be the only solution

thomas.uttendorfer
Site Admin
Site Admin
Germany
Posts: 171
Joined: 05 Mar 2017, 17:19
Location: Munich Germany

Re: SQL Server DateTime != SqlWindows Datetime

Post by thomas.uttendorfer » 02 May 2019, 07:47

Hi David,

Maybe you can try to strip off the milliseconds by using this one:

Update test set status = 2 where testid =1 and DateAdd(ms, -DatePart(ms, LastModifyTime), LastModifyTime) = :dtLastUpdateTime;

Regards Thomas
Thomas Uttendorfer
[ frevel & fey ] Software-System GmbH
https://thomasuttendorfer.wordpress.com/

FRBhote
India
Posts: 1326
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: SQL Server DateTime != SqlWindows Datetime

Post by FRBhote » 02 May 2019, 11:23

Tried this?

"Update test set status = 2 where testid =1 and LastModifyTime = '" || SalFmtFormatDateTime( dtLastUpdateTime, 'YYYY-MM-DD-HH.MM.SS.MSMSMS' ) || "' "

Return to “General Discussion”

Who is online

Users browsing this forum: No registered users and 0 guests