G/L Posting Date shows up as DateTime in Crystal Reports

rj5570

How can I force Crystal Reports 2008 to convert all DateTime To Date. I use to use Crystal 8.5 and in the Report Options you can select Convert DateTime To Date.

I know the Native Database only tracks Date. If i run the table, it shows only Date values. But like I said, its showing as a DateTime in Field Explorer if I show field type.

There must be something I am missing in the newer version of crystal.

Savatage

How about File->Options->Fields

now you can play around with date, time & datetime with the format editor. select the style you want to see.

rj5570

thanks for the response, but its not what i want to see.

i am trying to set up a parameter field and i want to only use date. but since posting date is a date time field, i cant set it as a date parameter.

i dont understand why crystal wants to see it as a DateTime field when navision is only using it as a date field

makes no sense

Savatage

rj5570 wrote:

i am trying to set up a parameter field and i want to only use date. but since posting date is a date time field, i cant set it as a date parameter.

Have you tried? does it give you an error?

Perhaps is an odbc setup that's not seeing the field in it's actual type?

I'm not using the newest version so I can't play around with it. I can only suggest places to look.

ara3n

Hello Sql 2k5 and 2k has datatime only variable. So navision had to use them, and probably continue using them in 2k8, because it can use the time component for closing Dates.

In your crystal report you need to change your sql statement to

CONVERT("Posting Date", CONVERT(INT, GETDATE()))


This will return the date.

rj5570

understand, and appreciate your help!

i dont think there is much of a difference between 2008 and crystal xi

where in the odbc set up would that be, there are not many options there

Savatage

ara3n wrote:

Hello Sql 2k5 and 2k has datatime only variable. So navision had to use them, and probably continue using them in 2k8, because it can use the time component for closing Dates.
In your crystal report you need to change your sql statement to
CONVERT("Posting Date", CONVERT(INT, GETDATE()))
This will return the date.

Not sure if he's using SQL or native.

In my n/odbc setup->options I have Closing Date Support UNCHECKED.

rj5570

ya mine is unchecked too

what do you have for your identifiers?

Savatage

http://technicalsupport.businessobjects ... /5956.aspx

it appears datetime has been a thorn in your side before
http://www.mibuso.com/forum/viewtopic.php?t=22514

I'm trying to download the 30day trial now - see if I get the same thing

rj5570

yes it has

but the new version doesnt have the option to convert datetime in report options

pain in the arse!

rj5570

ya i found that thread on BO forum too

trying it now, will have to set the time to 00,00,00 since NAV doesnt use that field in the Native Database

Savatage

How about create a formula field call "Posting Date" or whatever datetime field you're trying to convert to a date.

in the formula use the function CDATE
CDate (DateTimeField)

this will return a clean date only and use this field to match your parameter field with.

rj5570

so instead of using {G/L_Entry.PostingDate} in my detail, use the CDate formula field? then set a paramater for that field?

guess i am unclear on how to get the data

rj5570

if i create the formula to Cdate (datetime.field) how do i run a paramater on that?

rj5570

duh....i think i figured that part out now...running it, but doesnt look like it works

i made a paramater ?DateRange then put in the detail of the report the formula @PostingDate (which is a CDate(postingdate))

so that detail looks like this:

@postingdate Document_No.

its taking a bit, I will see what i get on this, but it doesnt look good so far

rj5570

I think that worked

I am writing the rest of my report, I will post out come and full solution when i get it

Savatage

In the select expert - click New - scroll down till you see the formula field you just created. select that then add how you want it to match
"Is equal to" or whatever - then drill into the blank to see the choices - then click the Parameter field

http://savatage99.googlepages.com/Param ... toVariable

@Posting Date is the Formula to clean the datetime field
Enter Date is the Parameter Field

rj5570

YES THAT WORKS!!!

\:D/

thanks!

Savatage

see that's opens up a whole new world of using formulas in your selection criteria \:D/

rj5570

yes it does!!!!

We just upgraded to 4.0 so, i have to re-write all my reports