Convert database field to time format

Question

I'm trying to make my string a nice formatted time but the column in the database isn't set up as a date/time field is there anyway I can do this?

Here's my query

Dim query as String = "Select * from openquery (devbook, 'SELECT wb.arrival_time FROM web_bookings wb ')"

And here's where i'm converting it

Session("formattime") = DateTime.Parse(drv.Row("arrival_time")).ToString("hh:mm")

The arrival_time field only has 4 numbers in like this 1000 so I need a way of converting it to a nice format like this 10:00AM

I'm using VB by the way

Any ideas?

Thanks

Jamie

Answer 1

The date may contain more than just hours and minutes. You need to check what is in arrival time

Answer 2

I'm not familiar with the VB.NET syntax, but I'm thinking you could just use string.Split() on the arrival time value, to split it into hours and minutes, then convert that into a DateTime.
DateTime.Parse won't automatically realise that those 4 numbers are supposed to be hours and minutes.

Answer 3

I assume that your input time is 3 or 4 digits representation of the time in 24 hour format that may have leading white space. You can then parse it like this:

Dim i As Int32
If Int32.TryParse(drv.Row("arrival_time")), NumberStyles.AllowLeadingWhite, CultureInfo.InvariantCulture, i) Then
  Dim hours As Int32 = i\100
  Dim minutes As Int32 = i Mod 100
  Dim ts As new TimeSpan(hours, minutes, 0)
  Dim dt As DateTime = DateTime.Now.Date + ts
  Session("formattime") = dt.ToString("hh:mm tt", CultureInfo.InvariantCulture)
Else
  ' Handle invalid time
End If

DateTime.Parse and friends is not able to parse time strings with only one digit for the hour hence this approach. Getting AM/PM right is not easy so that is delegated to the DateTime class.

Answer 4

Have a look at this http://blog.stevex.net/parsing-dates-and-times-in-net/

and

http://msdn.microsoft.com/en-US/library/8kb3ddd4.aspx

If you need the colon in there, and can guarantee that your DB field will always return a string with 4 characters you can do something like this to get the time in the right format.

string e = "1000"; // from db.
string t = string.Format("{0}:{1}", e.Substring(0, 2), e.Substring(2, 2));

Answer 5

How about:

string str = "1000";//drv.Row("arrival_time")
string[] formats = new string[] { "HHmm" };
DateTime dt = DateTime.ParseExact(str, formats,
                                    System.Globalization.CultureInfo.InvariantCulture,
                                    System.Globalization.DateTimeStyles.AdjustToUniversal);
string strTime = dt.ToShortTimeString();

DateTime dte = DateTime.Now;
IFormatProvider culture = new System.Globalization.CultureInfo("en-GB", true);

VB.NET

Dim str As String = "1000" 'drv.Row("arrival_time")
Dim formats As String() = New String() {"HHmm"}
Dim dt As DateTime = DateTime.ParseExact(str, formats, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.AdjustToUniversal)
Dim strTime As String = dt.ToShortTimeString()

Dim dte As DateTime = DateTime.Now
Dim culture As IFormatProvider = New System.Globalization.CultureInfo("en-GB", True)

Outputs:

10:00 AM

Alternatively, if you plan on using that frequently, you might want to add that as an extension method.

public static class Extensions
{
    public static string ToTime(this string str)
    {
        DateTime dt = DateTime.Now;
        try
        {
            string[] formats = new string[] { "HHmm" };
            dt = DateTime.ParseExact(str, formats,
                                                System.Globalization.CultureInfo.InvariantCulture,
                                                System.Globalization.DateTimeStyles.AdjustToUniversal);
        }
        catch
        { throw new Exception("Invalid data"); }
        return dt.ToShortTimeString();
    }
}

--VB.NET--

Public NotInheritable Class Extensions
    Private Sub New()
    End Sub
    <System.Runtime.CompilerServices.Extension> _
    Public Shared Function ToTime(str As String) As String
        Dim dt As DateTime = DateTime.Now
        Try
            Dim formats As String() = New String() {"HHmm"}
            dt = DateTime.ParseExact(str, formats, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.AdjustToUniversal)
        Catch
            Throw New Exception("Invalid data")
        End Try
        Return dt.ToShortTimeString()
    End Function
End Class

Example:

string str = "13000";//drv.Row("arrival_time")
string strTime = str.ToTime();

VB.NET

Dim str As String = "13000"
'drv.Row("arrival_time")
Dim strTime As String = str.ToTime()

Anything bad in the data will throw you an exception.

--EDIT--

Works fine in VB.NET.