Converting String to Date

[TheSpoon]

I have a String that represents a date. For example "040720" represents today. Its format is UNKNOWN, it is specified at run-time. The format of the string in my example happens to be "yymmdd", but it could be anything.

So, given a date string and a format string, how do I convert this to a Date object?


Thank you

 

[CasperTFG]

Dim MyDate as Date
MyDate = CDATE("040720")


Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[CasperTFG]

But that of course will return what you ahve set in Windows default. So for me it returns... 6/26/2011

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[TheSpoon]

CDATE("040720") will not work because CDATE has no knowledge of the string's format.

Thanks for trying though

 

[CasperTFG]

However you mentioned that you don't even know what the format might come in as.

Its format is UNKNOWN

So it would be impossible to convert without know the original format.

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[EBGreen]

So manipulate the string from the form that is determined at runtime into a form appropriate for CDate.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[TheSpoon]

I meant that the format is unknown at design time. It is known at run-time. Is there no function that can produce a Date object given a date String and a format String?

 

[mattKnight]

Is there no function that can produce a Date object given a date String and a format String?

Not one that will give reliable results, no.

However,

So manipulate the string from the form that is determined at runtime into a form appropriate for CDate

seems an appropriate solution. IS there some other reason why that wouldn't work?

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[TheSpoon]

Of course it could work, but it is a large amount of work. Hard to believe that there isn't a built-in function for this. VB has a function to convert a string to a date using the system's format, but not a user-specified format. Looks like another oversight.

 

[johnwm]

It looks like a fairly trivial function to write.
1. Parse the format string to collect year/day/month start and end positions.
2. Split the date string using Mid and the (now known) start and end positions
3. DateSerial gives you a date

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[TheSpoon]

You're assuming the month format is always "MM", the day format is always "dd", and the year format is always "yy". If that was the case, and only their order differed, it would be easy. But this is not the case because the format could be ANY valid format string, including "yyyy/MMMM dd" or any number of others. Today's date in this format would be "2004/July 20". I realize that this is all parseable, but I'm disappointed that there is not a built-in function. Seems like an oversight to me, since CDate is already capable of parsing any date in any format (since it works no matter what locale you're in), you just can't tell it what format to use.

 

[EBGreen]

How will you know what order the date is entered in? Lets say the value entered is 01/02/04. Is this January 1 (US) or February 1 (Europe)? If you know the format, then it is trivial to write the conversion.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[johnwm]

Since that's the way VB6 is, and it's no longer a current product, and Bill Gates rarely reads this forum you're probably out of luck.

However I stand by my previous comment - parsing (regardless of MM or MMMM) is a fairly trivial task.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[TheSpoon]

Yeah, I sure as hell don't expect them to come out with an update to VB6, I was just wondering if there is already a function to do this.

 

[bjd4jc]

I believe that the cdate function will work for this... using your example ( and try other possibilities that you will have) I did this in the debug window:

?cdate("2004/July 20")

and my system returned:

7/20/2004

to format this into a different format you could use the format function:

format("2004/July 20", "MMMM dd, YYYY")
returns
July 20, 2004

Any VALID date format will work with the CDATE function and you can check to see if it's valid with the IsDate Function

?IsDate("2004/July 20")
returns
True

if your application knows the format at runtime this should work fine for you...

 

[johnwm]

bjd4jc
.. and what does it return for "01/02/03"?
The hard bit about the question was the requirement to manage runtime-specified formats

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'