string field to Date

[hencyt]

I have a string field with date values like 20060628. What is the best/easiest way to convert this field to a date type?

I tried cdate but it is looking for commas. Do I have to insert the commas to be able to convert it? If so, how is best to do that?

Thanks very much in advance!
SunnyD

 

[bdreed35]

There are various ways to do this.
Here is one:

Create this formula:

//@convert to date
local stringvar input := "20060628"; //replace with your field
date(val(input[1 to 4]),val(input[5 to 6]),val(input[7 to 8]))

~Brian

 

[hencyt]

Thanks Brian!

I hadn't seen the val before but thought I would have to use the [# to #] positioning brackets.

Thanks!
SunnyD

 

[hencyt]

Does anyone know why that formula he gave me won't work in vs 10? I put the formula I have in 8.5 and it says no errors. But when I copy and paste to vs 10 it doesn't recognize the stringvar- is it called something else in 10?

Here is the formula:
local stringvar tcdate;
tcdate :={timecards.tc_date};
date(val(tcdate[1 to 4]),val(tcdate[5 to 6]),val(tcdate[7 to 8]))

Thanks in advance!
SunnyD

 

[synapsevampire]

Probably a formatting issue with the date.

I've never heard of an error of doesn't recognize the stringvar, perhaps you should post the REAL error instead.

try:

local stringvar tcdate;
tcdate := trim({timecards.tc_date});
if not(isnull(tcdate))
and
len(tcdate) = 8 then
date(val(tcdate[1 to 4]),val(tcdate[5 to 6]),val(tcdate[7 to 8]))
else
cdate(1950,1,1)

Now if it returns 1/1/1950 you know you've a bad date.

-k

 

[hencyt]

The reason I said it doesn't recognize it is because it doesn't turn blue. None of the variable declarations turn blue in my version 10. I have just installed vs 10. Is there an option that isn't set maybe?

I put in your formula k and the stringvar didn't turn blue there either. This is the error it returned and is the same error I was getting before. The REAL error:

The remaining text does not appear to be part of the formula.

--the highlighted error area starts right after the stringvar word in the formula.

Thanks for helping me out with this. It is strange.

Sunny

 

[synapsevampire]

Look up[ above, I'll bet that you have that language set to Basic Syntax instead of Crystal Syntax.

-k

 

[hencyt]

Ugh. Yep. I guess the new installation is defaulting to Basic.

Thanks- I figured it was something like that.

SunnyD

 

[dgillz]

NumberToDate() converts a 8 digit number in a YYYYMMDD format to a real date. That function is available as a download if it is not native to crystal 10.

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

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[hencyt]

dgillz-

Where can it be downloaded from?

Thanks,
SunnyD