Sql Server, GetDate and Linq to Sql.

OK, you’ve put a default value of GETDATE() on a column in your database and you’re inserting a record via InsertOnSubmit. Oh no, the current server date isn’t applied to your record. How can you get the server date and apply it?

Well, it turns out that there’s a really simple trick that just requires a little bit of skullduggery on your part. (I like that word). Create a partial class that corresponds to your data context. In that class, put the following code:

[Function(Name="GetDate", IsComposable=true)]
public DateTime GetSystemDate()
  MethodInfo mi = MethodBase.GetCurrentMethod() as MethodInfo;
  return (DateTime)this.ExecuteMethodCall(this, mi, new object[]{}).ReturnValue;

Then, all your code needs to do is call GetSystemDate() whenever you need to the current date from SQL.

Note: You may wonder why I’ve gone to all of this trouble, rather than using DateTime.Now in the client code. By using this method, we get the date and time based on the database server, rather than on client connections which may be set to different times.

10 thoughts on “Sql Server, GetDate and Linq to Sql.

  1. Jay

    Is there an advantage to this method over ExecuteQuery?
    Private Function GetServerTime() as DateTime
    Dim result as IEnumerable(Of DateTime) = _
    Me.ExecuteQuery(GetType(DateTime), _
    “SELECT GETDATE() AS CurrentSQLDateTime”, _
    New Object() {})
    Return result.Single
    End Function

    If so, any idea of the VB syntax for those bracketed attributes?

  2. Artiom

    Actually there is an advantage, and quite a big one..
    If you use the code that Jay provided, the query will be ran separately, and the date will be passed as a parameter. Furthermore, it can’t be used in precompiled queries!

    With the code provided by peteohanlon, linq will replace your GetSystemDate call in the linq query with GetDate in the resulting SQL query! And it can be used in precompiled queries :)

    It can also be used just to get the datetime though. In this case both are pretty much identical..


  3. jman

    There is a much easier and more efficient way of doing this. Simply navigate to your linq to sql(.dbml) file, click on the appropriate column and in the properties window set the “Auto Generated Value” to true. Now you dont have to spend time creating this function and calling it every time you want this to occur.

    It would be nice if you didnt even have to set “Auto Generated Value” property at all however.

    1. peteohanlon

      Nice – I’ll have a try. I originally wrote this because I couldn’t find a way to get the auto value, and the documentation for L2S was sparse at the time.

    2. T-bone

      The preceding 2 examples do have a distinct advantage over the dbml method, though: you don’t have to declare getdate() as a default method on your datetime column to get it to populate the record field with the current (database) server time. I have a scenario where I want the datetime to be null by default, and only sometimes be filled in either with the current timestamp, or an explicitly supplied value.

      So thanks to Pete and Jay for your solutions also! (That being said, the dbml method is also really good to know. Thanks!)

  4. John

    Ideally you’ll store all dates as UTC.. use getutcdate() instead of getdate().. then just use DateTime.UtcNow in C# where needed. This way, you can easily translate this to any local time imaginable.

  5. What are these namespaces used? It couldn’t identify the attribue “Function” keyword.
    using System;
    using System.Data.Linq.Mapping;
    using System.Reflection;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s