Convert GetDateTime to Time only format

Question

I'm creating an events crud app and am trying to connect the "time" of the event from my database to my code. The data type in the database format is Time(7) and the asp.net code is GetDateTime. I need it to be in a HH:mm format (not the seconds).

I've tried using the reader.GetDateTime but it keeps saying the Specified cast is not valid. Below is the error message and code..

System.InvalidCastException
  HResult=0x80004002
  Message=Specified cast is not valid.
  Source=<Cannot evaluate the exception source>
  StackTrace:
<Cannot evaluate the exception stack trace>



SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        // create a new event and add to list.
                        EventModel events = new EventModel();
                        events.Id = reader.GetInt32(0);
                        events.StartTime = reader.GetDateTime(1);
                        returnList.Add(events);
                    }
                }

`time` corresponds to .NET Core's [TimeOnly](https://learn.microsoft.com/en-us/dotnet/api/system.timeonly?view=net-7.0) type or .NET Framework's TimeSpan, not `DateTime`. This isn't about formats. DateTime itself is a binary value, it has no format. — Panagiotis Kanavos, Aug 07 '23 at 14:41
Have you tried using [GetTimeSpan](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.gettimespan?view=dotnet-plat-ext-7.0) ? — Panagiotis Kanavos, Aug 07 '23 at 14:44
What do you mean? That's a method of SqlDataReader, same as GetDateTime. You even used the `timespan` tag in the question. Have you tried using `events.StartTime = reader.GetTimeSpan(1);` ? — Panagiotis Kanavos, Aug 07 '23 at 14:59
I change it to events.StartTime = reader.GetTimeSpan(1); and it gives the following error message: Error CS0029 - Cannot implicitly convert type 'type' to 'type' — NateUser, Aug 07 '23 at 15:07
What types? Post the *full* errorIf `StartTime` isn't a `TimeSpan`, you'll get exactly this error. That means `StartTime` has the wrong type, not that `GetTimeSpan` can't load `time` values — Panagiotis Kanavos, Aug 07 '23 at 15:11
System.InvalidCastException HResult=0x80004002 Message=Specified cast is not valid. Source= StackTrace: — NateUser, Aug 07 '23 at 15:19
Add proper logging to your code instead of relying on the crash window. — Panagiotis Kanavos, Aug 07 '23 at 15:20
This isn't a `does GetTimeSpan work` question. It does. The question lacks almost all the necessary details, like the class and table definitions. Did you try debugging the code in Visual Studio? You won't get a crash report in a debugger like VS. Execution will stop at the point where the error occurred, allowing you to inspect the variables and the actual exception — Panagiotis Kanavos, Aug 07 '23 at 15:22
Exception thrown: 'System.InvalidCastException' in System.Data.dll An exception of type 'System.InvalidCastException' occurred in System.Data.dll but was not handled in user code Specified cast is not valid. — NateUser, Aug 07 '23 at 15:27
I'm guessing it's a `null` value, in which case you need `reader.IsDBNull` You should really use Dapper or similar for all this sort of thing. — Charlieface, Aug 07 '23 at 15:34
This is the class which is being used to create events. It only seems to be giving an error on the GetTimespan field. — NateUser, Aug 07 '23 at 15:46
namespace WebApplication.Data { internal class EventDAO { private string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=EventTrackerDatabase;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"; public List FetchAll() { List returnList = new List(); — NateUser, Aug 07 '23 at 15:47
using (SqlConnection connection = new SqlConnection(connectionString)) { string sqlQuery = "SELECT * from dbo.EventsTable"; SqlCommand command = new SqlCommand(sqlQuery, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { EventModel events = new EventModel(); events.Id = reader.GetInt32(0); events.StartTime = reader.GetDateTime(1); returnList.Add(events); } } } return returnList; } } } — NateUser, Aug 07 '23 at 15:49
Post any code in the question itself. You haven't posted any classes yet. Did you try to debug your code? Does the second field contain time values? Is it null? You're asking people to guess what your data contains. As for posting the exception, that means posting the *full* exception text returned by `Exception.ToString()`, not just the message. The exception string contains any inner exceptions and the full stack trace that shows the chain of calls that led to the error. — Panagiotis Kanavos, Aug 07 '23 at 15:53
Apologies. There are no nulls in the database. I'm new to c# and am trying to get you this information. Can you give me an example of what you need or where I can find it? — NateUser, Aug 07 '23 at 16:12
I'm trying to pull SQL data using the SqlDataReader. The data type for the StartTime column is Time(7) and there are no nulls. I'm using events.StartTime = reader.GetTimeSpan(1) at the moment and it's giving an error message Cannot implicitly convert type 'System.TimeSpan' to 'System.DateTime'. This is the StartTime field in the Model [Required] [DataType(DataType.Time)] [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:H:mm}")] public DateTime StartTime { get; set; } I have a default value of: public EventModel() { Id = -1; StartTime = DateTime.Now; } — NateUser, Aug 07 '23 at 16:38
Yeah that error is a compile time error nothing to do with SQL: C# can't convert `TimeSpan` which it receives from `GetTimeSpan` into a `DateTime` to match the type of `StartTime`. So why don't you declare `StartTime` as `TimeSpan`? Alternatively change the column type in the database to `datetime` depending on requirements. — Charlieface, Aug 07 '23 at 23:41

Convert GetDateTime to Time only format

0 Answers0