Hi All,

I have another date problem trying to convert a string to a date.
The string i'm using is:

060301 (representing 2006, March, 01)

When I run the string in my code:
CDate(myDateStr) I get 03-Feb-2065 instead of 01-Mar-2006.

Is there any way to get this right? Maybe with format?

Regards

Marco

You need to format it so CDate can understand it.

There's probably a more effificent way to do this, but this would work:

Dim TempDate As String
Dim NewDateStr As String

TempDate = Mid(myDateStr, 5, 2) & "/"
TempDate = TempDate & Mid(myDateStr, 3, 2) & "/"
TempDate = TempDate & Mid(myDateStr, 1, 2)
NewDateStr = CDate(StrDate)

Show quoteHide quote

Wow! You sure were giving VB a lot of credit if you thought it would be able
to figure out that "060301" was March 1, 2006 and not June 3, 2001 or
January 3, 2006 or even March 6, 2001. First off, VB stores dates in as a
Double (they are not **real** Doubles, just housed in the same sized memory
area as a Double). The whole number part represents the number of days since
what VB considers to be "date-zero" (which is December 30, 1899); the
decimal protion, if any, represents the fraction of a day that the time
value represents (for example, if the decimal part were 0.75, that would be
75% of a full days which is 18 hours past midnight, that is, 6:00 PM).
Anyway, since "060301" is not in any standard date format, VB's (evil) type
coercion made the CDate function treat is as its underlying Double value.
February 3, 2065 is 60,301 days past date-zero.

You also have to be very careful with 2-digit year values; letting VB
convert these to 4-digit years on its own can result in different year
values on different computers. The 06 shouldn't be a problem, but something
like "99" would be. If you let VB's (evil) type coercion handle the
expansion from a 2-digit to 4-digit year, it will use the computers' local
(Regional) settings and these are changeable by the user. On my computer,
"99" would be expanded to 2099 whereas most other computers would make that
1999.

Anyway, to answer your question, I would use the DateSerial function to
convert your value to a Date value.

Dim DateString As String
Dim TheYear As Long
Dim TheMonth As Long
Dim TheDayAsString
Dim ActualDateValue As Date
DateString = "060301"
TheYear = Left$(DateString, 2)
If TheYear < 60 Then  'pick the breakpoint you want to use
  TheYear = "20" & TheYear
Else
  TheYear = "19" & TheYear
End If
TheMonth = Mid$(DateString, 3, 2)
TheDay = Right$(DateString, 2)
ActualDateValue = DateSerial(TheYear, TheMonth, TheDay)

Rick

Rick Rothstein [MVP - Visual Basic] wrote:
Show quoteHide quoteWho are you, and what have you done with the real Rick?


ActualDateValue = DateSerial(Left$(DateString, 2), Mid$(DateString, 3, 2), Right$(DateString, 2))


Of course, as you noted, it does abdicate the interpretation of the 2-digit year
to whatever set of dlls is currently installed, but that's pretty much
the baseline state anyway.  One could probably use IIF to squeeze that in, too.



    Bob

Show quote Hide quote LOL .... Whatever do you mean?


Right$(DateString, 2))

Oh, you wondered why I didn't post a one-liner... <g>


2-digit year
Yes, you could add that to your code to give this one-liner...

ActualDateValue = DateSerial(IIf(Left$(DateString, 2) < 60, 20, 19) & _
                  Left$(DateString, 2), Mid$(DateString, 3, 2), _
                  Right$(DateString, 2))

but one could eliminate using an IIF function call and use a logical
expression instead. Do that, and taking advantage of the existing ordering
of date parts in the given date string, here is my proposal for a one-liner
(if I were going to propose one that is<g>)...

ActualDateValue = CDate(19 - (Left$(DateString, 2) < 60) & _
                             Format(datestring, "@@-@@-@@"))


Rick

Guys,

Great discussion but can someone explain why I still get a European
Date format
although I have set my Regional Settings to English (US)?

Marco

On 12 Mar 2006 00:06:59 -0800, vonclausow***@gmail.com wrote:

I'm sure we went through all this about a month ago.

The only sane way is to break the 'date' into Year Month Day and use
DateSerial - as Rick pointed out

Allowing VB to 'guess' the date is just asking for trouble

J French wrote:
Not to mention every 3-6 months or so, since the beginning of the newsgroup...


Show quoteHide quote

Yeah. Dates can be a bit of a bugger sometimes. And yet even now, after all
the trouble we've had in the past, we're all still carrying on as though
Y10K will never come ;-)

Mike

LOL... yeah, but I have **every** confidence that Microsoft will make sure
our VB6 code stops working LOOONNNGGG before Y10K will become a problem (I'm
worried about our code surviving trouble-free the next release of
Windows<g>).

Rick

Mike Williams wrote:
"Y2.1K" will be bad enough - a leap year that's scheduled to actually NOT happen.


(although it looks almost remotely conceivable that enough
  "leap seconds" may add up to make yet another kind of exception)



    Bob

Although this was not meant as serious code, I probably should have thrown a
CStr function call in there (and tagged the Format function with a $ sign)
for "proper coding" reasons...

ActualDateValue = CDate(CStr(19 - (Left$(DateString, 2) < 60)) & _
                              Format$(DateString, "@@-@@-@@"))

Rick

Why ?? (Path/File access error (Error 75)
VB.NET or C++.NET or C#
Using the replace command, please help
File Extension
NTFS File Access in VB6
Sub or Function is not defined
Control similar to MS Access List Box
write to disk and permissions
How to open a chm file nicely ?
Testing for "Access Denied" without trying to change the file