Posted: Tue Nov 08, 2005 6:06 pm Post subject: Date validation through DB2 query
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
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?
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?
Joined: 04 May 2016 Posts: 2 Topics: 0 Location: India, Bangalore
Posted: Thu May 05, 2016 1:51 am Post subject: Answer to how to valid if a content is a date or not in DB2
It can be done thru SQL mentioned below, hope it helps
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
Joined: 01 Feb 2007 Posts: 1075 Topics: 7 Location: At Home
Posted: Thu May 05, 2016 8:48 am Post subject:
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.
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Thu May 05, 2016 10:21 am Post subject: Re: Answer to how to valid if a content is a date or not in
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
www.linkedin.com/in/kolusu
Joined: 04 May 2016 Posts: 2 Topics: 0 Location: India, Bangalore
Posted: Fri May 06, 2016 12:27 am Post subject: It works but it not works,, strange !!!
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 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
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
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 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.
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
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
www.linkedin.com/in/kolusu
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
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
thats because
