Get SQL DateTime critique

J

joe_pool_is

Well-known member
Joined
Jan 18, 2004
Messages
451
Location
Texas
I like using store procedures because it allows me to specify what the format of my values are going to be ahead of time however, the SQL command SELECT GetDate does not have this option.

Below is how I solved it, and I am looking for advice as to whether or not it is a good technique or if there is something more elegant:

Particularly of interest: Do I need to cast the CurrentDateTime to a DateTime object, or should the SqlDataReader already be returning it in this format?
C#: 
DateTime sqlTime;
try {
  m_db.Open;
  using (SqlCommand cmd = new SqlCommand("SELECT GetDate AS [CurrentDateTime]", m_db)) {
    SqlDataReader r = cmd.ExecuteReader();
    while (r.Read() == true) {
      sqlTime = (DateTime)r["CurrentDateTime"];
    }
  }
} catch (InvalidOperationException e) {
  Console.WriteLine(e.StackTrace);
  sqlTime = DateTime.Now;
} catch (SqlException e) {
  Console.WriteLine(e.StackTrace);
  sqlTime = DateTime.Now;
} finally {
  m_db.Close();
}
 
You could always use the datareaders IsDbNull function to check for nulls before attempting to assign the value, or if you are using .Net 2 or higher then the sqlTime variable could be defined as a nullable datetime.

C#: 
        DateTime sqlTime;

            //  m_db.Open;
            using (SqlCommand cmd = new SqlCommand("SELECT GetDate AS [CurrentDateTime]"))
            {
                SqlDataReader r = cmd.ExecuteReader();
                while (r.Read() == true)
                {
                    if (!r.IsDBNull(0))
                        sqlTime = (DateTime)r["CurrentDateTime"];
                }
            }
or
C#: 
        DateTime? sqlTime;

            //  m_db.Open;
            using (SqlCommand cmd = new SqlCommand("SELECT GetDate AS [CurrentDateTime]"))
            {
                SqlDataReader r = cmd.ExecuteReader();
                while (r.Read() == true)
                {
                sqlTime = (DateTime)r["CurrentDateTime"];
                }
            }
 
Back
Top