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

  • Thread starter Thread starter Conan Kelly
  • Start date Start date
C

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
 
Conan,

There are endless posibilities in VB.

I do it mostly like this.

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

Cor
 
Conan Kelly said:
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?
Click to expand...

Format("11SEP2008", "##-###-####") perhaps?
 
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
 
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
 
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
 
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
 
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
 
Back
Top