Format() function in VB.Net vs VBA in XL

[Conan Kelly]

Crossposted:
microsoft.public.dotnet.languages.vb
microsoft.public.sqlserver.integrationsvcs



Hello all,

In XL 2003's VBA, I could use the format function to change "11SEP2008" (a
string) to "11-SEP-2008" (another string) like so:

Format("11SEP2008", "@@-@@@-@@@@")

That doesn't seem to be the case in VB.NET. When I tried the same thing, I
got "@@-@@@-@@@@" instead of "11-SEP-2008".

What would be the equivalent in VB.NET?

In SQL Server 2005 SSIS, I need to import fixed-width flat files with
string/text dates in the format of "ddmmmyyyy", but string/text dates in
that format can not be implicitly cast to
smalldatetime/DT_DBTIMESTAMP/DT_DBDATE. If I remember correctly, can't
"dd-mmm-yyyy" be implicitly cast? I have a script component transformation
in my data flow. I was trying to use the format function above to change
"ddmmmyyyy" to "dd-mmm-yyyy" in the script tranformation, but no workie!!!
For now, I guess I'll have to use Right(), Mid(), and Left() and concatenate
with hyphens.

Thanks for any help anyone can provide,

Conan Kelly

 

[Cor Ligthert[MVP]]

Conan,

There are endless posibilities in VB.

I do it mostly like this.

\\\
Dim seDate = CDate("12sep2008").ToString("dd-MMM-yyyy")
///

Cor

 

[Clive Lumb]

Crossposted:
microsoft.public.dotnet.languages.vb
microsoft.public.sqlserver.integrationsvcs



Hello all,

In XL 2003's VBA, I could use the format function to change "11SEP2008" (a
string) to "11-SEP-2008" (another string) like so:

Format("11SEP2008", "@@-@@@-@@@@")

That doesn't seem to be the case in VB.NET. When I tried the same thing,
I got "@@-@@@-@@@@" instead of "11-SEP-2008".

What would be the equivalent in VB.NET?

Format("11SEP2008", "##-###-####") perhaps?

 

[Conan Kelly]

Clive,

Thanks for the feedback, but sorry...no workie!!!

Just like using the "at" symbol, my results for your suggestion was
"##-###-####" instead of "11-SEP-2008".

Also, isn't the pound/number sign meant for digits? Wouldn't you use it to
change how a number is displayed? Something like this:
Format(pdblTotalBalance, "#,##0.00"). Do the pound/number signs even work
with string data? If they do, they might work for all the digits in the
string, but I'm guessing that they wouldn't know what to do with the "SEP"
part of the string.

Thanks again for all of your help,

Conan

 

[Conan Kelly]

Cor,

Thanks for the feedback. I'll try it out.

Although, it is not exactly what I had in mind. Take a date as a string
data type, convert it to a date data type, then to a string data type in the
desired format, just so it can be imported (converted again) into a
smalldatetime column in a database. That is a lot of converting. But I
guess you gotta go with what works.

Will VB.NET recognize "12SEP2008" as a date and convert it, or does it need
the hyphens in there ("12-SEP-2008")? I guess I'll find out in a couple of
minutes.

Thanks again for all of your help,

Conan

 

[Conan Kelly]

Cor,

Looks like it worked okay.

Just out of curiosity, why would the at symbol (@) work in VBA but not
VB.NET? Doesn't make sense.

Thanks again,

Conan

 

[Gerald Aichholzer]

Hello Conan,

Just out of curiosity, why would the at symbol (@) work in VBA but not
VB.NET? Doesn't make sense.

VBA is not VB.NET, .NET has its own syntax for format
strings which significant differences to the VBA version.
For more detail about .NET format strings see:

http://msdn.microsoft.com/en-us/library/26etazsy(VS.80).aspx


regards,
Gerald

 

[Clive Lumb]

Hi Conan,

Indeed no workee, sorry I was going to check then my VS2008 broked.
This could be quite an elegant solution...
Public Function dFormat(ByRef DateString As String) As String

Try

dFormat = DateString.Insert(2, "-").Insert(6, "-")

Catch

dFormat = "Length N/A"

End Try

End Function

 

[Conan Kelly]

Clive,

Thanks again.

Cool...I'll have to save this post and try that the next time I run into
this issure again. I'm not verry familiar with VB.NET...more VB6 & VBA. I
did not know about the Insert ?function/method? for strings.

Thanks again for all of your help,

Conan