无法将日期时间纪元转换为仅日期纪元格式

Question

I am trying to fetch date("jobDate") which is in Date format from one DB(db1) and insert into another DB(db2) where the Date("resolvedDate") is in epoch format. Now I'm passing the Date from db1 to the select query of db2. In order to match their datatypes, I'm converting time.Time to epoch format and passing to db2. I do not want timestamp, just the YYYY-MM-DD format. But the problem here is, the epoch date from db2 contains both date and time. As a result, when I pass jobDate to select query, it can not match any entry in the resolvedDate.

My code is below:

dsn := "server=********.md3q.***.com;user id=*******;password=******"
db, err := sql.Open("mssql", dsn) 
    if err != nil {
        log.Fatal(err)
    }
err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }
rows,err := db2.Query("select top 1 jobDate from mdesk.dbo.tbl_jobstatus where LastStatus = 'Success' ORDER BY jobDate DESC")
    if err != nil {
        log.Println(err)
    }
defer db.Close()
for rows.Next() {
    err := rows.Scan(&jobDate)
        if err != nil {
            log.Fatal(err)
        }
    log.Println("jobDate",jobDate)  //2013-01-11 00:00:00 +0000 UTC (I'm getting this)
}
    input:= jobDate.Unix()
    jobDate:= input3
    log.Println("EpochDate",jobDate) // 1357948800 this is the epoch format of the above date

dsn1 := "server=*******.ecc4q.******.com; user id=*******; password=*********"
db1, err := sql.Open("mssql", dsn1) 
    if err != nil {
        log.Fatal(err)
    }
err = db1.Ping()
    if err != nil {
        log.Fatal(err)
    }
rows1, err := db1.Query("select Incident_Number, resolvedDate, Corporate_ID from ITSM.dbo.HPD_Help_Desk_Classic where resolvedDate in (?)",jobDate) //main problem is here. 1357948800 is not matching with any entry in this query because all of the entries in resolvedDate are in datetime epoch format whereas I am  trying to search in date epoch format. 
    if err != nil {
        log.Println(err)
    }
defer db.Close()

Can anyone suggest what I'm doing wrong?

Answer 1

If the date type of the "resolvedDate" column is a DATE then try casting the time.Time to a DATE in SQL (see below). If "resolvedDate" is an int or string, then use the time.Format() method to pre-process it.

And always worry about time zones. If your database isn't in UTC then you may run into unexpected behavior.

rows1, err := db1.Query(
  "select Incident_Number, resolvedDate, Corporate_ID from ITSM.dbo.HPD_Help_Desk_Classic " + 
  "where resolvedDate in DATE(?)", // <--- See cast in SQL
  jobDate)