datetime to date field

[Crystalguru]

Version 8.5
oracle 8i

I am trying to convert a datetime field START_DT to a date field. I am using a SQL Expression.

I have tried:
trun
cast
to_date

They all work BUT when I put the field on the report, Crystal still is showing a datetime field. What's the deal here?

 

[hneal98]

In Crystal, right click on the field and select format field, then under the date/time tab, there is a drop down box called Order:. In that box choose Date.

 

[fredp1]

In Report Options, you can get CR to convert Datetime fields to Date/Datetime/String.

Will this do?

Fred

 

[Crystalguru]

Yes, the Order tab would work if I want it to print as a date. But I need the field to actually be a DATE field when I convert it in a SQL Expression.

For example:
SQL EXpression:
TO_DATE(T0."START_DT",'DD-MM-RRRR')

Put the SQL expression on my report. Still shows as a datetime field.

??

 

[Crystalguru]

Fredp1,

Yes, that would work also, if I wasn't using the field on the report for it's time.

It's crazy.

 

[hneal98]

Datetime field is a date field. I am not sure what you are getting at. Of course it will still show as a datetime field. Where are you viewing it from?

 

[Crystalguru]

From the Orace database, the field Start_dt is a datetime data type.

In a SQL Expression, I want to convert the Start_dt field to a DATE field. NO TIME.

I can do this using the truncate function from Oracle.

When I put the field on the report, it still is a datetime field. Why?

 

[fredp1]

I'm not sure what your trying to get at as well.If you use the report options to convert the datetime field to a date, it will display the field a a date field (with no time).
As hneal98 mentioned, you can also format the field to show date only. Take your pick.

As far as I can understand, you do not need a sql expression.
Are you using the field for someother calc?
Cheers

 

[Crystalguru]

Yup, using the START_dT field in the Select expert to go against ?Start Date and ?End Date. THese two prompts must be DATE types. So in order to use the start_dt field, I need to convert it to a date field. This is where this all started.

 

[lbass]

I think you just need to use:

Date({table.Start_dt}) in {?Start Date} to {?End Date}

-LB

 

[fredp1]

Now I see where your going with this.
Lbass solution is works but I don't think the SQL it will get passed to the server, so all the work will be done on the client.

Your original thought of a sql expression was on the right track. here is a solution.

SQL Expression similar to this
//%Start_DT_SQL_Expresion
convert(dateTime, cast(month(getDate()) as char(2)) + '/' + cast(day(getDate()) as char(2)) + '/' + cast(year(getDate()) as char(4)))
(Im using the getdate() function as a date) your solution will be...
convert(dateTime, cast(month(table."START_DT ") as char(2)) + '/' + cast(day(table."START_DT ") as char(2)) + '/' + cast(year(table."START_DT ") as char(4)))

Then your record selection formula will be
{%Start_DT_SQL_Expresion} in {?Start Date} to {?End Date}

This SQL will then pass to the Server for Server side processing making the report run faster.

Cheers

 

[Crystalguru]

You are on the right track. But still stuck. What you have won't work on Oracle 8i ...
convert(dateTime, cast(month(table."START_DT ") as char(2)) + '/' + cast(day(table."START_DT ") as char(2)) + '/' + cast(year(table."START_DT ") as char(4)))

The cast function needs its format like this:
cast(start_dt as date)

Which gives me exactly what I am looking for. BUT! And here's the slammer. When I put the SQL Expression on the report, it is still a datetime type? That's what is throwing me off.

I can take a number field, ie. Duration datatype = number and do a SQL Expression:
to_char(duration)

PUt it on the report and it becomes a string.

Now WHY doesn't that happen to the START_DT??????

ARGH>

 

[fredp1]

Bugger...

The solution I posted was for SQL Server. (oops!)

The reason I suspect that Start_DT shows a date only, is the parameter is defined as a Date type, so CR formats it as a Date(No time).
When the paramater is a datetime type, it shows the datetime.

With the sql expression if you place that on your report CR will default to a format of Datetime. (I;ve tried it with SQL server). The thing to note is that the time is 12:00am.

If you format the field, you can remove the time from being displayed. If you format, Customize, Date/Time, Order=Date you will not 'see' the time on the report.

I suppose what I'm saying, is I don't understand/believe that it the real problem.

Is it interfering with some other calc?

the sql expression with the cast function looks like this
//%Start_DT_SQL_Expresion
cast(cast(month(table1."START_DT") as char(2)) + '/' + cast(day(table1."START_DT") as char(2)) + '/' + cast(year(table1."START_DT") as char(4)) as datetime)

After you format the field is that okay?

Cheers
Fred

 

[Crystalguru]

Well, here is what I came up with. I haven't figured out why converting the datetime field to a date still shows as datetime on the report, but I have worked around it.

In my select expert:

{T0.START_DT} IN CDateTime(Year({?StartDate}),Month({?StartDate}),Day({?StartDate}),0,0,0) TO
CDateTime(Year({?EndDate}),Month({?EndDate}),Day({?EndDate}),0,0,0)

Not what I started with but it gets me the server side processing I was looking for.

Always two ways to skin a cat.


thanks for your help

 

[fredp1]

I've seen so many solutions posted which cause the SQL to be run on the client rather than the server side.

I've had some CD consultants do some contract work for me that created reports which ran on the client side.
It wasn't much of an issue to get it run on the server side.

It good to see that you have perservered and posted your solution.

Cheers