Convert String Elements to Date

[socalvelo]

CR11
Oracle DB
Desired Result: Extract date from a system generated non numeric string to use in report's record selection.

Fields: Call_No (string)
Incident_Date (DateTime)

I have unique system generated non-numeric string which references a call/incident. The string looks like this: 093270694. The first two digits are the year, the next three are the day of the year (in this case Nov 23) and the last four digits are a unique call number for that day).

The first thing I would like to do is write a formula to convert the first five digits into a date. I'm having some trouble with the day of year part.

I use a parameter for the call number in the report's record selection editor. I also use a parameter for the date which I want to eliminate. The reason I enter the date is for speed (millions of call numbers over several years). By limiting it to the exact date, the report returns the record immediately.

The second step is to insert the date formula from the Call_No string into my selection record.

 

[lbass]

Try the following:

stringvar x := {table.string};
dateadd("d",val(mid(x,3,3)),date(2000+val(left(x,2)),1,1))-1;

Not sure why you want to eliminate the parameter. I would think you would want to set up a date parameter, and then use a record selection formula like:

{@aboveformula} = {?date}

-LB

 

[socalvelo]

LB, thank you for the quick response and solution.
The formula works great. In my statement about not wanting a date parameter, I meant to say I did not want to manually enter the date when the report runs, just the call number. So the solution you gave me for the record selection was what I was also looking for.

I am having a problem with using the formula and the parameter field in the record selection though. In my case it is {@Call_Date}={?date}, as I would like the date formula to carry over to this. It seems to go into an extremely long search or a loop. If I enter a date manually though a parameter, my printed report returns immediately.

 

[lbass]

The problem is that the conversion is occurring locally. If you could create a SQL expression to convert the string to a date, it would be much faster.

-LB

 

[SkipVought]

I'd use this technique, however it applies to CR...
[tt]
DateSerial(20+val(Mid(x,1,2)),0,val(Mid(x,3,3)))
[/tt]
Ought to be able to use something like this in a query also.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lbass]

Go into the field explorer->SQL expression->new and try creating a SQL expression {%calldate} like this:

to_date('20'||{fn substring("table"."string",1,2)}||'-01-01')+to_number({fn substring("table"."string",3,3)})

Substitute your string field from the field list for "table"."string".

Then change the record selection formula to:

{%calldate} = {?Date}

This should make the report faster.

-LB

 

[socalvelo]

LB,
I know very little about writing SQL code and I am getting an error.

I entered the following in the SQL Editor:

to_date('20'||{fn substring("CSMAST_VIEW"."Call_No",1,2)}||'-01-01')+to_number({fn substring("CSMAST_VIEW"."Call_No",3,3)})

I have an error---> ORA-08161:literal does not match format string. This is coming from the first half of the expression.

Thanks

 

[SkipVought]

TO_DATE has the following syntax...

TO_DATE([i]DateString[/i],[i]DateStringFORMAT[/i])

so...

to_date('20'||{fn substring("CSMAST_VIEW"."Call_No",1,2)}||'-01-01'[b],'yyyy-mm-dd'[/b])+.........

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[socalvelo]

Now I'm receiving an ORA-00911:Invalid character error

I tried to decode it but can't figure out which character it doesn't like. I tried typing the expression in manually (rather than pasting) and it didn't help.

to_date('20'||{fn substring("CSMAST_VIEW"."Call_No",1,2)}||'-01-01','yyyy-mm-dd')+ to_number({fn substring("CSMAST_VIEW"."Call_No",3,3)})

 

[SkipVought]

Have you tried ONLY the TO_DATE or ONLY the TO_NUMBER function in the sql? Do they BOTH fail independently?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[socalvelo]

Yes. The to_number portion works fine.
If I didn't state earlier, this is a ODBC connection to Oracle

 

[SkipVought]

Have you listed ALL your Call_No values? I'd particularly want to see a DISTINCT list of the first 2 characters...

Select DISTINCT {fn substring("CSMAST_VIEW"."Call_No",1,2)}
From ....

What are these values?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lbass]

Can you paste in the actual SQL expression as used in your report? I don't see anything wrong with it after Skip's correction--although I forgot that there should be a -1 a the end of the expression. I tried this with Oracle and an ODBC connection, and it worked here.

But can the string ever be null or = ""?

-LB

 

[socalvelo]

LB / Skip - I found one record that has a null field in "Call_No". This is in 2005 and these records go back to 2002.

This is the exact SQL expression I am attempting. Note that there are no other SQL expressions in this report.
to_date('20'||{fn substring("CSMAST_VIEW"."Call_No",1,2)}||'-01-01','yyyy-mm-dd')+ to_number({fn substring("CSMAST_VIEW"."Call_No",3,3)})

As requested, distinct fields in "Call_No" for the first and second digits:

null (only one null field in the report, no "" fields come up)
02
03
04
05
06
07
08
09

I appreciate your assistance and patience with me as I am not trained with sql expressions.

 

[lbass]

Okay, I think the null might be the problem, although I'm not sure why this would trigger an invalid character message, but try this anyway:

to_date('20'||{fn substring({fn ifnull("CSMAST_VIEW"."Call_No",'99009')},1,2)}||'-01-01','yyyy-mm-dd')+ to_number({fn substring({fn ifnull("CSMAST_VIEW"."Call_No",'99009')},3,3)})-1

Substitute whatever value you want to be the default in case of a null for '99009'. Remember to add the -1 at the end.

You might also check whether the {fn substring(,,)} works with your datasource. In some cases substr(,,) is used instead.

-LB