SQLCODE: -180 The DATE, TIME, OR TIMESTAMP value is invalid

1optimus · #1 (**permalink**) 09-26-11, 11:58

Friends,

This is my first post and I'm new to DB2. Hopefully somebody would be able to point me in the right direction. Before I post my problem here is a snapshot of the environment:
IBM DB2 Universal Database Version 8
Level: DB2 v8.1.9.700
Build level: s050422
Product identifier: SQL08022

Problem:
This is an old application which needs some repair work. Language used is ASP(VB6) and database is DB2. I'm trying to display date fields and update them with a submit button. The Date columns in DB2 are of type DATE. There is also a TIMESTAMP column to determine the exact time of the update which is also updated with the submit button. To display the dates presently i'm using a function called GetRs. The query goes like this:

strSql = "SELECT a.PNUM, CASE CHAR(a.REF_DT) WHEN '0001-01-01' THEN '' ELSE CHAR(a.REF_DT) END R_DT , "
and so forth

The default values in the DATE columns are 1-01-01 and notnull.

The display(GetRs) works ok. But when I try to update the dates, the Recordset.UpdateBatch throws error - SQLCODE 180. And I think this is due to the incorrect DATE/CHAR conversion that I've implemented.

The client side script is designed to pass the date values in the format of 'Year-Month-Day' and this format is passed to the sql string.
I searched this forum and found few similar threads but couldn't implement them in my code.
I've tried using CAST, DATE(), DT() etc from the page below -
Who can help me to translate SQL Server query to DB2?

I would appreciate it if somebody could look into the issue and point me in the right direction. If you need more information please let me know.

Thanks,
Optimus

n_i · #2 (**permalink**) 09-26-11, 12:20

Why did you choose to post the query that works, but not the statement that fails? Is this some sort of a game where we get to guess what the update statement looks like?

1optimus · #3 (**permalink**) 09-26-11, 13:58

No. There was no "wild guess game" intended. The query works fine independently. But my question was related to conversion. Since the Date columns in DB2 are of type DATE my interpretation from the error was that I'm not converting to the DATE format properly. I'm using CHAR in the query.

Code:

Public Function UpdateABCDRs(adoABCDrs As Variant, ByVal lngPNUM As Long, _
                                Optional ByVal strUserID As String) As Boolean
---
---
adocnConn.BeginTrans
objABCD.GetRs rsABCD, adocnConn, eABCDAllFields, lngPNUM
If Not (rsABCD.BOF And rsABCD.EOF) Then
       rsABCD!PNUM = lngPNUM
       rsABCD!REF_DT = adoABCDrs!REF_DT
       ----
       ----
      rsABCD.Update
      objABCD.UpdateRs rsABCD, adocnConn
Else
      ---
      ---
End If
   adocnConn.CommitTrans

GetRs function:-

Code:

Public Function GetRs(adoRs As Variant, _
        Optional objConn As Variant, _
        Optional ByVal eABCDvt As EnumABCDViewTypes , _
        Optional ByVal lngPNUM As Long = 0) As Boolean

        ----
        ----
        Select Case eABCDvt 
          Case eABCDAllFields
          strSql = "SELECT a.PNUM, CASE CHAR(a.REF_DT) WHEN '0001-01-01' THEN '' ELSE CHAR(a.REF_DT) END R_DT , " 
---
---

UpdateRs is a function in ABCD class (I can't modify this function - existing structure)
the update statement:

Code:

Public Function UpdateRs(adoRs As Variant, objConn As Variant) As Boolean
 Dim rData     As DataProcessor
    
    On Error GoTo UpdateRS_ErrorHandler
            
    UpdateRs = False
    
    Set rData = New DataProcessor
    rData.UpdateRecordset adoRs, objConn
    Set rData = Nothing
           
    UpdateRs = True
     
    Exit Function

Comes here

Code:

Public Sub UpdateRecordset(adorsRecs As Variant, objConn As Variant)

Dim objEventLog As Object
Dim strErrDesc As String, strErrSource As String, lngErrNbr As Long
    On Error GoTo UpdateRecordset_ErrorHandler
        
    With adorsRecs
        Set .ActiveConnection = objConn
        .UpdateBatch adAffectAll      - Fails here. Value of adAffectAll = 3 
    End With
       
    Set adorsRecs.ActiveConnection = Nothing
        
    Exit Sub
UpdateRecordset_ErrorHandler:
   ----
   ----
End Sub

I've tried to put forth all the relevant codes for my problem. If it's still insufficient, please mention it.
All of the above functions (GetRs & UpdateRs) work perfectly fine when the Date column is of type DECIMAL.

PS:If the code is insufficient please let me know. Or simply ask what do you need and I'll post it. I'm still finding my through VB6-DB2 environment. It would help me immensely if anyone could point out where I went wrong or what information is actually relevant rather than indulging in wild guessing games

.

n_i · #4 (**permalink**) 09-26-11, 14:18

Quote:

Originally Posted by 1optimus

Code:

CASE CHAR(a.REF_DT) WHEN '0001-01-01' THEN '' ELSE CHAR(a.REF_DT) END

Empty string is not a valid representation of a date value. '1-01-01' isn't one either. Check what value you are assigning in "rsABCD!REF_DT = adoABCDrs!REF_DT"

1optimus · #5 (**permalink**) 09-26-11, 14:34

Thanks for the quick response. Checked the value assigned to
rsABCD!REF_DT = adoABCDrs!REF_DT
Assigned value:
2002-03-05

Edit: The purpose of the empty string was to display blank if the default value of the Date column is 0001-01-01

Code:

CASE CHAR(a.REF_DT) WHEN '0001-01-01' THEN '' ELSE CHAR(a.REF_DT) END

The client side script takes the values from a textbox which is split into txtYear, txtMonth & txtDay. There is a specific function which formats/converts all these values to the form 2002-03-05 and this value is passed to the vb6 code. This function can be modified if needed.

The query below executes ok in the DB2 window.
UPDATE ABCD_TEST
SET REF_DT = '2003-03-05', WHERE PNUM = 12345678;

My idea was to convert the user inputs to the format '2003-02-03'. Is this an invalid way? If so how could I modify the query.

Thanks