String to date conversion 1

[techninut]

I am trying to convert my date field (which is a 'string' datatype) in to a date field that is a date or number type so I can perform other operations. However, I have tried converting many different ways and am still having no success. My report basically shows the last activity of a customer and I am trying to write an if then statement that will pinpoint any customers that have not had any activity in the last 24 hours.

my date string is length 22 and looks like this :

yyyy/mm/dd hh:mm:ss.ss

all I really need to convert it to is a datefield with your basic date/time so I can then use the formula

datefield1 >= currentdate - 1

I know this is a mouthfull but any help available would be much appreciated.

- David

 

[pgtek]

Hi

just go in your formala section
and convert your date field

or
click on your field -> format -> customise and pick the format you want it to be

cheers

pgtek

 

[techninut]

I appreciate the quick response, however this field is not a formula, it is a string out of my db. Therefore I cannot customize or format it, that is why I am trying to create a formula to convert it from a string to a date/time field.

 

[ShelDoyal]

If I'm not mistaken, you should be able to create a formula from that field using the IsDate () function to turn it into a date. I imagine the helpfiles have more detail on formatting, but I'm pretty sure that would work.

 

[techninut]

ShelDoyal,

I appreciate your response, however the IsDate() function is actually a boolean function (meaning it returns a value of true or false) in order to determinte whether the data type is a date. I have been able to convert my string to a datetime using the DTStoDatetime() function, however I still can not figure out how to determine whether or not the last activity was from 24 hours or more ago.

Basically I have done this:

1)I converted my string to a datetime.
2)I am trying to use the format formula feature in order to highlight the background color if the last activity of that formula is more than 24 hours ago. However whenever I put in my formula editor parameters of:

@last_activity < currentdate -1

I keep receiving an error message stating that &quot;The formula result must be a number&quot;

I tried doing other formulas and once I got the error message:

&quot; The formula cannot refer to itself&quot;

If anyone has any ideas, please let me know.

- David

 

[goranm]

Hi !

Can´t you just use a formula like this for the background color:

if DTStoDateTime(YourString) < CurrentDateTime -1 then
crRed
else
crNoColor

/Goran

 

[mck144]

Try using the DateTimeValue() formula. You can either input a number or a string.

-Mike

 

[techninut]

dsergile (TechnicalUser) Aug 26, 2003
** Goranm **

Yes ! That did work, thanks for the help. Now I am trying to add on that so my report will highlight any null values,

I tried this:

//**

if isnull({emote.agent.last_activity}) then cryellow
if DTStoDateTime({remote_agent.last_activity}) < CurrentDateTime -1 then
crRed
else
noColor

**//

I get no errors from this, however my null values are still not being highlighted. I made sure that my report options converts all null values to default so I know that is not the problem.

Do you have any ideas ?

** Mck144 **

I tried using what you suggested:

if DateTimeValue(remote_agent.last_actity) < currentDateTime -1 then cryellow
else nocolor

except this gave me an error saying that it must be a number value.

Anyways, I appreciate the help from everybody.

-David

 

[lbass]

You can't format a null field because, in effect, it isn't there. You can instead place the field which contains nulls in a text box, and format that for color if the field is null.

However, what you have done is to change the nulls to a default value, and therefore your field is no longer null--so your formula cannot work based on nulls. So I would uncheck &quot;convert nulls to default&quot; and then format the text box with this formula:

if isnull({remote.agent.last_activity}) then cryellow else
if DTStoDateTime({remote_agent.last_activity}) < CurrentDateTime -1 then crRed else crNoColor

-LB