we have date in “dd/mm/yyyy” format . mysql accept “yyyy-mm-dd” format.
how to convert “dd/mm/yyyy” for to “YYYY-mm-dd” format.
we have to insert into mysql database.
we have date in “dd/mm/yyyy” format . mysql accept “yyyy-mm-dd” format.
how to convert “dd/mm/yyyy” for to “YYYY-mm-dd” format.
we have to insert into mysql database.
Can you show an example of how you’re inserting? Are you storing dates as one of MySQL’s string data types like CHAR or VARCHAR? If not, then the format of the date strings shouldn’t matter.
Just use SQL builder that converts a time.Time
properly when used in the query…To be honest, I expect all basic SQL libraries to be able to deal with that…
don’t you store dates as time.Time
?
Isn’t the db driver able to use the right format for them ?
please show the code
[{
“material_code”: 3702,
“Sitename”: “https://xyz.abc.com”,
“datatype”: “OUTSIDE”,
“XlOpendate”: “31-05-2022”
}]
Xlopendate format “DD/MM/YYYY”
type Excelwebsitelist struct {
Material_code int json:"material_code" validate:"required"
Sitename string json:"sitename" validate:"required"
datatype string json:"studenttype" validate:"required"
XlOpendate time.Time json:"xlopendate" validate:"required"
}
we are using gin framework.
we are want to insert data into mysql table ( XlOpendate field of type date )
Please use backticks to make your code looking proper, like this:
```go
// code goes here
```
Will render like:
// code goes here
So how are you querying the database? The XlOpendate
field in the struct is already time.Time
, unless your DB query is written weirdly, I expect that to just work.
it is working now
mydate, err := time.Parse("02-01-2006", data.XlOpendate) //dd/mm/yyyy
if err != nil {
err := errors.New("Parsing error" + err.Error())
return err
}
str_date := mydate.Format("2006-02-01") // formated in YYYY/MM/DD
date, err := time.Parse("2006-02-01", str_date) // transafer to Date object
if err != nil {
err := errors.New("Parsing error" + err.Error())
return err
}
It is solved . This is correct method .
I still do not see a DB query here. What ever you do, it works around the actual proble…
At the end, mydate
and date
will actually be equal! Just try it, mydate == date
will hold forever!
I am still confused .
if I take Xlopendate field as a Time.time. I am getting this error
{
“Binding exception”: “parsing time “\“31-04-2022\”” as “\“2006-01-02T15:04:05Z07:00\””: cannot parse “4-2022\”” as “2006"”
}
If I use string I have to convert .
So the XlOpendate
is not a time.Time
? Then the info you gave earlier was wrong and misleading.
Anyway, from your last snippet you can just use mydate
without any further back and forth converting.
Hi,
sorry but you’re making some confusion.
Can you post the JSON and the exact struct you’re using?
then you should also show the code used to insert into the DB.
The workflow should be
[{
"Website_code": 3702,
"Sitename": "https://xyz.abc.com",
"datatype": "OUTSIDE",
"XlOpendate": "31-05-2022",
"Remarks":"dummy data"
}]
type Excelwebsitelist struct {
Website_code int `json:"website_code" validate:"required"`
Sitename string `json:"sitename" validate:"required"`
Datatype string `json:"datatype" validate:"required"`
XlOpendate string `json:"xlopendate" validate:"required"`
Remarks string `json:"remarks" validate:"required"`
Opendate time.Time
}
func Uploaddata(_in []req.Excelwebsitelist) error {
tx, err := Mydb.Beginx()
if err != nil {
err := errors.New("database error" + err.Error())
return err
}
defer func(tx *sqlx.Tx) {
if err != nil {
_ = tx.Rollback()
} else {
_ = tx.Commit()
}
}(tx)
for index, data := range _in {
mydate, err := time.Parse("02-01-2006", data.XlOpendate) //dd/mm/yyyy
if err != nil {
err := errors.New("Parsing error" + err.Error())
return err
}
str_date := mydate.Format(Mysqldateformat) // formated in YYYY/MM/DD
date, err := time.Parse(Mysqldateformat, str_date) // transafer to Date object
if err != nil {
err := errors.New("Parsing error" + err.Error())
return err
}
_in[index].Opendate = date
}
_InsertQuery := `
insert into website (
website_code,sitename,datatype,opendate ,remarks
) values (:website_code,:sitename,:datatype,:opendate ,:remarks)
ON DUPLICATE KEY UPDATE
datatype =VALUES(datatype),opendate = VALUES(opendate),remarks = values(remarks)`
_, err = tx.NamedExec(_InsertQuery, _in)
if err != nil {
return err
}
return nil
}
from user we have taken “datetime” in string and runtime we have converted to TIME.time , and saved in database .
which I think wrong way of programming .
I see you’re doing a double conversion and I don’t understand why
mydate, err := time.Parse("02-01-2006", data.XlOpendate)
: string → Timestr_date := mydate.Format(Mysqldateformat)
: Time → stringdate, err := time.Parse(Mysqldateformat, str_date)
: string → Timewhy don’t use mydate
directly?
Tip: why don’t define the Field as Time and implement UnmarshalJSON()
for the struct ?
mydate, err := time.Parse("02-01-2006", data.XlOpendate)
: string → Timestr_date := mydate.Format(Mysqldateformat)
: Time → stringdate, err := time.Parse(Mysqldateformat, str_date)
: string → Timeisn’t the DB driver able to convert a Time
in the correct format without passing to string?
which driver are you using ?
isn’t the driver able to work directly on Time variables?
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.