Convert Datetime to Date 1

[Viscount]

Afternoon All,

I have got 4 fields that I wish to show in a report

reportdate.Date
list.Referral_Date
op.Op_Date
cancel.Cancel_Date

Each of the fields are in a different table and each of them are datetime fields.

The reportdate table is a constant - period table with an entry for every date since 1963 upto 2063 e.g. 01/01/2004 00.00.00

I have linked each of the other tables to the reportdate table using a left outer join.

What I would like to get is:

Date Referral Date Op Date Cancel Date
01/01 01/01
02/01 02/01 02/01
03/01 03/01
04/01 04/01 04/01
05/01 05/01
05/01 05/01
06/01 06/01 06/01
06/01 06/01
07/01 07/01
08/01 08/01

Etc. I am using CR9 and SQL2000

I am now noticing that as some of the data that was inputted has got the time portion it is not being called up when put into the report as it is not the same as the reportdate field.

I think that I need to convert each of the datetime fields to just date - but before it reaches the report.

Is this right..? And if so what is the best way of doing this..?

Thanks for your help.

Vis.

 

[MColeman]

With File | Options | Fields, there are options for setting the default format of the field types. There is
one for data and one for datetime.

Are these setup to have a default format?

 

[Viscount]

They are setup for Custom Style dd/mm/yyyy - it does not seem to mention time at all.

When the information is displayed in the report - it only shows the date and not the time as well.

Is that right..?

Cheers

Vis.

 

[MColeman]

You could also put in your record selection:

Date(tabledatetimefield) to make it just look at the
date portion of that field.

 

[kchaudhry]

To convert Date-time field to just a Date field you can do the following:

File > Report Options > Convert Date-time field > Select "Date" from the drop down list.

I think this will do what you are trying to acheive.

Hope it helps!

Kchaudhry

 

[Viscount]

McColeman: I will give the date(datefield) a go.

Kchaudhy: I cannot seem to find that option under File / report options...

Thanks


Vis

 

[kchaudhry]

Sorry I forgot to mention that I am using CR 8.5. I am not familiar with CR 9 so this option might be in a different place.

Kchaudhry

 

[Viscount]

McColeman: I tried that method - however I get the error a Boolean is required here.

Vis

 

[Viscount]

I will see if I can find it somwhere else - I havea plan..!

 

[Viscount]

It was already set up so that it only displayed the date and not the time portion.

I think that I may need to add a command in the Database Expert - is this possible..?

Cheers

Vis