character to date field conversion

[FreeBird]

Re: character to date field conversion

Yes, you are right....grrrr....newbie mistakes. I tried it and it all works fine, the dates are showing. Thank you so much for your patience and time ;)

[Tim Kiebert]

Re: character to date field conversion

:) You're welcome

[lcgreene]

Re: character to date field conversion

How does CTOD work when the existing table date field is populated 5/1//2010 and is character with many records. When I tried CTOD it converted the character date string to 5/1/2000. Also does the same when I use a new blank field formatted as date type, the same change of the date occurs. Any ideas ?

[lcgreene]

Re: character to date field conversion

CTOD("5/11/2010") converts to 05/01/2001, does anyone know why this character date strings is changed as a result of the conversion. This occurs on all records in the table which are different dates and CTOD returns the same changed date for all.

thx lcg

[Tom Cone Jr]

Re: character to date field conversion

using build 2924_3560, I don't get the same results you're reporting.

?ctod("5/11/2010")
= {05/11/2010}

?ctod("05/11/2010")
= {05/11/2010}

Seems to be behaving correctly.

[lcgreene]

Re: character to date field conversion

Thanks for you information. However I have a table with character date. I tried the update table function not using the genie. I tried to convert the strings in the field using CTOD("start date")

which yielded 5/1/2001 for each entry. Did you use the update method ? If not even when I create an empty filed to run the update the same thing happens. I will try again copying your comment.

Thx
LCG

[Tom Cone Jr]

Re: character to date field conversion

Lou, this is different from your first post. To unscramble this for you we will need to see sample data. You're not passing a literal character string to the function, you're passing a table field reference. We need a copy of the table to see what's actually being passed to the function.

[lcgreene]

Re: character to date field conversion

Tom,

when in the expression builder I do ctod("1/1/2010") which is the field content using F2. The result shown is 1/1/2008, when ok is done the error message "expression does not evaluate to a character value" is displayed.

What did you do to get a correct result ?

Thanks,

Lou

[Tom Cone Jr]

Re: character to date field conversion

I used the interactive window, not the expression builder.

Are we using the same build of vers 10?

Are you unwilling to furnish sample data?

[lcgreene]

Re: character to date field conversion

Tom,

Ok I am going to try and attach a copy of the table.

Lou

[lcgreene]

Re: character to date field conversion

I am on ver 9, also run 6 and 7 and would believe have 5 on my win 2000 laptop. Don't know where the file went, but it did. I just used the interactive window, copying the characterdate from one of thetables. The conversion yielded >
?ctod("5 / 2 0 / 2 0 1 0")
= {05/02/2000}

Note the date converted to an unrelated date. It appears ctod cannot convert a true character date that was taken form a text file. I use Monarch to parse data from reports off the web which are either generic text or via a text program that works with Vista.

[Ton Spies]

Re: character to date field conversion

Can you please try in the interactive window
?? ctod(stritran("5 / 2 0 / 2 0 1 0"," ",""))

[Tom Cone Jr]

Re: character to date field conversion

Lou, the blank spaces in the character string you are passing to the function seem to be what's causing the issue. Blanks are characters, too. You're getting an invalid result because your input string of characters is invalid. It's the same as asking Alpha to turn the following into a valid date: ctod("5A/B2C0D/E2F0G1H0")

Strip the blanks from your input string, as Ton is suggesting.

[lcgreene]

Re: character to date field conversion

Tom,

Thanks, that is exactly as you said, the blanks spaces. Just never occurred to me.

Thanks, have a good one

Lou

[Tom Cone Jr]

Re: character to date field conversion

Good, and thanks to Ton for illustrating an easy way to clean up your data.