converting string to datetime

[zino]

I need to convert a string as: "DEC 18 2007 2:49:40:783PM"
to a date. This value is sent by SQL server as :select convert(varchar(30),
creationDate, 109) from ... ... .

I tried :
Dim dt As DateTime
Dim myCultureInfo As New CultureInfo("en-US")
If DateTime.TryParseExact("Dec 18 2007 2:49:40:783PM", "mon dd yyyy
hh:mi:ss:mmmAM", myCultureInfo, Globalization.DateTimeStyles.None, dt) Then
.... ...
.... .... ..

always return false (string not a valid date)

how can I do it ?

 

[\(O\)enone]

I need to convert a string as: "DEC 18 2007 2:49:40:783PM"
to a date. This value is sent by SQL server as :select
convert(varchar(30), creationDate, 109) from ... ... .

Just one question: why don't you get SQL Server to return the date in
ISO8601 format instead? This is the only globally identifiable non-ambiguous
format in which dates can be represented as strings.

You can get SQL Server to return it in this format by changing the 109 in
your convert statement to 21 instead. (I <3 magic numbers....)

Then you can just use DateTime.Parse or CDate to convert the string to a
date.

HTH?

 

[Tom Shelton]

I need to convert a string as: "DEC 18 2007 2:49:40:783PM"
to a date. This value is sent by SQL server as :select convert(varchar(30),
creationDate, 109) from ... ... .

I tried :
Dim dt As DateTime
Dim myCultureInfo As New CultureInfo("en-US")
If DateTime.TryParseExact("Dec 18 2007 2:49:40:783PM", "mon dd yyyy
hh:mi:ss:mmmAM", myCultureInfo, Globalization.DateTimeStyles.None, dt) Then
... ...
... .... ..

always return false (string not a valid date)

how can I do it ?

Your format specifier is wrong...

"MMM dd yyyy h:m:s:ffftt"

You need to adjust the m,s in the time, depending on if the minutes are
minutes/seconds are padded. I'm assuming not, because the hour wasn't,
but if they are - then change the m to mm and the s to ss.

 

[Cor Ligthert[MVP]]

Zino,

Although Tom is in my idea correct answering direct your question, is in my
idea the parameter approach much simpler.

http://www.vb-tips.com/SQLParameters.aspx

Cor

 

[Patrice]

Or even why to return a string at all ? My personal preference would be to
always return a date from SQL Server. Then the date is formatted client side
for display (what if you display those data in a grid and want to sort them
by date ?)

 

[zino]

I got it to work with the : convert(varchar(30),creationDate,21) instead of
"109".

thanks for the help