CODEWITHSUNDEEP
c#sqldapper
Silagy
Silagy

Reputation: 3083

Dapper insert DateTime value

I am using dapper CROD operation.

I am trying to enter a new row to a table with DataTime field.

I have created the following class:

 //Properties
    public int Id { get; set; }
    public string Title { get; set; }
    public string Message { get; set; }
    public int UserID { get; set; }
    public DateTime CreationDate { get; set; }
    public DateTime UpdateDate { get; set; }
    public DateTime ActiveStartDate { get; set; }
    public DateTime ExpirationDate { get; set; }

Now, the insert statement is as follow:

public static string InsertNewMessage
        => @"INSERT INTO Messages 
(Title,[Message],UserID,CreationDate,UpdateDate,ActiveStartDate,
ExpirationDate)
            VALUES (N'@Title',N'@Message',@UserID,CAST('@CreationDate' as 
DATETIME),CAST('@UpdateDate' as DATETIME),
CAST('@ActiveStartDate' as DATETIME)
,CAST('@ExpirationDate' as DATETIME))
SELECT CAST(SCOPE_IDENTITY() as int)";



int returnId = _db.Query<int>(QueriesRepository.InsertNewMessage,
    _message).SingleOrDefault();

Now what am i doing wrong - i have dig the web for a solution for the following error - with no luck I know the problem is the formatting of the DateTime object, but i saw that Dapper should support that.

Conversion failed when converting date and/or time from character string

Upvotes: 0

Views: 7057

Answers (1)

Yaroslav Voznyy
Yaroslav Voznyy

Reputation: 75

I believe it quite a late response. But anyway I faced the same issue yesterday, so I'll put my solution here. You don't need to CAST datetime directly in sql query. In my case datetime value was out of range, basically, it was 01-01-0001 00:00:00 so that caused the error. Make you sure you pass correct datetime value. Hope this helps!

Upvotes: 2

Related Questions