text date to a date value

[matpj]

I have a table that stores the date in text format as the following: DD MMM YY
e.g. 04 JUN 05

my report needs to group by date, preferably by year, and then month.
how can I split this?
I'm not worried about the DD section, but if there is a way of forcing the value as a date then that would be great.

thanks,

Matt

 

[ts04]

DD MMM YY

Create the following fields

@Day: mid({Table.DateField},1,2)
@Month mid({Table.DateField},3,3)
@Year mid({Table.DateField},6,2)

The mid function extracts characters from your string. The first number represents the starting point and the second number the number of characters it needs to extract.

Now sort on @Year, @Month, @Day as you like.

Note: If there are spaces in your data-field you have to count them in to. I have assumed that the data looks like this: DDMMMYY

Tanja

 

[matpj]

hi Tanja,

thanks for that, i'll give it a go, although, this will sort it in alphabetical month order, rather than chronologically.

is there a way I can make combine all 3 seperate fields into a data field?

 

[matpj]

I have worked out that it is possible to specify a sort order, which I have done.

I'm now trying to turn the year component into a full year.
Currently, I now have a numeric '03' or '04' etc in the grouping.

I have tried to create a field that says:

If {@Year} = 03 then "2003";

but it doesn't work
do you have any other ideas?

 

[GJParker]

Try using

CDate(Replace('05 JUN 04' , ' ', '/'))

This should give you what you need.






Gary Parker
MIS Data Analyst
Manchester, England

 

[matpj]

Garym,
thanks gary, using it in your format above works, but when I try to use the field in place of '05 JUN 2004' it fails.

I'm qualifying the lenght of the date field first though, as sometimes there exists 'tba' or "ON HOLD" in there.
so I am trying:

IF (len{Date} > 8) then <your function>
else <99/99/9999>

it does not work though, as the len function complains that it requires a string?!?!?!?
does this make sense?

 

[ts04]

I have made a test run and this works:

Create a formulafield @Convert_Date (Crystal Syntax):

Stringvar MonthTemp := convert_Formula(mid({@Datum},3,3));
StringVar YearTemp := "20" + mid({@Datum},6,2);

Date(Val(YearTemp), Val(MonthTemp), Val({@Datum}[1 to 2]))

Add the custom function convert_Formula (Basic-syntax):

Function Convert_date (Month as string) as string

select case Month

case "JAN"
Convert_date = "01"
case "FEB"
Convert_date = "02"
case "MAR"
Convert_date = "03"
case "APR"
Convert_date = "04"
case "MAY"
Convert_date = "05"
case "JUN"
Convert_date = "06"
case "JUL"
Convert_date = "07"
case "AUG"
Convert_date = "08"
case "SEP"
Convert_date = "09"
case "OKT"
Convert_date = "10"
case "NOV"
Convert_date = "11"
case "DEC"
Convert_date = "12"

End select

End Function

Tanja

 

[ts04]

I have made a test run and this works:

Create a formulafield @Convert_Date (Crystal Syntax):

Stringvar MonthTemp := convert_Formula(mid({@Datum},3,3));
StringVar YearTemp := "20" + mid({@Datum},6,2);

Date(Val(YearTemp), Val(MonthTemp), Val({@Datum}[1 to 2]))

Add the custom function convert_Formula (Basic-syntax):

Function Convert_Formula (Month as string) as string

select case Month

case "JAN"
Convert_date = "01"
case "FEB"
Convert_date = "02"
case "MAR"
Convert_date = "03"
case "APR"
Convert_date = "04"
case "MAY"
Convert_date = "05"
case "JUN"
Convert_date = "06"
case "JUL"
Convert_date = "07"
case "AUG"
Convert_date = "08"
case "SEP"
Convert_date = "09"
case "OKT"
Convert_date = "10"
case "NOV"
Convert_date = "11"
case "DEC"
Convert_date = "12"

End select

End Function

Tanja

 

[ts04]

You need the last of the two posts

Tanja

 

[GJParker]

Try something like this

IF (len{Table.Date} > 8) then
CDate(Replace({Table.Date} , ' ', '/'))
Else
CDate('9999/99/99')

Replace {Table.Date} with your database field, this must be a string for the LEN() function to work

HTH


Gary Parker
MIS Data Analyst
Manchester, England