Validating Timestamp with DB2

ntmartins · Posted: Wed Apr 15, 2009 11:13 pm

Hi there,

I think this is possible: use DB2 to validate if a timestamp variable e correct. Meaning validate date and time format.

Can anyone tell me how to do it?

I'm need to do it in a cobol program.

Craq Giegerich · Posted: Wed Apr 15, 2009 11:41 pm

ntmartins · Posted: Wed Apr 15, 2009 11:49 pm

In truth it was a basic question. I should have tried to think a bit more.

Thank you for your answer.

ntmartins · Posted: Thu Apr 16, 2009 7:57 pm

I've tried this solution but i'm having an bind error.

Both variables were defined as Char 26.

BIND PACKAGE(DGB0.DCCBAU) MEMBER(MAUQ123A) OWNER(DCOWNDB) QUALIFIER(DCOWNDB

DEGREE(ANY) EXPLAIN(YES) VALIDATE(BIND) OPTHINT('MAUQ123A')

DSNX200I -DGB2 BIND SQL ERROR

USING DCOWNDB AUTHORITY

PLAN=(NOT APPLICABLE)

DBRM=MAUQ123A

STATEMENT=499

SQLCODE=-171

SQLSTATE=42815

TOKENS=1 +

CSECT NAME=DSNXOBFA

RDS CODE=30

DSNT233I -DGB2 UNSUCCESSFUL BIND FOR

PACKAGE = DGB0.DCCBAU.MAUQ123A.()

Craq Giegerich · Posted: Thu Apr 16, 2009 9:18 pm

What is the actual definition of the fields and what is the actual sql statement.

ntmartins · Posted: Thu Apr 16, 2009 10:12 pm

Problem solved.

But here's the explanation for future references...

fields definition:

WS-TS1 PIC X(26).
WS-TS2 PIC (X26).

SQL Statement

EXEC SQL
SET :WS-TS1 = :WS-TS2 + 1 MICROSECOND
END EXEC.

Anyway, I've been looking into DB2 manual and the function MICROSECOND returns the microseconds from a timestamp!

When we try the statement on Spufi we get:

---------+---------+---------+---------+---------+---------+---------+---------+
SELECT '2009-03-10-15.55.00.846647' + 1 MICROSECOND
FROM SYSIBM.SYSDUMMY1
;
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 1
OF + IS INVALID

Because the correct sentence would be this:

---------+---------+---------+---------+---------+---------
SELECT MICROSECOND('2009-03-10-15.55.00.846647')
FROM SYSIBM.SYSDUMMY1
;
---------+---------+---------+---------+---------+---------

---------+---------+---------+---------+---------+---------
846647

So, the solution was:

Fields

WS-TS1 PIC X(26).
WS-DATEDUMMY PIC X(10).

SQL Statement

EXEC SQL
SET :WS-DATEDUMMY = DATE(:WS-TS1)
END-EXEC.

IF SQLCODE NOT EQUAL 0
ERROR
END-IF.

Thank you anyway.

Craq Giegerich · Posted: Thu Apr 16, 2009 11:06 pm

Microsecond is a named duration which can be added or subtacted from a timestamp.

Code:

SELECT '2009-03-10-15.55.00.846647' + 1 MICROSECOND
FROM SYSIBM.SYSDUMMY1

should be

SELECT TIMESTAMP('2009-03-10-15.55.00.846647') + 1 MICROSECOND
FROM SYSIBM.SYSDUMMY1

MICROSECOND( ) is a function.

ntmartins · Posted: Thu Apr 16, 2009 11:24 pm

Ok, got that.

Thank you for your time and explanation.