使用golang将DateTime值插入MS SQL

Question

I am trying to insert a DateTime value into a MS SQL table using golang. The SQL table is this structure:

CREATE TABLE dbo.TimeSample (
    ModifiedDate datetime
);

The golang code I have is this:

func timeSample(db *sql.DB) (error) {
    ctx := context.Background()
    var err error

    t := time.Now().Format(time.RFC3339)
    fmt.Println(t)
    tsql := fmt.Sprintf("INSERT INTO [dbo].[TimeSample] ([ModifiedDate]) VALUES ('%s');",time.Now().Format(time.RFC3339))

    // Execute non-query
    result, err := db.ExecContext(ctx, tsql)
    if err != nil {
        log.Fatal("Error inserting new row: " + err.Error())
        return err
    }

    fmt.Println(result)

    return nil
}

I am getting the following error when trying to insert:

2018-04-20T10:39:30-05:00 2018/04/20 10:39:30 Error inserting new row: mssql: Conversion failed when converting date and/or time from character string. exit status 1

Any idea on how I should format this to work for MS SQL?

Answer 1

To elaborate on my comment, what you should do is to use parametrized SQL - it not only takes care of converting time values to correct format it also protects agains SQL injection attack. Usually SQL parameters are represented by ? but some drivers use $1 or :name so check the driver's documentation. And your code would then be like:

tsql := "INSERT INTO [dbo].[TimeSample] ([ModifiedDate]) VALUES (?)"
result, err := db.ExecContext(ctx, tsql, time.Now())

Note that you don't want to have terminating ; in the SQL string too.