Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Date in where clause - Windows

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Karthikeyan Subbarayan

New User


Joined: 24 Feb 2008
Posts: 62
Location: Boston

PostPosted: Wed Nov 15, 2017 9:07 pm    Post subject: Date in where clause - Windows
Reply with quote

HI,
I have a view which use date field as where condition. The query is working in Z/OS DB2 version but in windows DB2 this is not working. Need some help

Code:

SELECT CALN_DATE
FROM CALENDAR_VIEW 
WHERE CAST(date(CALN_DATE) AS VARCHAR(10)) < cast(date('2017-10-10')  as varchar(10))


where the CALN_DATE is a date data type
Back to top
View user's profile Send private message

Karthikeyan Subbarayan

New User


Joined: 24 Feb 2008
Posts: 62
Location: Boston

PostPosted: Wed Nov 15, 2017 9:22 pm    Post subject: Reply to: Date in where clause - Windows
Reply with quote

i am getting the error as below
Quote:
The string representation of a datetime value is out of range.. SQLCODE=-181, SQLSTATE=22007, DRIVER=4.16.53
Back to top
View user's profile Send private message
Karthikeyan Subbarayan

New User


Joined: 24 Feb 2008
Posts: 62
Location: Boston

PostPosted: Thu Nov 16, 2017 12:03 am    Post subject: Reply to: Date in where clause - Windows
Reply with quote

Finally it Worked !! ... still not sure what causing the issue in windows DB2 alone. when we add date directly in where condition.

The update query is:

Code:
SELECT CALN_DATE
 FROM CALENDAR_VIEW
WHERE CALN_DATE <
 (SELECT '2017-10-10' FROM SYSIBM.SYSDUMMY1 )
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1827
Location: NY,USA

PostPosted: Thu Nov 16, 2017 12:45 am    Post subject:
Reply with quote

Why are you doing fancy stuff? Why below wouldn't work?
Code:
SELECT CALN_DATE
  FROM CALENDAR_VIEW
 WHERE CALN_DATE < '2017-10-10'
Back to top
View user's profile Send private message
Karthikeyan Subbarayan

New User


Joined: 24 Feb 2008
Posts: 62
Location: Boston

PostPosted: Thu Nov 16, 2017 12:50 am    Post subject: Reply to: Date in where clause - Windows
Reply with quote

@Rohit
We tried that simple query as well and also tried using cast to char and tried it. All the query is working in Z/OS DB2 but for some reason is not working in windows DB2. Every time we run in windows DB2 the below error
Quote:
The string representation of a datetime value is out of range.. SQLCODE=-181, SQLSTATE=22007, DRIVER=4.16.53
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1827
Location: NY,USA

PostPosted: Thu Nov 16, 2017 12:54 am    Post subject:
Reply with quote

I know.. but why to cast the date varchar? why to use select from sysdummy1? I called them fancy stuff.
Second, check if CALN_DATE is timestamp?
Back to top
View user's profile Send private message
Karthikeyan Subbarayan

New User


Joined: 24 Feb 2008
Posts: 62
Location: Boston

PostPosted: Thu Nov 16, 2017 1:21 am    Post subject: Reply to: Date in where clause - Windows
Reply with quote

@Rohit, i run this query only at the beginning
Code:
SELECT CALN_DATE
  FROM CALENDAR_VIEW
 WHERE CALN_DATE < '2017-10-10'
and got error as
Quote:
"DBPROD.CALENDAR_VIEW" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=4.16.53

then only i used "CAST" which also ended up with same error.

CALN_DATE is defined as "DATE"
DDL of view:
Code:


CREATE VIEW "DBPROD"."CALENDAR_VIEW" ("CALN_YEAR", "CALN_MONTH", "CALN_WEEK_NBR", "CALN_DATE", "CALN_DAY_TYPE", "CALN_WEEK_DAY_ABBRV") AS
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_01, DATE(CALN_MONTH ||'/01/'|| CALN_YEAR), CALN_DAY_TYPE_01, CALN_WEEK_DAY_ABBRV_01 from DBPROD.calendar_mstr where CALN_JULIAN_DT_01 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_02, DATE(CALN_MONTH ||'/02/'|| CALN_YEAR), CALN_DAY_TYPE_02, CALN_WEEK_DAY_ABBRV_02 from DBPROD.calendar_mstr where CALN_JULIAN_DT_02 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_03, DATE(CALN_MONTH ||'/03/'|| CALN_YEAR), CALN_DAY_TYPE_03, CALN_WEEK_DAY_ABBRV_03 from DBPROD.calendar_mstr where CALN_JULIAN_DT_03 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_04, DATE(CALN_MONTH ||'/04/'|| CALN_YEAR), CALN_DAY_TYPE_04, CALN_WEEK_DAY_ABBRV_04 from DBPROD.calendar_mstr where CALN_JULIAN_DT_04 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_05, DATE(CALN_MONTH ||'/05/'|| CALN_YEAR), CALN_DAY_TYPE_05, CALN_WEEK_DAY_ABBRV_05 from DBPROD.calendar_mstr where CALN_JULIAN_DT_05 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_06, DATE(CALN_MONTH ||'/06/'|| CALN_YEAR), CALN_DAY_TYPE_06, CALN_WEEK_DAY_ABBRV_06 from DBPROD.calendar_mstr where CALN_JULIAN_DT_06 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_07, DATE(CALN_MONTH ||'/07/'|| CALN_YEAR), CALN_DAY_TYPE_07, CALN_WEEK_DAY_ABBRV_07 from DBPROD.calendar_mstr where CALN_JULIAN_DT_07 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_08, DATE(CALN_MONTH ||'/08/'|| CALN_YEAR), CALN_DAY_TYPE_08, CALN_WEEK_DAY_ABBRV_08 from DBPROD.calendar_mstr where CALN_JULIAN_DT_08 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_09, DATE(CALN_MONTH ||'/09/'|| CALN_YEAR), CALN_DAY_TYPE_09, CALN_WEEK_DAY_ABBRV_09 from DBPROD.calendar_mstr where CALN_JULIAN_DT_09 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_10, DATE(CALN_MONTH ||'/10/'|| CALN_YEAR), CALN_DAY_TYPE_10, CALN_WEEK_DAY_ABBRV_10 from DBPROD.calendar_mstr where CALN_JULIAN_DT_10 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_11, DATE(CALN_MONTH ||'/11/'|| CALN_YEAR), CALN_DAY_TYPE_11, CALN_WEEK_DAY_ABBRV_11 from DBPROD.calendar_mstr where CALN_JULIAN_DT_11 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_12, DATE(CALN_MONTH ||'/12/'|| CALN_YEAR), CALN_DAY_TYPE_12, CALN_WEEK_DAY_ABBRV_12 from DBPROD.calendar_mstr where CALN_JULIAN_DT_12 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_13, DATE(CALN_MONTH ||'/13/'|| CALN_YEAR), CALN_DAY_TYPE_13, CALN_WEEK_DAY_ABBRV_13 from DBPROD.calendar_mstr where CALN_JULIAN_DT_13 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_14, DATE(CALN_MONTH ||'/14/'|| CALN_YEAR), CALN_DAY_TYPE_14, CALN_WEEK_DAY_ABBRV_14 from DBPROD.calendar_mstr where CALN_JULIAN_DT_14 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_15, DATE(CALN_MONTH ||'/15/'|| CALN_YEAR), CALN_DAY_TYPE_15, CALN_WEEK_DAY_ABBRV_15 from DBPROD.calendar_mstr where CALN_JULIAN_DT_15 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_16, DATE(CALN_MONTH ||'/16/'|| CALN_YEAR), CALN_DAY_TYPE_16, CALN_WEEK_DAY_ABBRV_16 from DBPROD.calendar_mstr where CALN_JULIAN_DT_16 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_17, DATE(CALN_MONTH ||'/17/'|| CALN_YEAR), CALN_DAY_TYPE_17, CALN_WEEK_DAY_ABBRV_17 from DBPROD.calendar_mstr where CALN_JULIAN_DT_17 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_18, DATE(CALN_MONTH ||'/18/'|| CALN_YEAR), CALN_DAY_TYPE_18, CALN_WEEK_DAY_ABBRV_18 from DBPROD.calendar_mstr where CALN_JULIAN_DT_18 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_19, DATE(CALN_MONTH ||'/19/'|| CALN_YEAR), CALN_DAY_TYPE_19, CALN_WEEK_DAY_ABBRV_19 from DBPROD.calendar_mstr where CALN_JULIAN_DT_19 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_20, DATE(CALN_MONTH ||'/20/'|| CALN_YEAR), CALN_DAY_TYPE_20, CALN_WEEK_DAY_ABBRV_20 from DBPROD.calendar_mstr where CALN_JULIAN_DT_20 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_21, DATE(CALN_MONTH ||'/21/'|| CALN_YEAR), CALN_DAY_TYPE_21, CALN_WEEK_DAY_ABBRV_21 from DBPROD.calendar_mstr where CALN_JULIAN_DT_21 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_22, DATE(CALN_MONTH ||'/22/'|| CALN_YEAR), CALN_DAY_TYPE_22, CALN_WEEK_DAY_ABBRV_22 from DBPROD.calendar_mstr where CALN_JULIAN_DT_22 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_23, DATE(CALN_MONTH ||'/23/'|| CALN_YEAR), CALN_DAY_TYPE_23, CALN_WEEK_DAY_ABBRV_23 from DBPROD.calendar_mstr where CALN_JULIAN_DT_23 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_24, DATE(CALN_MONTH ||'/24/'|| CALN_YEAR), CALN_DAY_TYPE_24, CALN_WEEK_DAY_ABBRV_24 from DBPROD.calendar_mstr where CALN_JULIAN_DT_24 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_25, DATE(CALN_MONTH ||'/25/'|| CALN_YEAR), CALN_DAY_TYPE_25, CALN_WEEK_DAY_ABBRV_25 from DBPROD.calendar_mstr where CALN_JULIAN_DT_25 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_26, DATE(CALN_MONTH ||'/26/'|| CALN_YEAR), CALN_DAY_TYPE_26, CALN_WEEK_DAY_ABBRV_26 from DBPROD.calendar_mstr where CALN_JULIAN_DT_26 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_27, DATE(CALN_MONTH ||'/27/'|| CALN_YEAR), CALN_DAY_TYPE_27, CALN_WEEK_DAY_ABBRV_27 from DBPROD.calendar_mstr where CALN_JULIAN_DT_27 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_28, DATE(CALN_MONTH ||'/28/'|| CALN_YEAR), CALN_DAY_TYPE_28, CALN_WEEK_DAY_ABBRV_28 from DBPROD.calendar_mstr where CALN_JULIAN_DT_28 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_29, DATE(CALN_MONTH ||'/29/'|| CALN_YEAR), CALN_DAY_TYPE_29, CALN_WEEK_DAY_ABBRV_29 from DBPROD.calendar_mstr where CALN_JULIAN_DT_29 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_30, DATE(CALN_MONTH ||'/30/'|| CALN_YEAR), CALN_DAY_TYPE_30, CALN_WEEK_DAY_ABBRV_30 from DBPROD.calendar_mstr where CALN_JULIAN_DT_30 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR))) union 
(SELECT CALN_YEAR, CALN_MONTH,CALN_WEEK_NBR_31, DATE(CALN_MONTH ||'/31/'|| CALN_YEAR), CALN_DAY_TYPE_31, CALN_WEEK_DAY_ABBRV_31 from DBPROD.calendar_mstr where CALN_JULIAN_DT_31 <> '' and caln_year > CHAR(YEAR(CURRENT_DATE - 1  YEAR)));
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1827
Location: NY,USA

PostPosted: Thu Nov 16, 2017 1:50 am    Post subject:
Reply with quote

Quote:
"DBPROD.CALENDAR_VIEW" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=4.16.53
This error has nothing to do with CAST or any function but to do with Schema name used (DBPROD) . You need to know your correct schema name and the environment both before you run the query. So even if you have a right schema name but wrong DB2 environment, it won't work.

Try giving your date in the same format as VIEW in Windows.
Code:
DATE(CALN_MONTH ||'/01/'|| CALN_YEAR),
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1932
Location: UK

PostPosted: Thu Nov 16, 2017 3:21 pm    Post subject:
Reply with quote

I would also check the manual to see how DB2 for Windoze differs from DB2 for mainframe.
Back to top
View user's profile Send private message
Karthikeyan Subbarayan

New User


Joined: 24 Feb 2008
Posts: 62
Location: Boston

PostPosted: Thu Nov 16, 2017 8:01 pm    Post subject: Reply to: Date in where clause - Windows
Reply with quote

Thanks Nic
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm
No new posts Validate the Date girishb2 DFSORT/ICETOOL 9 Tue Sep 19, 2017 1:12 am
No new posts JES2 job size field matching Windows ... SRICOBSAS All Other Mainframe Topics 4 Tue Sep 05, 2017 5:49 pm
No new posts Julian Date to CICS ABSTTIME blayek CICS 3 Wed Aug 30, 2017 11:15 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us