How to Use SQL's Getdate() and Dateadd() in a Linq to SQL Expression

How do I use SQL's GETDATE() and DATEADD() in a Linq to SQL expression?

Try this:

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

EDIT: this needs to be a part of your DataContext class.

Now you can use GetSystemDate() instead of DateTime.Now in your queries.
As for date differences take a look at System.Data.Linq.SqlClient namespace, especially DayDiffXXX functions of SqlMethods class.

SQL GETDATE() in insert operations using Linq To Sql

Can you check this link. there is a attribute called IsDbGenerated which can be annotated with your LINQ partial classes.

http://msdn.microsoft.com/en-us/library/system.data.linq.mapping.columnattribute.isdbgenerated.aspx#Y456

GETDATE() in LINQ

As far as I can see with linq to sql the post you refer to is the way to go. I don't see how anything could be better. It's a pretty elegant solution.

With Entity Framework there is a possibility to use SqlFunctions.CurrentTimestamp. Besides that, even when you use DateTime.Now in linq to entities (not linq to sql) it is translated to GetDate:

context.Companies.Select (c => DateTime.Now);

translates:

SELECT 
GetDate() AS [C1]
FROM [dbo].[Company] AS [Extent1]

It's a different story if you only want to get the database date. In that case I don't see how anything could beat executing a query SELECT GetDate().

How to get CurrentTime from SQL Server with LINQ/Entities/VB.Net

Just execute a ExecuteStoreQuery command.

Dim dateTimeVal As DateTime = context.ExecuteStoreQuery(Of DateTime)("select getdate()").First()

Calculate mins in Linq

You can use the EntityFunctions class to perform operations on dates, among other things.

And (tbl.LastDate >= EntityFunctions.AddMinutes(DateTime.Now, -15))

if you are not using entityfunction you can do that

DateTime oldestDate = DateTime.Now.AddMinutes(-15);

...
then modified the where portion of the LINQ query

And (tbl.LastDate >= oldestDate )

Configuring a DateTime column to update from db time

I think you need to use a trigger to update the time. The way I handle it is to mark the property in the LINQ designer as readonly and server generated. I set up a trigger on update to modify the column on every update. For created dates, I do the same readonly/server generated and use getdate() as the column default. Setting the column to server generated is important as you don't want it to send data back for that column which may be incorrect -- either NULL, the old date, or DateTime.Minumum, depending on the type and the initial setting.

Another alternative would be to handle it in code and update the column using PropertyChanged event handlers for each column on the class. I think it's easier to go the trigger route. Note that this will use the web server time, not the DB time -- but they should be in sync anyway (they have to be if you're using secure connections). You could conceivably do a select to get the DB server time, but then you'd always be off by the round-trip time. As I said, I think the trigger is easier.

---

Related Topics