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

[Conan Kelly]

Crossposted:
microsoft.publi c.dotnet.langua ges.vb
microsoft.publi c.sqlserver.int egrationsvcs

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("11SEP20 08", "@@-@@@-@@@@")

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

---------------------------
"Smokin' weed kills your brain cells. Drinkin' only screws up your
liver...ya got 2 a those."
- Earl Hickey (NBC's "My Name is Earl")

[Clive Lumb]

"Conan Kelly" <CT************ **@msnNOSPAM.co mNOSPAMa écrit dans le message
de news: eM************* *@TK2MSFTNGP06. phx.gbl...

Crossposted:
microsoft.publi c.dotnet.langua ges.vb
microsoft.publi c.sqlserver.int egrationsvcs

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("11SEP20 08", "@@-@@@-@@@@")

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("11SEP20 08", "##-###-####") perhaps?

[Cor Ligthert[MVP]]

Conan,

There are endless posibilities in VB.

I do it mostly like this.

\\\
Dim seDate = CDate("12sep200 8").ToString("d d-MMM-yyyy")
///

Cor
"Conan Kelly" <CT************ **@msnNOSPAM.co mNOSPAMschreef in bericht
news:eM******** ******@TK2MSFTN GP06.phx.gbl...

Crossposted:
microsoft.publi c.dotnet.langua ges.vb
microsoft.publi c.sqlserver.int egrationsvcs

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("11SEP20 08", "@@-@@@-@@@@")

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

---------------------------
"Smokin' weed kills your brain cells. Drinkin' only screws up your
liver...ya got 2 a those."
- Earl Hickey (NBC's "My Name is Earl")

[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(pdblTota lBalance, "#,##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


"Clive Lumb" <clumb2@gratuit _en_anglais.fr. invalidwrote in message
news:48******** *************** @news.free.fr.. .

>
"Conan Kelly" <CT************ **@msnNOSPAM.co mNOSPAMa écrit dans le
message de news: eM************* *@TK2MSFTNGP06. phx.gbl...

>Crossposted:
microsoft.publ ic.dotnet.langu ages.vb
microsoft.publ ic.sqlserver.in tegrationsvcs

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("11SEP2 008", "@@-@@@-@@@@")

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("11SEP20 08", "##-###-####") perhaps?

[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

"Cor Ligthert[MVP]" <no************ @planet.nlwrote in message
news:67******** *************** ***********@mic rosoft.com...

Conan,

There are endless posibilities in VB.

I do it mostly like this.

\\\
Dim seDate = CDate("12sep200 8").ToString("d d-MMM-yyyy")
///

Cor
"Conan Kelly" <CT************ **@msnNOSPAM.co mNOSPAMschreef in bericht
news:eM******** ******@TK2MSFTN GP06.phx.gbl...

>Crossposted:
microsoft.publ ic.dotnet.langu ages.vb
microsoft.publ ic.sqlserver.in tegrationsvcs

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("11SEP2 008", "@@-@@@-@@@@")

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
smalldatetim e/DT_DBTIMESTAMP/DT_DBDATE. If I remember correctly, can't
"dd-mmm-yyyy" be implicitly cast? I have a script component
transformati on 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

---------------------------
"Smokin' weed kills your brain cells. Drinkin' only screws up your
liver...ya got 2 a those."
- Earl Hickey (NBC's "My Name is Earl")

[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


"Conan Kelly" <CT************ **@msnNOSPAM.co mNOSPAMwrote in message
news:u%******** *******@TK2MSFT NGP02.phx.gbl.. .

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 Ligthert[MVP]" <no************ @planet.nlwrote in message
news:67******** *************** ***********@mic rosoft.com...

>Conan,

There are endless posibilities in VB.

I do it mostly like this.

\\\
Dim seDate = CDate("12sep200 8").ToString("d d-MMM-yyyy")
///

Cor
"Conan Kelly" <CT************ **@msnNOSPAM.co mNOSPAMschreef in bericht
news:eM******* *******@TK2MSFT NGP06.phx.gbl.. .

>>Crossposted :
microsoft.pub lic.dotnet.lang uages.vb
microsoft.pub lic.sqlserver.i ntegrationsvcs

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("11SEP 2008", "@@-@@@-@@@@")

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
smalldateti me/DT_DBTIMESTAMP/DT_DBDATE. If I remember correctly, can't
"dd-mmm-yyyy" be implicitly cast? I have a script component
transformatio n 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

---------------------------
"Smokin' weed kills your brain cells. Drinkin' only screws up your
liver...ya got 2 a those."
- Earl Hickey (NBC's "My Name is Earl")

[Gerald Aichholzer]

Hello Conan,

Conan Kelly wrote:

>
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/libr...sy(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.Inse rt(2, "-").Insert(6 , "-")

Catch

dFormat = "Length N/A"

End Try

End Function

"Conan Kelly" <CT************ **@msnNOSPAM.co mNOSPAMa écrit dans le message
de news: O$************* @TK2MSFTNGP05.p hx.gbl...

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(pdblTota lBalance, "#,##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


"Clive Lumb" <clumb2@gratuit _en_anglais.fr. invalidwrote in message
news:48******** *************** @news.free.fr.. .

>>
"Conan Kelly" <CT************ **@msnNOSPAM.co mNOSPAMa écrit dans le
message de news: eM************* *@TK2MSFTNGP06. phx.gbl...

>>Crossposted :
microsoft.pub lic.dotnet.lang uages.vb
microsoft.pub lic.sqlserver.i ntegrationsvcs

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("11SEP 2008", "@@-@@@-@@@@")

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("11SEP2 008", "##-###-####") perhaps?

[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

"Clive Lumb" <clumb2@gratuit _en_anglais.fr. invalidwrote in message
news:ew******** ******@TK2MSFTN GP04.phx.gbl...

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.Inse rt(2, "-").Insert(6 , "-")

Catch

dFormat = "Length N/A"

End Try

End Function

"Conan Kelly" <CT************ **@msnNOSPAM.co mNOSPAMa écrit dans le
message de news: O$************* @TK2MSFTNGP05.p hx.gbl...

>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(pdblTot alBalance, "#,##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


"Clive Lumb" <clumb2@gratuit _en_anglais.fr. invalidwrote in message
news:48******* *************** *@news.free.fr. ..

>>>
"Conan Kelly" <CT************ **@msnNOSPAM.co mNOSPAMa écrit dans le
message de news: eM************* *@TK2MSFTNGP06. phx.gbl...
Crossposte d:
microsoft.pu blic.dotnet.lan guages.vb
microsoft.pu blic.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("11SE P2008", "@@-@@@-@@@@")

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("11SEP 2008", "##-###-####") perhaps?