MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Date validation through DB2 query

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
astro
Beginner


Joined: 05 Oct 2005
Posts: 31
Topics: 7

PostPosted: Tue Nov 08, 2005 6:06 pm    Post subject: Date validation through DB2 query Reply with quote

Hello friends,
I need to find whether date entered by the user is a valid date or not?
Is there any DB2 SQL query to find this out?
Thanks for your replies
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Tue Nov 08, 2005 6:34 pm    Post subject: Reply with quote

astro,

Check this link. An sql code of -181 means your date is invalid.

http://www.mvsforums.com/helpboards/viewtopic.php?p=22257#22257

Hope this helps...

Cheers

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
astro
Beginner


Joined: 05 Oct 2005
Posts: 31
Topics: 7

PostPosted: Wed Nov 09, 2005 5:23 am    Post subject: Reply with quote

Hi Kollusu,
Thanks for your answer.
In fact I have also seen the post you have mentioned in your reply.
And When I tried, I am getting SQLCODE -104.
Attaching the trace information below.
Can you tell me how can I overcome this problem?
Code:

00255 Before SQLEXEC...
00256           VALIDDATE= "          "
                ENTRYDATE= "09.11.2005"
00257 CONNECT TO DB2T USER ASTRO USING ******
00258 SQLCODE=0     SQLERRP=DSN07011                  SQLERRD=(0 1 0 1 4 0)     SQLWARN= , , , , , , , ,                  SQLEXT=   00000                  SQLSTATE=00000
00259 SET CURRENT SQLID = 'TEST'
00260 SQLCODE=0     SQLERRP=DSN07011                  SQLERRD=(0 0 0 0 0 0)     SQLWARN= , , , , , , , ,                  SQLEXT=   00000                  SQLSTATE=00000
00261 SET  :VALIDDATE= DATE(  :ENTRYDATE) 
00262 After SQLEXEC...
00263 SQLCODE=-104     SQLERRP=DSNHPARS
      SQLERRD=(0 0 0 -1 7 0)     SQLWARN= , , , , , , , ,
      SQLEXT=   42601
      SQLSTATE=42601
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Wed Nov 09, 2005 5:40 am    Post subject: Reply with quote

astro,

Are you using the query posted by me on the mainframe or PC? I am not sure if the PC version of DB2 has the host variable concept

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
astro
Beginner


Joined: 05 Oct 2005
Posts: 31
Topics: 7

PostPosted: Wed Nov 09, 2005 6:03 am    Post subject: Reply with quote

Hello Kollusu,
You are absolutely right.
I tried this after your latest reply.
Your query works fine in a cobol code running in mainframe.
well. I explain the problem in detail.
I am trying your query in a Visual Age program.
Visual Age has the facility to trace the program without generating the program itself.
So when this query is executed from Windows, it connects to DB2 in mainframe and returs with sqlcode -104.
But if the same program is generated in COBOL & when the generated COBOL code runs in mainframe, the query works fine & I am able to get the desired functionality.
Thanks for your helps.
Though my purpose is resolved, I can not resist myself asking you that is there any other DB2 query(for eg., with SYIBM.SYSDUMMY1) with which we can avoid -104 while running in PC also?
Back to top
View user's profile Send private message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany

PostPosted: Wed Nov 09, 2005 7:43 am    Post subject: Reply with quote

Try this:

Code:

SELECT DATE('2005-12-23') FROM SYSIBM.SYSDUMMY1 ;


You get SQL code = 0 in case date is correct, otherwhise sqlcode -181
Back to top
View user's profile Send private message
astro
Beginner


Joined: 05 Oct 2005
Posts: 31
Topics: 7

PostPosted: Thu Nov 10, 2005 12:46 pm    Post subject: Reply with quote

Hello bauer,
I am getting the same error(sqlcode -104) I got for Mr.Kollusu's answer.
Anyhow Thanks for your answer.
Back to top
View user's profile Send private message
Arun Sundaramoorthy
Beginner


Joined: 04 May 2016
Posts: 2
Topics: 0
Location: India, Bangalore

PostPosted: Thu May 05, 2016 1:51 am    Post subject: Answer to how to valid if a content is a date or not in DB2 Reply with quote

It can be done thru SQL mentioned below, hope it helps Cool

WITH TEMP (DATEE) AS
(SELECT '160229' AS DATEE FROM SYSIBM.SYSDUMMY1)
SELECT CASE WHEN
LENGTH(RTRIM(TRANSLATE(DATEE, '', '0123456789'))) = 0 AND
SUBSTR(DATEE,3,2) > 0 AND SUBSTR(DATEE,3,2) < 13 AND
SUBSTR(DATEE,5,2) <=
SUBSTR(CHAR(LAST_DAY('20'||SUBSTR(DATEE,1,4)||'01')),9,2) THEN
'DATE'
ELSE 'NOT A DATE'
END
FROM TEMP
_________________
Arun Sundaramoorthy
Back to top
View user's profile Send private message
Nic Clouston
Advanced


Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home

PostPosted: Thu May 05, 2016 8:48 am    Post subject: Reply with quote

Is this a record? Replying to a topic that has been dead for over 10 YEARS!
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Thu May 05, 2016 10:21 am    Post subject: Re: Answer to how to valid if a content is a date or not in Reply with quote

Arun Sundaramoorthy wrote:
It can be done thru SQL mentioned below, hope it helps


Arun Sundaramoorthy,

I appreciate you trying to help, but did you test your query? I ran your query as is on the mainframe and it does not work.

here is the output

Code:

WITH TEMP (DATEE) AS                                                             
(SELECT '160229' AS DATEE FROM SYSIBM.SYSDUMMY1)                                 
SELECT CASE WHEN                                                                 
LENGTH(RTRIM(TRANSLATE(DATEE, '', '0123456789'))) = 0 AND                       
SUBSTR(DATEE,3,2) > 0 AND SUBSTR(DATEE,3,2) < 13 AND                             
SUBSTR(DATEE,5,2) <=                                                             
SUBSTR(CHAR(LAST_DAY('20'||SUBSTR(DATEE,1,4)||'01')),9,2) THEN                   
'DATE'                                                                           
ELSE 'NOT A DATE'                                                               
END                                                                             
FROM TEMP                                                                       
---------+---------+---------+---------+---------+---------+---------+---------+-
                                                                                 
---------+---------+---------+---------+---------+---------+---------+---------+-
DSNE610I NUMBER OF ROWS DISPLAYED IS 0                                           
DSNT408I SQLCODE = -180, ERROR:  THE DATE, TIME, OR TIMESTAMP VALUE *N IS       
         INVALID                                                                 
DSNT418I SQLSTATE   = 22007 SQLSTATE RETURN CODE                                                               



Assuming that you did not provide the 4 digit year, I tried the date 20160229 which is a February 29 of current year which is leap year and a valid date and your query returns a not a date which is wrong.

Here is the output of the query
Code:

---------+---------+---------+---------+---------+---------+---------+
WITH TEMP (DATEE) AS                                                 
(SELECT '20160229' AS DATEE FROM SYSIBM.SYSDUMMY1)                   
SELECT CASE WHEN                                                     
LENGTH(RTRIM(TRANSLATE(DATEE, '', '0123456789'))) = 0 AND             
SUBSTR(DATEE,3,2) > 0 AND SUBSTR(DATEE,3,2) < 13 AND                 
SUBSTR(DATEE,5,2) <=                                                 
SUBSTR(CHAR(LAST_DAY('20'||SUBSTR(DATEE,1,4)||'01')),9,2) THEN       
'DATE'                                                               
ELSE 'NOT A DATE'                                                     
END                                                                   
FROM TEMP                                                             
---------+---------+---------+---------+---------+---------+---------+
                                                                     
---------+---------+---------+---------+---------+---------+---------+
NOT A DATE                                                           
DSNE610I NUMBER OF ROWS DISPLAYED IS 1                               
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100           
---------+---------+---------+---------+---------+---------+---------+
DSNE617I COMMIT PERFORMED, SQLCODE IS 0                               
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0             



So please test your solutions before you post especially that have dormant for over a decade.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Arun Sundaramoorthy
Beginner


Joined: 04 May 2016
Posts: 2
Topics: 0
Location: India, Bangalore

PostPosted: Fri May 06, 2016 12:27 am    Post subject: It works but it not works,, strange !!! Reply with quote

Nic Clouston - I wanted to validate a large set of date stored in a char column, so I was looking for an alternate to ISDATE() function just a week back and google took me to this topic.
But none in this forum had given an answer to the question asked !!!
I got the answer and thought to give some life to this dead topic.

Kolusu - I did test my SQL before put it in here, if you run my SQL as it is in QMF it will run fine, but then after seeing your post I was shocked to see that it fails in SPUFI Rolling Eyes thats because
In QMF CHAR(LAST_DAY('20'||SUBSTR(DATEE,1,4)||'01')) gives '2016-02-01'
but in SPUFI it returns '20160201'

So the SQL which I had posted before will work in QMF and not in SPUFI.

Now the below SQL will work in SPUFI and not in QMF, it is very bad that DB2 doesn't deal with dates the same way in different places
Pfft

WITH TEMP (DATEE) AS
(SELECT '160229' AS DATEE FROM SYSIBM.SYSDUMMY1)
SELECT CASE WHEN
LENGTH(RTRIM(TRANSLATE(DATEE, '', '0123456789'))) = 0 AND
SUBSTR(DATEE,3,2) > 0 AND SUBSTR(DATEE,3,2) < 13 AND
SUBSTR(DATEE,5,2) <=
SUBSTR(CHAR(LAST_DAY('20'||SUBSTR(DATEE,1,4)||'01')),7,2) THEN
'DATE'
ELSE 'NOT A DATE'
END
FROM TEMP
_________________
Arun Sundaramoorthy
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Fri May 06, 2016 8:08 am    Post subject: Re: It works but it not works,, strange !!! Reply with quote

Arun Sundaramoorthy wrote:

But none in this forum had given an answer to the question asked !!!


Wrong, the answer is already provided and why is that checking for -181 is an issue as it is indeed done to verify the date and time.

Check this explanation for SQLCODE of -181

https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.codes/src/tpc/n181.dita


Arun Sundaramoorthy wrote:

I got the answer and thought to give some life to this dead topic.


Not really as your query does not work

Arun Sundaramoorthy wrote:


Kolusu - I did test my SQL before put it in here, if you run my SQL as it is in QMF it will run fine, but then after seeing your post I was shocked to see that it fails in SPUFI Rolling Eyes thats because
In QMF CHAR(LAST_DAY('20'||SUBSTR(DATEE,1,4)||'01')) gives '2016-02-01'
but in SPUFI it returns '20160201'


To begin with the default date format on the mainframe is YYYY-MM-DD ie dates always with a separator. So unless your shop has defined the date format differently it is is always with separator.
The date formats USA, ISO, JIS, EUR all refer to date with separator.

LOCAL refers to a site default format.

https://www.ibm.com/support/knowledgecenter/SS9UMF_10.1.0/ugr/tpc/dsq_format_date_time.dita


Arun Sundaramoorthy wrote:

So the SQL which I had posted before will work in QMF and not in SPUFI.


NO you posted a solution that works just at your shop as your shop has different date formats defined.

Arun Sundaramoorthy wrote:

Now the below SQL will work in SPUFI and not in QMF, it is very bad that DB2 doesn't deal with dates the same way in different places
Pfft


Both QMF and DB2 process the exact same way but each have their own defaults. Looks like your shop has a weird set up where they are not consistent.

And for the record even your new query does NOT work. I still do not understand why you chose to use a 2 digit year instead of CCYY to begin with.

And secondly even if your query is working , you are limiting to the dates within 2000-2099. So design a solution which would work universally without any changes.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group