Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved SQL Server Datetime

  1. How-to
0
Votes
Undo
  1. Andrew Davis
    Andrew Davis
  2. PowerBuilder
  3. Monday, 8 April 2024 11:25 AM UTC

Hi

 

pb 2022r3 win10 sql 15

All, still trying to connect to a sql server rather than my normal SQL Anywhere.

 

I am trying to save a PB datetime field back to the SQL server database and I am getting an error

SQLSTATE = 22007
Microsoft SQL Server Native Client 11.0
Conversion failed when converting date and/or time from character string.

No changes made to database.

INSERT INTO "rfid_import_stats" ( "run_id", "last_run_time", "newest_data", "records_changed" ) VALUES ( 2, '2024-02-28 14:49:07.978783', '1900-01-01 00:00:00.000', 0 )

 

Any help would be greatly appreciated - the field in the SQL is type datetime - should i use a different type ?

 

regards

Andew

  1. SQL Server
Print
Troubleshooting "Connection request timed out" in ...
Responses (3)
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Tuesday, 9 April 2024 16:39 PM UTC
  2. PowerBuilder
  3. # 1

I you look at the page Mike linked to, datetime can only handle 3 decimal places. The datetime2 type can handle 7. The PowerBuilder datetime can only handle 6 decimal places so using datetime2 may not work correctly.

You should install the latest Microsoft client software here:

https://learn.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver16

Set DBMS to 'MSO' and the DBParm Provider property to 'MSOLEDBSQL19'.

Using a supported database interface may take care of the problem.

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 9 April 2024 16:14 PM UTC
  2. PowerBuilder
  3. # 2

Hi Andrew;

  FYI: the SNC DB Client was deprecated by Microsoft way back in the SS 2012 release.

Microsoft's current direction is to use the newer & supported "MSOLEDBSQL" DB Client. Especially for newer versions of SS like SS2019 & higher

This would mean that in your PB App's DB Connection settings you would be coding (for example):  SQLCA.DBMS = "MSO". That will load the PB DB "Interface" driver that will in turn then connect to the Microsoft "MSOLEDBSQL" SS DB Client (which must be installed on your PB IDE / App EXE machine). Your DBA Team should be able to supply you with the "MSOLEDBSQ" DB Client driver installation.   HTH

Regards .. Chris

Comment
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Monday, 8 April 2024 23:43 PM UTC
  2. PowerBuilder
  3. # 3

what is generating the datetime value? compare your 2 datetime strings.  

'2024-02-28 14:49:07.978783'   has too many decimal places for datetime

 

datetime (Transact-SQL) - SQL Server | Microsoft Learn

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.