Date Time Conversion 1

[dgillz]

MS Server 2000 database, ODBC connection, Crystal XI. I have tons of crystal experience but virtually zero experience converting a string to a date-time. I have a char field populated as follows:

10/24/2006 06:00am

There are no leading or trailing spaces. I have tried

Datetimevalue({MyField}) as well as Datetimevalue(Trim({MyField}))

In both cases I am getting the error "Bad Date-Time Format String".

Any insights appreciated.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[Ladyazh]

I am probably going to post useless advice but just in case you overlooked this option
have you tried to right click on the Date field and Format it to the date type you want?
Is there specific reason you are using a formula for this?

 

[Margiola]

To convert a string or number to a date or to a datetime, use the CDate or CDateTime function in a formula, eg

CDate ("10/24/2006 06:00am")
or
CDateTime ("10/24/2006 06:00am")

 

[dgillz]

Ladyazh,

It is not a date time field it is a string field. Hence the need for a formula.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[mocgp]

I do a lot of string to date conversions but not in the format you have indicated. But here is what I would try:

(Assuming that single digit months and days include a leading zero AND that the time format is always 7 characters...)

Global StringVar dtstr:=left({MyField},10)+"-"+ right({MyField},7);
If IsDateTime(dtstr) then DateTimeValue(dtstr)

You may need to adjust the StringVar results so that it passses the If IsDateTime test. Maybe play with the character positions or change the "-" character. The key is that you have to re-format the string so that it passes the test.

 

[lbass]

Try:

datetime(val(mid({table.datestr},7,4)), val(left({table.datestr},2)),val(mid({table.datestr},4,2)),
(
if right({table.datestr},2) = "am" then
val(mid({table.datestr},13,2)) else
if right({table.datestr},2) = "pm" then
val(mid({table.datestr},13,2))+12
)
,val(mid({table.datestr},16,2)),0)

-LB

 

[dgillz]

mocgp,

Why the variable?
Why the dash "-" ?

Of couse my string already fails the test, hence the error. I do not see that declaring a variable to do what I can do in a straight formula accomplishes.

How would I "play with" the string? What would I change the "-" to?

In any case, a simple formula of:

DateTimeValue("10/24/2006-06:30am")

Returns the same error.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[dgillz]

Thanks LB. I got the DateTime() function to work what I converted the strings to numbers as per your example.

However in the interests of expanding my knowledge, what is the DateTimeValue() function for and how dows it work? According to the help file my formula should work perfectly. I must be missing something.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[lbass]

Don,

I just tested your sample string with datetimevalue() and it returned the same correct datetime result as my suggestion in my earlier post. I wonder if the issue is that the string is not entered consistently in the database.

-LB

 

[dgillz]

Lbass,

According to the help file, DateTimeValue() can work with a single argument, a string in a "MM/DD/YYYY HH:MMam" format.

Your formula has 5 arguments.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[mocgp]

dgillz,

Your question about why I use the variable made me think back a good ways to remember why.

If I remember correctly, the variable and the If-then test prevents a report from failing on a "bad date format" encounter. In a perfect world, you would want to see the error and correct the faulty records. In reality, I have to deal with lots of screwy date records in string format that will never be scrubbed.

The reference to the dash comes from encountering a variety of date/time structures, some of which insert the dash between the date and time, others do not. My point was that, in order for the string to be converted to a date, it has to be re-formatted and tested.

You are right in that a simple formula performs the string-to-date conversion, but it also produces the failure on a bad format. My method prevents my reports from failing when they encounter the bad formats.

 

[synapsevampire]

Interesting, if you just use:

cdatetime("10/24/2006 06:00am") it works here on CR XI.

So try:

cdatetime({table.field})

It may be that the format changes based on the date, or it could be that it is sometimes blank or null.

-k

 

[mocgp]

sv:

Yes, your test worked because it happens to be a properly formatted string and converts without error using cdatetime.

dgillz was getting a "Bad Date-Time Format String" error, which can only mean that one or more of the string fields was not formatted properly for the cdatetime conversion (I haven't tested blanks or nulls to see if they fail the test). The problem with the cdatetime function is that the resulting error doesn't tell you which (or how many) fields are guilty of the bad format. It would be easy enough to query the field, sort out the culprits, and correct them. But then what happens the next time a bad string gets entered?... The report fails again. The variable with the If/Then test prevents the error.

 

[synapsevampire]

mocgp: So just use:

If isdatetime({table.,field}) then
cdatetime({table.field})
else
cdate(1900,1,1)

or some such.

I understand the notion of malformed date stamps, but this is overly complicated, check for it being valid and take some action.

Since Don supplied an example, I supplied the solution for the example. He's a strong Crystal coder, so null/blank checks are second nature to him, the issue is probably something else, such as a bad format.

Anyway, the above resolves it, or one might convert to a string and display a message when it failed th3e isdatetime.

-k

 

[dgillz]

cDatetime() was the answer. However looking at the help files I do not understand why DateTimeValue() did not work. Having said that, I have a solution so I don't really care.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[synapsevampire]

Good to hear, Don, thanks for clarifying.

-k

 

[mocgp]

sv:

I agree with your assessment that I have over-complicated this; your approach is the best solution for Don's field. I am influenced by the necessity to deal with a string field that looks like this:

2006-08-23-08.04.21.175000

It's not likely that his field would be that far off the format he indicated. In my case, I have to re-format before I can perform test and convert, otherwise all the dates end up being 1/1/1900. My suggesting the re-format to Don was based on the (mistaken) assumption that he had a record whose format was way out of whack.