Convert text field to date field in Cognos

At this time I am unable to do this in the database.  Our usage must occur in Cognos.

I'm having trouble find much detail on this function.  Can you provide example syntax?

It would help if we knew what kind of database you're working with.  However, there are cognos functions that will work too.

This gets you the two characters preceeding the first /:
substring([myDate],Position('/',[myDate])-2,2)

This gets you the characters between the /:
substring([myDate],Position('/',[myDate])+1,Position('/',substring([myDate],position('/',[myDate])+1)))

This gets you the two characters following the second /:
substring([myDate],position('/',[myDate])+Position('/',substring([myDate],position('/',[myDate])+1)),2)

Put 'em all together:
_make_timestamp(
  cast(substring([myDate],Position('/',[myDate])-2,2),integer),
  cast(substring([myDate],Position('/',[myDate])+1,Position('/',substring([myDate],position('/',[myDate])+1))),integer),
  cast(substring([myDate],position('/',[myDate])+Position('/',substring([myDate],position('/',[myDate])+1)),2),integer)

Of course, this will throw an error if any of your fields have fewer then two /, or have a non-numberic in the two spaces before the first slash or after the second, or if any of them aren't in year/month/day format or contain invalid numbers.  Other then that, it's bulletproof.

I started just trying out the first function, and I'm sure I'm doing something wrong.  If you look at the screenshot below, you'll see what I'm trying to do.  The "Referral_Auth_Notes" is the field I'm trying to parse.  When I execute this, it errors out.

By the way, I'm using MS SQL server 2005.

Do any of the records lack a '/'?  What is the actual error message?

Here's what I'm getting....

Your Cognos administrator need to look at the log files and give you the complete error message.

I attached a snippet from the log.  I'm hoping that I don't need to create a ticket with my vendor to fix this problem.  Any thoughts?

As Rwrigley pointed out in his earlier posts there are a number of issues here.  Besides the ones he already pointed out the most problamatic from your point of view is the year being either 2 or 4 digits. This will not throw an error but will give you corrpt data.   If you really have to attempt this I would first extract each piece into its own data item.

Once you have done that you can clean up each item before converting them to a number.  ONce you have cleaned each section you should be able to convert to a date without errors.   Here is an example of some possible clean up for the first part.For example
Define  date_Part_1 =
substring ([Referral_Auth_Notes],Position('/',[Referral_Auth_Notes])-2,2)
Then Define Data_Part_1_Clean

IF ( [date_Part_01] = '01' or [date_Part_01] = ' 1' or [date_Part_01] = '1 ') THEN  ( '01' ) ELSE
IF ( [date_Part_01] = '02' or [date_Part_01] = ' 2' or [date_Part_01] = '2 ') THEN  ( '02' ) ELSE
IF ( [date_Part_01] = '03' or [date_Part_01] = ' 3' or [date_Part_01] = '3 ') THEN  ( '03' ) ELSE
.
.
.
IF ( [date_Part_01] = '09' or [date_Part_01] = ' 9' or [date_Part_01] = '9 ') THEN  ( '09' ) ELSE
    ( '00' )

The only thought I have on your error is you need to check for Null values before you perform your substring.  

If  ( [Referral_Auth_Notes] <> null) then (substring ([Referral_Auth_Notes],position('/', [Referral_Auth_Notes])-2,2)) else ("00")

Your first suggestion looks good, but I can't seem to make it functional.  The next post gives me this error:

Parsing error before or near position: 131 of: "If ( [Referral_Auth_Notes] <> null) then (substring ([Referral_Auth_Notes],position('/', [Referral_Auth_Notes])-2,2)) else ("00")"

Sorry typo on my part use single quote at the end

else ('00')

Thanks for all your help so far.  I should have thought of that typo, especially considering I just recently had to deal with that.

I'm still getting an error.

DPR-ERR-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2008-12-02-15:31:21.451-#14

The log file is attached.

Hmmm I substituted a field from one of my pakages and it works fine.  Try this create a new report(do not reuse the report you have been working on).  Bring two items into the report a key field and Referral_Auth_Notes.  Filter the report so you get one record that has a date in Referral_Auth_Notes.  Drag a new Query calculationquery calculation into the report after Referral_Auth_Notes and name it Date_Part_1.   Copy and past the calculation below into the expression editor.  Run the report.

If ( [Referral_Auth_Notes] <> null) then (substring ([Referral_Auth_Notes],position('/', [Referral_Auth_Notes])-2,2)) else ('00')

Sorry for the delay on my response, I've been on another project.

Wow, that works!  It is pulling the '12' out of 12/xx/xxxx.  How do I pull the other dates out?  Thanks for your help!

Modified from above comment by Rwrigley

2md part
If ( [Referral_Auth_Notes] <> null) then
substring([Referral_Auth_Notes],Position('/', [Referral_Auth_Notes] )+1,Position('/',substring([Referral_Auth_Notes] ,position('/',[Referral_Auth_Notes])+1)))

3rd part
If ( [Referral_Auth_Notes] <> null) then
substring( [Referral_Auth_Notes],position('/', [Referral_Auth_Notes] )+Position('/',substring([Referral_Auth_Notes],position('/',[Referral_Auth_Notes])+1)),2)

It's erroring out.  I know it's because I'm stupid.  See pic.

I ultimately want to filter date ranges, so I need to piece these pieces together.  Thanks.

You have to create the three variables separately and then put them together because your data is so diverse.   You will also have to dedide what to do with the dates that are missing.  

So once you have created three variables Date_Part1, Date_Part2, and Date_Part3.  You will need to clean them up.  Ie remove any that are not numbers or are null.   See the code I posted above for Date_Part_clean.  For month you will need to check for 1 - 12 for day 1-31 and for year well you get the idea.  

Only once you have eliminated any non-nummeric or invalid characters can you glue the pieces together and do a cast to make it a date.

Also I noticed I typed ,2 on the last date part when that one should be ,4 since you said the year is 4 digits sometimes.

Thanks *so* much so far.  There's a error somewhere in the last 2 parsers.
Here's the error:

192.168.10.246:9300      5584      2008-12-08 15:04:53.901      -5                  0      0      5284      QOS      1166      1      Audit.RTUsage.QOS            <message code="901" location="/qs:command/validate/querySet/queries/query/selection/dataItem[4]/expression" severity="error" title="QE-DEF-0459 CCLException" type="expression">QE-DEF-0260 Parsing error before or near position: 51 of: &quot;If ( [Referral_Auth_Notes] <> null) then
substring&quot;
QE-DEF-0261 QFWP - Parsing text: If ( [Referral_Auth_Notes] <> null) then
substring( [Referral_Auth_Notes],position(&apos;/&apos;, [Referral_Auth_Notes] )+Position(&apos;/&apos;,substring([Referral_Auth_Notes], Position(&apos;/&apos;,[Referral_Auth_Notes])+1)),2)
</message>

By the way, can you recommend any good online resources or books on learning more about Cognos?

If ( [Referral_Auth_Notes] <> null) then
(substring( [Referral_Auth_Notes],position('/', [Referral_Auth_Notes] )+Position('/',substring([Referral_Auth_Notes],position('/',[Referral_Auth_Notes])+1)),4)) else ('NoGo')

Brackets on if then else are wrong.  Try this

Here is PArt 2 with the correct backets I hope.  I am just doing this on the fly.
Define Date_Part_2

If ( [Referral_Auth_Notes] <> null) then
(substring([Referral_Auth_Notes],Position('/', [Referral_Auth_Notes] )+1,Position('/',substring([Referral_Auth_Notes] ,position('/',[Referral_Auth_Notes])+1)))) else ('No')

Great.  I now can capture the first and second part of the date, what's the last field?  Can you explain what's going on within the code?  I could finish it up if I understood it.  Thanks.