Bad Date Format String

[NattyCat]

I am using the formula below to convert a string date and a string time into a datetime field.

datetime(date({Asgnmnt.DATEACKNOW}) & " " & time({Asgnmnt.TIMEACKNOW}))

Am getting the Bad Date Format String error...can anyone help urgently?

 

[NattyCat]

Just been looking into it..seems there are nulls in that field. Is this the problem? If so..how can I allow for that?

 

[bdreed35]

It is probably more of a case that either {Asgnmnt.DATEACKNOW} or {Asgnmnt.TIMEACKNOW} are not in a format that the date function ot time function can convert.

Please post some sample date of the each of the 2 fields so that we can see the format of the date and time. I am sure a solution can be formulated after that.

~Brian

 

[NattyCat]

The Original fields are String fields, and are in the following format:

DateRec'd TimeRec'd Date Acknowledged TimeAcknowledged
2004-01-02 07:58:32 2004-01-02 09:32:21
2004-01-02 09:21:12 2004-01-07 14:58:32
2004-01-05 14:11:43 2004-01-05 14:12:22

There are null values in the acknowledged fields, but not in the date received fields.

What I am trying to do is work out the days, hours and minutes between the datetime received and datetime acknowledged.

I used the following formula to concatenate each date:

@Date Received
datetime(date({CallLog.RecvdDate}) & " " & time({CallLog.RecvdTime}))

@Date Acknowledged
datetime(date({Asgnmnt.DATEACKNOW}) & " " & time({Asgnmnt.TIMEACKNOW}))

Once I'd got two datetime fields, I then used the following formula to work out the difference in minutes:

@R to A Diff
datediff("n",{@DT Received},{@DT Acknowledged})

Lastly, I used the following formula to separate the values into days, hours and minutes - although, so far this hasn't worked. For example, I am getting "12 days, 0 hours and 0 minutes" for some differences that are only a day or two days.

numberVar dur := {@R to A Diff};
numberVar days;
numberVar hrs;
numberVar min;
stringVar result;

days := Truncate(dur/480);
hrs := Truncate(Remainder(dur,480)/60);
min := Remainder(Remainder(Remainder(dur,480),60),60);


result := totext(days, "0") + " d, " + totext(hrs, "0") + " h, " + totext(min,"0") + " m";

result

So apart from the above formula not returning the expected results, when I try to navigate around the report, I get an error saying "bad date format string" - it opens the formula editor with the @Date Acknowledged formula.

Am using V9 against SQL Server.

 

[kskid]

How about

@Date Received
datetime(datevalue({CallLog.RecvdDate}),timevalue({CallLog.RecvdTime}))

@Date Acknowledged
datetime(datevalue({Asgnmnt.DATEACKNOW}),timevalue({Asgnmnt.TIMEACKNOW}))

 

[NattyCat]

@Date Acknowledged
datetime(datevalue({Asgnmnt.DATEACKNOW}),timevalue({Asgnmnt.TIMEACKNOW}))

Still gives "bad date format string

 

[NattyCat]

am pretty certain is has something to do with null values within the acknowledged date. But - I have to include these values as there will be lots of calls received that have not yet been acknowledged, so therefore there WILL be nulls....not sure if i'm barking up the wrong tree - but if there was a way that I could tell Crystal to ignore any null ones then that might work...any ideas?