How to convert a date string into a true date? 1

[keArts]

I have a date field (4/8/2010 M/D/YYYY) that is in a string. I need to converet it into a date field. I have tried the following:

StringVar DT := {Prt.Prt_Registration_Date};
NumberVar YR := Val (Split (DT , '/')[3]);

if YR < 50 then YR := YR + 2000 else
if YR < 1000 then YR := YR + 1900;

Date ( YR,
Val (Split (DT , '/') [1]),
Val (Split (DT , '/') [2]) )

I get the following error message:

A subscript must be between 1 & the size of the array.

What is wrong with my formula? Or what formula could I use to get this into a date?

 

[Turkbear]

Hi,
Use the Date(String) function:

CDate and DateValue functions are equivalent to Date.
Description
Returns a Date value.
Overloads
Date (number)
Date (string)
Date (dateTime)
Date (YYYY, MM, DD)
Arguments
number is a value representing the number of days starting from December 30, 1899. It can be positive or negative, and is truncated if fractional.
string is a text string representing a date, example: "September 20, 1999".
dateTime is a DateTime value.
YYYY is a whole number representing a year; MM is a whole number representing a month; DD is a whole number representing a day of the month.
Returns
A Date value.
Action
Date (number) returns a Date value given a number of days starting from December 30, 1899.
Date (string) returns a Date value given a string that represents a date from January 1, 100 through December 31, 9999.
Date (dateTime) returns the date portion of a given DateTime value.
Date (YYYY, MM, DD) returns a Date value given numeric arguments of the year, month, and day.
Examples
Date ("Dec 31, 1999")
Returns the Date value for Dec. 31, 1999.
Date (50)
Returns the Date value for February 18, 1900.
Date (#Oct. 20, 1999 12:02pm#)
Returns the Date value for October 20, 1999.
Date (1930, 7, 30)
Returns the Date value for July 30, 1930.
Comments
You can use the IsDate function to check if a String argument can be converted to a Date before doing the actual conversion. That way, if the conversion cannot be done, you can handle the situation appropriately.

Crystal's function will recognize any standard date format string, and should convert yours OK.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[keArts]

I tried the IsDate to check if the string argument can be converted to a date - the result came back True.

When I tried the Date it gave me an error message Bad Date Format String.

 

[lbass]

Can you verify that your date returns in the format M/D/YYYY? Your original formula is based on a format of M/D/YY.

-LB

 

[keArts]

My date does return in the format M/D/YYYY. How do I change the formula to reflect this?

 

[lbass]

Well, I just tested this, and Turkbear is right, you should be able to just use:

date({yourstringdate})

So, that implies that you have a null or empty field, or badly formed date, so try something like this:

if isnull({yourstringdate}) or
trim({@yourstringdate}) = "" or
trim(replace({@yourstringdate},"/","")) = "" or
ubound(split({yourstringdate},"/")<> 3 then
date(9999,9,9) else
date({yourstringdate})

-LB