how to convert string to date?

9 Mar 2006 10:30 PM

vonclausowitz

Hi All,

This seemed a simple project but I'm at the edge almost.
I can't seem to convert a string to a date.
The string looks like this:

"01 MAR 06" and I want to convert it to a date like: 01-03-06

I've tried:

Dim strDate as String
Dim myDate as Date
strDate = "01 MAR 06"

myDate = Format(CDate(strDate), "dd-mm-yy")
but it won't work..... I get a type mismatch!!!!!!!!!!!!!

Anyone?

Regards
Marco

9 Mar 2006 10:35 PM

Ken Halter

<vonclausow***@gmail.com> wrote in message
Show quoteHide quote

news:1141943422.646452.139200@e56g2000cwe.googlegroups.com...
> Hi All,
>
> This seemed a simple project but I'm at the edge almost.
> I can't seem to convert a string to a date.
> The string looks like this:
>
> "01 MAR 06" and I want to convert it to a date like: 01-03-06
>
> I've tried:
>
> Dim strDate as String
> Dim myDate as Date
> strDate = "01 MAR 06"
>
> myDate = Format(CDate(strDate), "dd-mm-yy")
> but it won't work..... I get a type mismatch!!!!!!!!!!!!!
>
> Anyone?
>
> Regards
> Marco

You can use CDate but watch the regional settings.
'========
Private Sub Command1_Click()
   '"01 MAR 06" and I want to convert it to a date like: 01-03-06
   Const THE_DATE = "01 MAR 06"
   Dim dtConverted As Date

   dtConverted = CDate(THE_DATE)
   Debug.Print Format$(dtConverted, "dd-mm-yy")
End Sub
'========

--
Ken Halter - MS-MVP-VB - Please keep all discussions in the groups..
DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm

10 Mar 2006 1:12 PM

Tony Proctor

You need to understand what you've written there Marco.

You're starting with a string variable holding "01 MAR 06"

You're then using CDate() to convert that to a Date value (held internally
in binary, not as a string anymore)

You're then using Format to convert that binary date back to a string, and
presumably generating "01-03-06"

You're then assigning that string to a real date variable which will try and
convert it back to an internal binary format.

I can't reproduce it here so it may be something to do with your regional
settings, as Ken suggested. Since you're using a convoluted route (i.e.
string->date->string->date) it's hard to say exactly where the failure
occurs. It would be worth splitting your assignment statement up into
separate steps to determine that.

Tony Proctor

<vonclausow***@gmail.com> wrote in message
Show quoteHide quote

news:1141943422.646452.139200@e56g2000cwe.googlegroups.com...
> Hi All,
>
> This seemed a simple project but I'm at the edge almost.
> I can't seem to convert a string to a date.
> The string looks like this:
>
> "01 MAR 06" and I want to convert it to a date like: 01-03-06
>
> I've tried:
>
> Dim strDate as String
> Dim myDate as Date
> strDate = "01 MAR 06"
>
> myDate = Format(CDate(strDate), "dd-mm-yy")
> but it won't work..... I get a type mismatch!!!!!!!!!!!!!
>
> Anyone?
>
> Regards
> Marco
>

10 Mar 2006 4:42 PM

vonclausowitz

Tony,

I does have something to do with the regional settings (Dutch in my
case).
It wants to see MRT instead of MAR. Is there no way to solve this apart
rom
changing the regional settings?

The only thing I can think of is uing something like:

If Instr(myDate, "MAR") <> 0 Then
Replace("MAR", "MRT")
.......

But it is not fancy, is it....

Marco

Tony Proctor schreef:

Show quoteHide quote

> You need to understand what you've written there Marco.
>
> You're starting with a string variable holding "01 MAR 06"
>
> You're then using CDate() to convert that to a Date value (held internally
> in binary, not as a string anymore)
>
> You're then using Format to convert that binary date back to a string, and
> presumably generating "01-03-06"
>
> You're then assigning that string to a real date variable which will try and
> convert it back to an internal binary format.
>
> I can't reproduce it here so it may be something to do with your regional
> settings, as Ken suggested. Since you're using a convoluted route (i.e.
> string->date->string->date) it's hard to say exactly where the failure
> occurs. It would be worth splitting your assignment statement up into
> separate steps to determine that.
>
> Tony Proctor
>
> <vonclausow***@gmail.com> wrote in message
> news:1141943422.646452.139200@e56g2000cwe.googlegroups.com...
> > Hi All,
> >
> > This seemed a simple project but I'm at the edge almost.
> > I can't seem to convert a string to a date.
> > The string looks like this:
> >
> > "01 MAR 06" and I want to convert it to a date like: 01-03-06
> >
> > I've tried:
> >
> > Dim strDate as String
> > Dim myDate as Date
> > strDate = "01 MAR 06"
> >
> > myDate = Format(CDate(strDate), "dd-mm-yy")
> > but it won't work..... I get a type mismatch!!!!!!!!!!!!!
> >
> > Anyone?
> >
> > Regards
> > Marco
> >

10 Mar 2006 8:18 PM

Desi

Marco,

In your original post you wrote that you desired:

>"01 MAR 06" and I want to convert it to a date like: 01-03-06

If the "01-03-06" you listed is also simply a string data type then I would
build
a Select Case structure to convert the month component thusly:

Case Is = "MRT"
    MyVariable = "03"

.... and then MyDate = ("01-" & MyVariable & "-06") etc.

If you are actually wanting to convert a string data type into a date data
type, then
in my opinion there's no safer way than the brute force method:

I would build a Select Case structure for the month component like this:

Case Is = "MAR"
    MyMonthString = "March"

I would build a Select Case structure for the year component like this:

Case Is = "06"
    MyYearString = "2006"

Then glue the three date components back together like this:

MyDateString = ("01" & " " & MyMonthString & " " & MyYearString )

CDate() should work fine on this string as you've made it clear to VB which
date component is which.

Typing this into the Immediate window of my local IDE:    ?CDate("01 March
2006")

Yeilds this: 3/1/2006

Once you have a "pure" date data type, you can use VB's Format() function to
do just about anything you need to.

Desi

Thread options