​I'm trying to convert a string (that should be a date). But I keep getting errors like:
ORA-01830: date format picture ends before converting entire input string
RSV-SRV-0042 Trace back:

or

RSV-SRV-0063 An error occurred while executing the 'asynchRunSpecification_Request' command. XQE-DAT-0001 Data source adapter error: java.sql.SQLDataException: ORA-01843: not a valid month

How can I get around this?

Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------

#CognosAnalyticswithWatson

We have a MS SQL Server database and this works in the report expression.

If (isdate([Demand Deposits / Savings].[Dates].[Charge Off Date]) = -1) then
([Demand Deposits / Savings].[Dates].[Charge Off Date])
else
(NULL)

But I will say that for all of our date fields/columns we check the for validity prior to creating a view with a datetime type column. Sometimes the customers have some half cooked column data that might be a date because it is user free form comments and they use the ISDATE.

what is your backend database? What does the raw data look like and what does your report expression look like that is giving the errors?

------------------------------
brenda grossnickle
------------------------------

Hi Brenda,
Thanks for the quick reply.
The isDate doesn't work.

Its an Oracle DB.  It's set as Type Alpha(40,0) with Data Type characterLength 16​

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Mon November 04, 2019 04:08 PM
From: brenda grossnickle
Subject: String(date) to real date to use in filter??

We have a MS SQL Server database and this works in the report expression.

If (isdate([Demand Deposits / Savings].[Dates].[Charge Off Date]) = -1) then
([Demand Deposits / Savings].[Dates].[Charge Off Date])
else
(NULL)

But I will say that for all of our date fields/columns we check the for validity prior to creating a view with a datetime type column. Sometimes the customers have some half cooked column data that might be a date because it is user free form comments and they use the ISDATE.

what is your backend database? What does the raw data look like and what does your report expression look like that is giving the errors?

------------------------------
brenda grossnickle
------------------------------

@Jenifer Broughton My guess is the string column that is suppose to be dates has invalid date formats for some of the rows. To test this, try to filter down to a couple of rows and see if you can cast the string column to a date with a limited result set.​

------------------------------
DENNY NAREZNY
------------------------------

Original Message:
Sent: Mon November 04, 2019 04:39 PM
From: Jenifer Broughton
Subject: String(date) to real date to use in filter??

Hi Brenda,
Thanks for the quick reply.
The isDate doesn't work.

Its an Oracle DB.  It's set as Type Alpha(40,0) with Data Type characterLength 16​

------------------------------
Jenifer Broughton
------------------------------


Original Message:
Sent: Mon November 04, 2019 04:08 PM
From: brenda grossnickle
Subject: String(date) to real date to use in filter??

We have a MS SQL Server database and this works in the report expression.

If (isdate([Demand Deposits / Savings].[Dates].[Charge Off Date]) = -1) then
([Demand Deposits / Savings].[Dates].[Charge Off Date])
else
(NULL)

But I will say that for all of our date fields/columns we check the for validity prior to creating a view with a datetime type column. Sometimes the customers have some half cooked column data that might be a date because it is user free form comments and they use the ISDATE.

what is your backend database? What does the raw data look like and what does your report expression look like that is giving the errors?

------------------------------
brenda grossnickle

The field is actually a 'user defined field' that has some text and dates in it.  We are filtering out text.  We need to use the date to compare for prior 6 months.  ​

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Tue November 05, 2019 08:19 AM
From: DENNY NAREZNY
Subject: String(date) to real date to use in filter??

@Jenifer Broughton My guess is the string column that is suppose to be dates has invalid date formats for some of the rows. To test this, try to filter down to a couple of rows and see if you can cast the string column to a date with a limited result set.​

------------------------------
DENNY NAREZNY
------------------------------


Original Message:
Sent: Mon November 04, 2019 04:39 PM
From: Jenifer Broughton
Subject: String(date) to real date to use in filter??

Hi Brenda,
Thanks for the quick reply.
The isDate doesn't work.

Its an Oracle DB.  It's set as Type Alpha(40,0) with Data Type characterLength 16​

------------------------------
Jenifer Broughton


Original Message:
Sent: Mon November 04, 2019 04:08 PM
From: brenda grossnickle
Subject: String(date) to real date to use in filter??

We have a MS SQL Server database and this works in the report expression.

If (isdate([Demand Deposits / Savings].[Dates].[Charge Off Date]) = -1) then
([Demand Deposits / Savings].[Dates].[Charge Off Date])
else
(NULL)

But I will say that for all of our date fields/columns we check the for validity prior to creating a view with a datetime type column. Sometimes the customers have some half cooked column data that might be a date because it is user free form comments and they use the ISDATE.

what is your backend database? What does the raw data look like and what does your report expression look like that is giving the errors?

------------------------------
brenda grossnickle

Can you post an example of the filter you're using?

If you're using the to_date function, it needs to match the date string you're passing to it. A timestamp would fail if you're only referencing the date in the mask. 

Try either of the following:
[Date] >= to_date('2019-01-01 05:55 PM','YYYY-MM-DD HH:MI PM')

[Date] = to_date(#sq(timestampMask($current_timestamp,'yyyy-mm-dd'))#,'YYYY-MM-DD')


------------------------------
Paul Mendelson
------------------------------

Hi @Paul Mendelson I tried the with the following errors:

1st option above and received this message:
XQE-DAT-0001 Data source adapter error: java.sql.SQLDataException: ORA-01861: literal does not match format string  (I've tried tweaking)

2nd options above and received this message:
XQE-DAT-0005 Cannot convert the string value '' to data type timestamp with time zone.​​

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Mon November 04, 2019 10:05 PM
From: Paul Mendelson
Subject: String(date) to real date to use in filter??

Can you post an example of the filter you're using?

If you're using the to_date function, it needs to match the date string you're passing to it. A timestamp would fail if you're only referencing the date in the mask. 

Try either of the following:
[Date] >= to_date('2019-01-01 05:55 PM','YYYY-MM-DD HH:MI PM')

[Date] = to_date(#sq(timestampMask($current_timestamp,'yyyy-mm-dd'))#,'YYYY-MM-DD')


------------------------------
Paul Mendelson
------------------------------

Can you post an example of what your date is returning? Based on the error it looks like a TIMESTAMP WITH TIMEZONE datatype, which I've never worked with unfortunately. 

Based on the Oracle documentation you'll probably need to use the TO_TIMESTAMP_TZ function. Without access to an Oracle instance, this will be difficult for me to get right without a bit of trial and error. 

Try something like this:

TO_TIMESTAMP_TZ(#sq($current_timestamp)#,'YYYY-MM-DD H24:MI:SS.FF3-TZH:TZM')

#sq($current_timestamp)# returns a string that looks like '2019-11-05 11:49:09.010-06:00' and I'm pretty sure I got the flags right for the data mask.​

------------------------------
Paul Mendelson
------------------------------

Original Message:
Sent: Tue November 05, 2019 12:31 PM
From: Jenifer Broughton
Subject: String(date) to real date to use in filter??

Hi @Paul Mendelson I tried the with the following errors:

1st option above and received this message:
XQE-DAT-0001 Data source adapter error: java.sql.SQLDataException: ORA-01861: literal does not match format string  (I've tried tweaking)

2nd options above and received this message:
XQE-DAT-0005 Cannot convert the string value '' to data type timestamp with time zone.​​

------------------------------
Jenifer Broughton
------------------------------


Original Message:
Sent: Mon November 04, 2019 10:05 PM
From: Paul Mendelson
Subject: String(date) to real date to use in filter??

Can you post an example of the filter you're using?

If you're using the to_date function, it needs to match the date string you're passing to it. A timestamp would fail if you're only referencing the date in the mask. 

Try either of the following:
[Date] >= to_date('2019-01-01 05:55 PM','YYYY-MM-DD HH:MI PM')

[Date] = to_date(#sq(timestampMask($current_timestamp,'yyyy-mm-dd'))#,'YYYY-MM-DD')


------------------------------
Paul Mendelson

This is an expression which walks through a string (in this case a
structure of  20191104 ) , builds up the elements of a date, and then
casts the value to date.   It might be helpful for you to use as a
pattern for your expression if your time information is in a predictable location.

cast (
substring(
cast(DATE_KEY as varchar(8)),1,4 )
||  '-'
 +
substring(
cast(DATE_KEY as varchar(8)),5,2 )
 || '-'
substring(
cast(DATE_KEY as varchar(8)),7,2 )
, date)

------------------------------
IAN HENDERSON
------------------------------

Original Message:
Sent: Tue November 05, 2019 12:31 PM
From: Jenifer Broughton
Subject: String(date) to real date to use in filter??

Hi @Paul Mendelson I tried the with the following errors:

1st option above and received this message:
XQE-DAT-0001 Data source adapter error: java.sql.SQLDataException: ORA-01861: literal does not match format string  (I've tried tweaking)

2nd options above and received this message:
XQE-DAT-0005 Cannot convert the string value '' to data type timestamp with time zone.​​

------------------------------
Jenifer Broughton
------------------------------


Original Message:
Sent: Mon November 04, 2019 10:05 PM
From: Paul Mendelson
Subject: String(date) to real date to use in filter??

Can you post an example of the filter you're using?

If you're using the to_date function, it needs to match the date string you're passing to it. A timestamp would fail if you're only referencing the date in the mask. 

Try either of the following:
[Date] >= to_date('2019-01-01 05:55 PM','YYYY-MM-DD HH:MI PM')

[Date] = to_date(#sq(timestampMask($current_timestamp,'yyyy-mm-dd'))#,'YYYY-MM-DD')


------------------------------
Paul Mendelson

Another problem is, the day and month are not always 2 characters.  I've had to find the '/' position to parse things out.  It's not ideal.  ​

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Tue November 05, 2019 12:53 PM
From: IAN HENDERSON
Subject: String(date) to real date to use in filter??

This is an expression which walks through a string (in this case a
structure of  20191104 ) , builds up the elements of a date, and then
casts the value to date.   It might be helpful for you to use as a
pattern for your expression if your time information is in a predictable location.

cast (
substring(
cast(DATE_KEY as varchar(8)),1,4 )
||  '-'
 +
substring(
cast(DATE_KEY as varchar(8)),5,2 )
 || '-'
substring(
cast(DATE_KEY as varchar(8)),7,2 )
, date)

------------------------------
IAN HENDERSON
------------------------------


Original Message:
Sent: Tue November 05, 2019 12:31 PM
From: Jenifer Broughton
Subject: String(date) to real date to use in filter??

Hi @Paul Mendelson I tried the with the following errors:

1st option above and received this message:
XQE-DAT-0001 Data source adapter error: java.sql.SQLDataException: ORA-01861: literal does not match format string  (I've tried tweaking)

2nd options above and received this message:
XQE-DAT-0005 Cannot convert the string value '' to data type timestamp with time zone.​​

------------------------------
Jenifer Broughton


Original Message:
Sent: Mon November 04, 2019 10:05 PM
From: Paul Mendelson
Subject: String(date) to real date to use in filter??

Can you post an example of the filter you're using?

If you're using the to_date function, it needs to match the date string you're passing to it. A timestamp would fail if you're only referencing the date in the mask. 

Try either of the following:
[Date] >= to_date('2019-01-01 05:55 PM','YYYY-MM-DD HH:MI PM')

[Date] = to_date(#sq(timestampMask($current_timestamp,'yyyy-mm-dd'))#,'YYYY-MM-DD')


------------------------------
Paul Mendelson

Hi Jenifer,
Could you post a collection of the string values that you're trying to convert ?
The solution will likely involve a number of different functions for the various cases that are present.

//Henk

------------------------------
HENK CAZEMIER
------------------------------

Original Message:
Sent: Tue November 05, 2019 01:42 PM
From: Jenifer Broughton
Subject: String(date) to real date to use in filter??

Another problem is, the day and month are not always 2 characters.  I've had to find the '/' position to parse things out.  It's not ideal.  ​

------------------------------
Jenifer Broughton
------------------------------


Original Message:
Sent: Tue November 05, 2019 12:53 PM
From: IAN HENDERSON
Subject: String(date) to real date to use in filter??

This is an expression which walks through a string (in this case a
structure of  20191104 ) , builds up the elements of a date, and then
casts the value to date.   It might be helpful for you to use as a
pattern for your expression if your time information is in a predictable location.

cast (
substring(
cast(DATE_KEY as varchar(8)),1,4 )
||  '-'
 +
substring(
cast(DATE_KEY as varchar(8)),5,2 )
 || '-'
substring(
cast(DATE_KEY as varchar(8)),7,2 )
, date)

------------------------------
IAN HENDERSON


Original Message:
Sent: Tue November 05, 2019 12:31 PM
From: Jenifer Broughton
Subject: String(date) to real date to use in filter??

Hi @Paul Mendelson I tried the with the following errors:

1st option above and received this message:
XQE-DAT-0001 Data source adapter error: java.sql.SQLDataException: ORA-01861: literal does not match format string  (I've tried tweaking)

2nd options above and received this message:
XQE-DAT-0005 Cannot convert the string value '' to data type timestamp with time zone.​​

------------------------------
Jenifer Broughton


Original Message:
Sent: Mon November 04, 2019 10:05 PM
From: Paul Mendelson
Subject: String(date) to real date to use in filter??

Can you post an example of the filter you're using?

If you're using the to_date function, it needs to match the date string you're passing to it. A timestamp would fail if you're only referencing the date in the mask. 

Try either of the following:
[Date] >= to_date('2019-01-01 05:55 PM','YYYY-MM-DD HH:MI PM')

[Date] = to_date(#sq(timestampMask($current_timestamp,'yyyy-mm-dd'))#,'YYYY-MM-DD')


------------------------------
Paul Mendelson