CODEWITHSUNDEEP
asp.netdatetimesession
Jamie Taylor
Jamie Taylor

Reputation: 3530

Convert database field to time format

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

Upvotes: 0

Views: 1014

Answers (5)

Martin Liversage
Martin Liversage

Reputation: 106826

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.

Upvotes: 0

Kamran Khan
Kamran Khan

Reputation: 9986

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.

alt text

Upvotes: 2

Dave
Dave

Reputation: 2562

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));

Upvotes: 0

Nellius
Nellius

Reputation: 3114

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.

Upvotes: 0

Shiraz Bhaiji
Shiraz Bhaiji

Reputation: 65391

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

Upvotes: 0

Related Questions