Home > Linq, SQL > Sql Server, GetDate and Linq to Sql.

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.

About these ads
Categories: Linq, SQL Tags: , , ,
  1. DW
    January 21, 2008 at 11:09 pm | #1

    Thanks for the tip, its cool to see that we can use attributes to access SQL functions like that.

  2. Jay
    February 14, 2008 at 8:11 pm | #2

    Is there an advantage to this method over ExecuteQuery?
    (VB)
    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?

  3. peteohanlon
    February 15, 2008 at 10:40 am | #3

    Jay – there’s no advantage to this, other than it’s more Linq trickery.

  4. Artiom
    June 11, 2008 at 7:07 pm | #4

    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..

    Regards,
    Artiom

  5. jman
    December 4, 2008 at 6:51 pm | #5

    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.

    • peteohanlon
      December 5, 2008 at 12:56 pm | #6

      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.

    • T-bone
      April 1, 2011 at 6:21 pm | #7

      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!)

  6. معترض
    November 2, 2009 at 9:13 am | #8

    thanks very much
    az taraf barobach

  7. John
    April 11, 2010 at 7:34 pm | #9

    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.

  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

Join 40 other followers

%d bloggers like this: