IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Date in where clause - Windows


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Nov 16, 2017 12:45 am
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
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Nov 16, 2017 12:54 am
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
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

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Nov 16, 2017 1:50 am
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: 2455
Location: Hampshire, UK

PostPosted: Thu Nov 16, 2017 3:21 pm
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Need to convert date format DFSORT/ICETOOL 20
No new posts Need help to append a date&tsp at... DFSORT/ICETOOL 9
No new posts Fetch data from programs execute (dat... DB2 3
No new posts Insert system time/date (timestamp) u... DFSORT/ICETOOL 5
Search our Forums:

Back to Top