How OrmLite Update a DateTime to getdate()

      Db.UpdateOnly(() => new QueueRecord
                    CallTime = DateTime.Now,
                    State = 1,
                    WindowID = WindowID
                }, p => p.ID== currentItem.ID);

the above code is update the CallTime to Local Computer DateTime, I Want to update the CallTime to Sqlserver server datetime, If use the sql code is

update QueueRecord set CallTime = getdate()

How To Set the getdate() in Ormlite ?

You can’t with a Typed API so you’ll need to use db.ExecuteSql to execute a Custom Update statement.

For INSERT’s you can declare columns with the default value of RDBMS UTC Time, e.g:

public class Poco
    [Default(OrmLiteVariables.SystemUtc)]  //= {SYSTEM_UTC}
    public DateTime CreatedTimeUtc { get; set; }

Look at tests/ServiceStack.OrmLite.Tests/Shared/UpdateCommandFilter.cs for a filter which does what you want and tests/ServiceStack.OrmLite.Tests/DefaultValueTests.cs for how to use it. (Sorry, I don’t know how to generate a github url for those.)

Hi Bruce thanks for the pointer. FYI to add a link in Markdown you can use this syntax:


Here’s link to UpdateCommandFilter.cs you can copy which I’ve just changed to use an extension method to make it a bit nicer to use. Here’s different examples showing its usage, e.g:

db.Update(cmd => cmd.SetUpdateDate<DefaultValues>(nameof(DefaultValues.UpdatedDateUtc)),
    new DefaultValues { Id = 1, DefaultInt = 45 }, new DefaultValues { Id = 2, DefaultInt = 72 });

If I Use


In SqlServer,

this.Variables = new Dictionary<string, string>
                { OrmLiteVariables.SystemUtc, "SYSUTCDATETIME()" },

the return date is not my want date, I want return getdate() , How to override it , or added some OrmLiteVariables?

You can add a new variable with:

SqlServerOrmLiteDialectProvider.Instance.Variables["{GETDATE}"] = "GETDATE()";

Which you should then be able to use with:


I don’t get values set when using the Default attribute and the variable method as shown.

I have a table:

RecID UniqueIdentifier NOT NULL,

A poco for that table:

public partial class TestTable
public string RecID { get; set; }        
public DateTime LastSavedDateTime { get; set; }

And running this code yields an error

ServiceStack.OrmLite.SqlServer.SqlServer2016OrmLiteDialectProvider.Instance.Variables["{GETDATE}"] = "GETDATE()";
        var dbFactory = new OrmLiteConnectionFactory("Data Source=localhost;Initial Catalog=TestDB;User ID=Dev;Password=*******", ServiceStack.OrmLite.SqlServer2016Dialect.Provider);
        using (var db = dbFactory.Open())
            TestTable testItem = new TestTable { RecID = System.Guid.NewGuid().ToString() };

The error is:

System.Data.SqlClient.SqlException: 'Cannot insert the value NULL into column 'LastSavedDateTime', table 'TestDB.dbo.TestTable'; column does not allow nulls. INSERT fails.
The statement has been terminated.'

I’ve tried this on SS 5.0 from MyGet and also various different versions of the SQLDialect Providers.

What I really wanted was a subquery as the variable - e.g.:

ServiceStack.OrmLite.SqlServer.SqlServer2016OrmLiteDialectProvider.Instance.Variables["{NextSequenceNo}"] = "(SELECT COALESCE(MAX(SequenceNo), 0) + 1 FROM TestTable)";

I’ll have to resort to a custom insert, but it’d be nice if possible to have these variables working as I prefer the strong type philosophy.

The [Default] field creates a column with a default value when it creates the Table Schema, it has no effect on an already existing Table.