locked
SQL Time(7) data type to VB Datetimepicker Time format RRS feed

  • Question

  • Hi

    I have a datetimepicker object on my form and I have set the format to Time with up down arrows. I have a SQL table with a field which I have set as Time(0) datatype. I'm using a SqlDataReader to set the value of the objects on my form to the value from the selected SQL record. I'm struggling to convert the SQL Time(0) data type so that it shows in the Time formatted datetimepicker object as "hh:mm:ss". All my efforts are returning an error "Specified cast not valid". Any advice on how I should be reading this datatype to get it into my VB object in the format I want? Thanks

    Monday, November 23, 2020 4:41 PM

Answers

  • Hello,

    Look at SqlDataReader.GetTimeSpan(Int32) Method. Then use .ToString("") or use the following or modify to suit your needs.

    Imports System
    Imports System.Globalization
    
    Namespace ExtensionsLibrary
    	Public Module DateTimeExtensions
    		''' <summary>
    		''' Convert TimeSpan into DateTime
    		''' </summary>
    		''' <param name="sender"></param>
    		''' <returns></returns>
    		''' <remarks>
    		''' Intended to be used when the date part does not matter
    		''' </remarks>
    		<System.Runtime.CompilerServices.Extension> _
    		Public Function ToDateTime(ByVal sender As TimeSpan) As DateTime
    			Return DateTime.ParseExact(sender.Formatted("hh:mm"), "H:mm", Nothing, DateTimeStyles.None)
    		End Function
    		''' <summary>
    		''' Format a TimeSpan with AM PM
    		''' </summary>
    		''' <param name="sender">TimeSpan to format</param>
    		''' <param name="format">Optional format</param>
    		''' <returns></returns>
    		<System.Runtime.CompilerServices.Extension> _
    		Public Function Formatted(ByVal sender As TimeSpan, Optional ByVal format As String = "hh:mm tt") As String
    			Return DateTime.Today.Add(sender).ToString(format)
    		End Function
    	End Module
    End Namespace
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    My GitHub code samples
    GitHub page

    • Marked as answer by commanderbond Tuesday, November 24, 2020 11:24 AM
    Monday, November 23, 2020 5:57 PM

All replies

  • forum migrated to mentioned below link please use this

    https://docs.microsoft.com/en-us/answers/index.html

     hope answer the question if issue resolve please accept answer
    Monday, November 23, 2020 4:55 PM
  • Hello,

    Look at SqlDataReader.GetTimeSpan(Int32) Method. Then use .ToString("") or use the following or modify to suit your needs.

    Imports System
    Imports System.Globalization
    
    Namespace ExtensionsLibrary
    	Public Module DateTimeExtensions
    		''' <summary>
    		''' Convert TimeSpan into DateTime
    		''' </summary>
    		''' <param name="sender"></param>
    		''' <returns></returns>
    		''' <remarks>
    		''' Intended to be used when the date part does not matter
    		''' </remarks>
    		<System.Runtime.CompilerServices.Extension> _
    		Public Function ToDateTime(ByVal sender As TimeSpan) As DateTime
    			Return DateTime.ParseExact(sender.Formatted("hh:mm"), "H:mm", Nothing, DateTimeStyles.None)
    		End Function
    		''' <summary>
    		''' Format a TimeSpan with AM PM
    		''' </summary>
    		''' <param name="sender">TimeSpan to format</param>
    		''' <param name="format">Optional format</param>
    		''' <returns></returns>
    		<System.Runtime.CompilerServices.Extension> _
    		Public Function Formatted(ByVal sender As TimeSpan, Optional ByVal format As String = "hh:mm tt") As String
    			Return DateTime.Today.Add(sender).ToString(format)
    		End Function
    	End Module
    End Namespace
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    My GitHub code samples
    GitHub page

    • Marked as answer by commanderbond Tuesday, November 24, 2020 11:24 AM
    Monday, November 23, 2020 5:57 PM
  • Thank you,

    Both solutions worked.

    Tuesday, November 24, 2020 11:32 AM