Converting 2000-12-06:00:00:00.00 to DEC 6, 2000 3

[campbere]

I have a query file that produces a column called conversion_date. The values for this column are
2000-12-06:00:00:00.00 for the reporting purposes I would like to have it displayed as DEC 6, 2000.

How is this done? I have been going up and down with all the date functions and haven't found the right one.

I had one suggestion to do something like this:

totext(date(left({Query.CONVERSION_DATE},4),
mid((Query.CONVERSION_DATE},6,2),
mid((Query.CONVERSION_DATE},9,2),"MMM dd, yyyy&quot

But I was getting parenthesis errors and when I didn't get that I got this function has too many arguements.

I appreciate the help.

 

[jjames]

This should work as is...

totext(
date(
val(left({Query.CONVERSION_DATE},4)),
val(mid({Query.CONVERSION_DATE},6,2)),
val(mid({Query.CONVERSION_DATE},9,2))
),
"MMM dd, yyyy&quot

 

[balves]

Maybe I'm missing something but...

Why don't you just format the field on the report?

 

[HiHeidi]

I agree.. can't you just right click the field and reformat the date?

 

[campbere]

I can't simply format the field on the report because I don't have the option. I believe this is because the field is from a qry file.

 

[Jinny]

I would try using;

CDATE ({query.conversion_date} [1 to 10])


then you can format your field to print the date as you would like to see it.

Judi

 

[Hm786]

Hi,
If the field is created form an sql query, then you can format the field in the query designer by using this syntax
to_char(to_date(substr('2000-12-06:00:00:00.00 ',1,10),'YYYY-MM-DD'),'Mon dd,yyyy')this will give you the exact output you are looking for. You cannot format the query field in the report designer. I had the problems before so I always format in the query designer and it works fine.

Hamida