CODEWITHSUNDEEP
c#sqlsql-serverdatetime
YAKOVM
YAKOVM

Reputation: 10153

datetime in C# vs, SQL and GETDATE() from SQL Server

I use GETDATE() in a SQL Server stored procedure to insert a date into the SQL Server database table.

After that I need to implement a C# function which is based on datetime input parameter finds if the date was saved in the tables.

The datetime in C# and SQL are different. How do I convert from C# datetime to SQL datetime which has a form of yyyy-mm-ddT:yy:mm:ss.mmm? I need to specify explicitly yyyy-mm-ddT:yy:mm:ss.mmm.

Will be happy for all propositions/possible ways.

Upvotes: 3

Views: 9106

Answers (5)

Slack Shot
Slack Shot

Reputation: 1110

If you are using Entity Framework, and your database is using datetime and not datetime2, the trick is to use SqlDateTime to match the fact that .Net goes to nanosecond, versus sql's millisecond precision. You can use your DateTime variable in .net.. for a SqlDateTime instance, and then you can uniquely identify a record down to the millisecond. 

System.Data.SqlTypes.SqlDateTime entry2 = new System.Data.SqlTypes.SqlDateTime(new DateTime(dto.LookUpDateTime));
DateTime entry = entry2.Value;

var existticket = from db in context.Tickets
                              where db.LookupDateTime == entry && db.UserId == UserId
                              select db;

Upvotes: 2

satnhak
satnhak

Reputation: 9861

You should never write DateTime.Now from client code to insert into the database as this will be based on the clients local time; do this

public DateTime GetDatabaseTime()
{
    var parameter = new SqlParameter("time", SqlDbType.DateTime2)
    {
        Direction = ParameterDirection.Output
    };

    using (var connection = new SqlConnection(ConnectionString))
    {
        connection.Open();

        using (var command = new SqlConnection("SELECT @time = SYSDATETIME()", connection))
        {
            command.ExecuteNonQuery();
        }
    }

    return (DateTime)parameter.Value;
}

Also you should never use DATETIME in SQL Server you should always use DATETIME2 as DATETIME is less accurate than C#::DateTime and it will lead to rounding errors. I know this from bitter experience.

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460068

A datetime has no format at all, it has a value. SQL-DateTimes and C# DateTimes are compatible. So don't convert it (to string) at all but pass it as datetime-parameter to the database.

Then you're safe if the DateTime value is within SqlDateTime.MinValue(January 1, 1753) and SqlDateTime.MaxValue(December 31, 9999).

Upvotes: 2

Habib
Habib

Reputation: 223237

DateTime in .Net framework and SQL Server (if it is DateTime type field) is irrespective of the format. Format is only useful for displaying output.

If your field in SQL Server is of DateTime type then you can query it from C# code using parameterized query something like:

public DataTable GetRecords(DateTime dtParameter)
{
    DataTable dt = null;
    using (SqlConnection conn = new SqlConnection("connection string"))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * from yourTable where DateField = @dateparameter"))
        {
            conn.Open();
            cmd.Parameters.AddWithValue("@dateparameter",dtParameter);
            SqlDataReader dr = cmd.ExecuteReader();
            //...rest of the code
            dt.Load(dr);
        }
    }
    return dt;
}

Upvotes: 5

D Stanley
D Stanley

Reputation: 152521

Datetimes between C# and SQL are 100% compatible. The format shouldn't make any difference if you are passing them as DateTimes. If you are generating a SQL string then I would highly recommend changing to SQL Parameters so you don;t have to worry about any formatting issues.

Upvotes: 4

Related Questions