Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pervasive SQL - Updating database date fields via ODBC with null and '' values

Status
Not open for further replies.

Sladybug

Technical User
Sep 13, 2017
1
US
I am using DNN Sharp action grid to capture employee updates and write them back to the Timberline database via ODBC. Null dates are stored as '' in Action grid. I need to write an update query through an ODBC driver to a Timberline database where I can format null values. If I was using SQL I could use a case statement to account for dates being null or '', or having a value. If I write my update query as

UPDATE PRM_MASTER__EMPLOYEE
SET Employee_Name = 'ROOF;SHANNON R'
,Rehire_date = Null
WHERE Employee='30715'


the update is successful. However the following returns the error "Error 0 22007 [Simba][SimbaEngine ODBC Driver]Invalid date, time or timestamp value.";
UPDATE PRM_MASTER__EMPLOYEE
SET Employee_Name = 'ROOF;SHANNON R',
Rehire_date = ''
WHERE Employee='30715'


If I try and write the below this also generates an error "Error 0 42000 [Simba][SimbaEngine ODBC Driver"
UPDATE PRM_MASTER__EMPLOYEE
SET Employee_Name = 'ROOF;SHANNON R',
Rehire_date = if(Rehire_date='',null,'Rehire_date')
WHERE Employee='30715'


I've tried to use a case statement as well with no luck. I am using the Timberline ODBC Data Driver from Sage Software version 15.01.39.

Thank you.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top