Converting Data Type when Importing?? Why?

[rhoneyfi]

Hello,
I am using CR10 linking up to a SQL database. When I import 2 fields (time received, time completed), they import as strings. In the SQL database, they are stored as date/time fields. How come they are not being impored as date/time fields in Crystal?

P.S. Yes, I know that I can use the cdate formula to convert a string to a date/time...but in this particular case, it will not fix the problem.

 

[synapsevampire]

It should work...

You can always convert it using either a Crystal SQL Expression or a Crystal formula, but you'd have to post what Crystal is passing to assist you there.

If you receive "12/01/2004 05:20:59" use:

cdatetime(val(left({table.field},2),val(mid({table.field},4,2)),val(mid({table.field},7,4)),val(mid({table.field},12,2)),val(mid({table.field},15,2)),,val(mid({table.field},17,2)))

You get the idea...

-k

 

[rhoneyfi]

Here's the problem:
Ex: Time Received = 12/22/2004 10:12:30
Time Completed: 12/22/2004 2:12:30

I have a formula the calculates the difference between the fields. In this case it assume that both fields are in AM format (since they are not 24 hour, nor do they have the AM/PM in them)

In the SQL database, they are stored as
Time Received = 12/22/2004 10:12:30 AM
Time Completed: 12/22/2004 2:12:30 PM
So in this case, it would give me the correct difference between the times.

 

[rhoneyfi]

OK...I've dug a little deeper. It seems that when Crystal imports the field lenght, it is a max of 19 character. In the SQL database, it must be longer than this. What can I do to tell Crystal that the # of max characters needs to be like 21 or so? Thanks

 

[synapsevampire]

OK, I'll assume that you mean MS SQL Server, most large scale databases are SQL based.

SQL Server does NOT store datetimes as you're assuming, it displays them that way. Are you certain that the data type is a datetime?

At any rate, it sounds like a driver issue to me, so try applying the service pack.

http://support.businessobjects.com/fix/hot/si75/default.asp

if you're connecting via OLE DB, try ODBC or vice-versa.

-k