View previous topic :: View next topic
Author
Message
Karthikeyan Subbarayan New User Joined: 24 Feb 2008Posts: 62 Location: Boston
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
Karthikeyan Subbarayan New User Joined: 24 Feb 2008Posts: 62 Location: Boston
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
Karthikeyan Subbarayan New User Joined: 24 Feb 2008Posts: 62 Location: Boston
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
Rohit Umarjikar Global Moderator Joined: 21 Sep 2010Posts: 3048 Location: NYC,USA
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
Karthikeyan Subbarayan New User Joined: 24 Feb 2008Posts: 62 Location: Boston
@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
Rohit Umarjikar Global Moderator Joined: 21 Sep 2010Posts: 3048 Location: NYC,USA
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
Karthikeyan Subbarayan New User Joined: 24 Feb 2008Posts: 62 Location: Boston
@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
Rohit Umarjikar Global Moderator Joined: 21 Sep 2010Posts: 3048 Location: NYC,USA
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
Nic Clouston Global Moderator Joined: 10 May 2007Posts: 2455 Location: Hampshire, UK
I would also check the manual to see how DB2 for Windoze differs from DB2 for mainframe.
Back to top
Karthikeyan Subbarayan New User Joined: 24 Feb 2008Posts: 62 Location: Boston
Thanks Nic
Back to top
Please enable JavaScript!