SQl Designer converts DateTime to String

[Wabush]

I have created a query in SQl Designer from an Oracle database and the datetime field is now reading as a string field, so to convert it back, I am using the following formula, but it says too many parameters have been given to the function.

stringVar MyYear := Mid ({Query.DATETIME},1,4 );
stringVar MyMonth := Mid ({Query.DATETIME},6,2 );
stringVar MyDay := Mid ({Query.DATETIME},9,2 );

dateVar MyNewDate := Date (MyYear,MyMonth,MyDay);

Any ideas?

 

[Naith]

Replace your formula with this:

numbervar MyYear := tonumber(mid({@date},7,4 ));
numberVar MyMonth := tonumber(mid({@date},4,2 ));
numberVar MyDay := tonumber(mid({@date},1,2 ));

dateVar MyNewDate := Date(MyYear, MyMonth, MyDay);

Naith

 

[Naith]

**Addendum**

Sorry, Wabush - I neglected to spot that your date format is different from the format I used in my response.

Still use numbervars, but obviously where your year values are 1,4 use that, as opposed to my 7,4 - and the same for month and day.

Naith

 

[Wabush]

Thanks, Naith. That got me further, but now it's giving me "the string is non-numeric".
Is that because there are null records in that field?

 

[Naith]

You're right on the money; that's exactly why it is.

If the date-to-text conversion contains null values or spaces, then the formula I gave you will fall on it's ass.

You need to prefix the numbervar declarations with:

"if not isnull({@stringdate}) then"

And then you're home free.

Naith

 

[Wabush]

Thanks again..much appreciated..However <grin> it still doesn't want to work.

This is what I had;

if length(trim({Query.DATETIME})) > 0 then
numberVar MyYear := tonumber(Mid ({Query.DATETIME},1,4));
numberVar MyMonth := tonumber(Mid ({Query.DATETIME},6,2));
numberVar MyDay := tonumber(Mid ({Query.DATETIME},9,2));
Date (MyYear,MyMonth,MyDay)

Then I tried yours;

if not isnull({Query.DATETIME}) then
numberVar MyYear := tonumber(Mid ({Query.DATETIME},1,4));
numberVar MyMonth := tonumber(Mid ({Query.DATETIME},6,2));
numberVar MyDay := tonumber(Mid ({Query.DATETIME},9,2));
Date (MyYear,MyMonth,MyDay)

And, I got it to run once, and then it crapped out again with &quot;the string is non-numeric&quot;.

I even put in a selection formula in to only bring back records that have something in that field...

I'm stuck. Why would it run once and then not anymore?

 

[Naith]

This looks to me like you have punctuation or spaces being caught up in the string when the tonumber conversion is occuring.

But the fact you say it ran once, and then not again is very interesting. Perhaps you could confirm whether or not you ran it on the same subset of data.

Anyway, trouble-shooting time: I think you should put the todate conversion on ice for 5, and display the totext string of the date as it is before our new formula attempts to convert it.

Whack {Query.DATETIME} in the details section, and conditionally format the details section like this, so that any punctuation or spaces are brought to your attention. Use something like &quot;InStr ({@nullchecker}, ',')&quot; to conditionally make the field red or something to enable you to quickly see where your problem lies.

 

[Wabush]

Ok, the string isn't the problem. As long as I don't try to do anything with the newly formatted date field, it will display on the report.

However, if I try to use it in a parameter, or group on it, it gives me the error. So, back to the original dilemma of what to do when SQL designer changes your datetime field?

I think I will ask the DBA's (again) if they can create a view for me to work from....

Thanks for all your help -

 

[Naith]

Aha. Well there's your problem then. You can't parameterise on the conversion or group on it, because both those operations are processed *prior* to you working your magic on the string field. By the time the parameter or the group check the field, the date is still in an unacceptable format.

You'll have to do something about this field in the database - or as you suggest, in a view - before it gets to report.

Sorry this story didn't have a happy ending for you.

Naith