CODEWITHSUNDEEP
c#sql-server
Saurabh Gupta
Saurabh Gupta

Reputation: 81

Not getting correct date from database

Not getting correct date from database on the following lines. The actual date in database is 04/30/2016 09:30:00 PM but I'm getting it as 01/01/0001 12:00:00 AM. Not sure what's going on.

 DateTime fromDb =  sqlReader.GetDateTime(1);
 DateTime toDb = sqlReader.GetDateTime(2);

Here is the full method:

 private bool IsRoomAlreadyTaken(String room, DateTime fromUser, DateTime toUser)
    {
        bool roomAlreadyTaken = false;
        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
        {
            SqlCommand sqlCmd = new SqlCommand("SELECT Id, convert(varchar(30), DateFrom, 131), convert(varchar(30), DateTo, 131) FROM Access_Privilege where RoomId = @RoomId", sqlConnection);
            sqlCmd.Parameters.AddWithValue("@RoomId", room);
            sqlConnection.Open();
            SqlDataReader sqlReader = sqlCmd.ExecuteReader();
            while (sqlReader.Read())
            {
                DateTime fromDb =  sqlReader.GetDateTime(1); //On this line
                DateTime toDb = sqlReader.GetDateTime(2); //On this line

                if (DateTime.Compare(fromUser, fromDb) == 0 && TimeSpan.Compare(fromUser.TimeOfDay, fromDb.TimeOfDay) > 0 && TimeSpan.Compare(toUser.TimeOfDay, toDb.TimeOfDay) <0)
                {
                    roomAlreadyTaken = true;
                }
            }
            sqlReader.Close();
        }
        return roomAlreadyTaken;
    }

Edit after trying the following suggestions from @ManOVision:

From the docs: "No conversions are performed; therefore, the data retrieved must already be a DateTime object." I've had trouble in the past with SqlDataReader.Get[type] before. Try switching it to Convert.ToDateTime(sqlReader.GetValue(1).ToString()) or at least check what sqlReader.GetValue(1) returns from the database.

Results, with the following lines altered:

MessageBox.Show("sqlReader.GetValue(1)-->" + sqlReader.GetValue(1));
MessageBox.Show("sqlReader.GetValue(2)-->" + sqlReader.GetValue(2));
DateTime fromDb =  Convert.ToDateTime(sqlReader.GetValue(1).ToString());
DateTime toDb = Convert.ToDateTime(sqlReader.GetDateTime(2).ToString());

sqlReader.GetValue(1) returns 23/07/1437 9:30:12:000 PM and sqlReader.GetValue(2) returns 23/07/1437 10:30:12:483 PM. With GetValue(), time seems to be have returned correctly but somehow date is still messed up. This experiment proves that the record that is being returned from the DB is the correct one. But date value is still being lost in conversion.

On further execution of the program, Convert.ToDateTime(sqlReader.GetValue(1).ToString()) throws the following error:

String was not recognized as a valid DateTime.



at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
   at System.Convert.ToDateTime(String value)
   at SGFinalProjectRoomAllocationSystem.ManageAccessForm.IsRoomAlreadyTaken(String room, DateTime fromUser, DateTime toUser) in c:\Projects\SGFinalProjectRoomAllocationSystem\SGFinalProjectRoomAllocationSystem\ManageAccessForm.cs:line 222
   at SGFinalProjectRoomAllocationSystem.ManageAccessForm.ValidatePrivilegs(String emp, String room, DateTime from, DateTime to) in c:\Projects\SGFinalProjectRoomAllocationSystem\SGFinalProjectRoomAllocationSystem\ManageAccessForm.cs:line 246
   at SGFinalProjectRoomAllocationSystem.ManageAccessForm.grntAccssBtn_Click(Object sender, EventArgs e) in c:\Projects\SGFinalProjectRoomAllocationSystem\SGFinalProjectRoomAllocationSystem\ManageAccessForm.cs:line 283
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.Run(Form mainForm)
   at SGFinalProjectRoomAllocationSystem.Program.Main() in c:\Projects\SGFinalProjectRoomAllocationSystem\SGFinalProjectRoomAllocationSystem\Program.cs:line 19

Upvotes: 1

Views: 600

Answers (3)

katu
katu

Reputation: 367

Try DateTime.Parse method. MSDN

DateTime fromDb = DateTime.Parse(sqlReader[1].ToString()); DateTime toDb = DateTime.Parse(sqlReader[2].ToString());

Or Try DateTime.TryParse method. MSDN

DateTime fromDb;

if(DateTime.TryParse(sqlReader[1].ToString(), out fromDb)) //Conversion Successful. fromDb is set else //Conversion Unsuccessful

Upvotes: 1

Saurabh Gupta
Saurabh Gupta

Reputation: 81

Convert methods were causing the problem. Removing them from the query solved it.

Code with the problem:

SqlCommand sqlCmd = new SqlCommand("SELECT Id, convert(varchar(30), DateFrom, 131), convert(varchar(30), DateTo, 131) FROM Access_Privilege where RoomId = @RoomId", sqlConnection);

Working code.

SqlCommand sqlCmd = new SqlCommand("SELECT Id, DateFrom, DateTo FROM Access_Privilege where RoomId = @RoomId", sqlConnection);

Upvotes: 1

ManOVision
ManOVision

Reputation: 1893

From the docs: "No conversions are performed; therefore, the data retrieved must already be a DateTime object."

I've had trouble in the past with SqlDataReader.Get[type] before. Try switching it to Convert.ToDateTime(sqlReader.GetValue(1).ToString()) or at least check what sqlReader.GetValue(1) returns from the database.

Upvotes: 0

Related Questions