Oracle DateTime Insert ORA-01861/ORA-01843 Bug #799

Open
opened 2017-12-08 08:26:09 +00:00 by jjjachyty · 6 comments
jjjachyty commented 2017-12-08 08:26:09 +00:00 (Migrated from github.com)

ORA-01861: literal does not match format string
ORA-01843: not a valid month\n"

Can be temporary modified engine.go 1589 line

// formatTime format time as column type
func (engine *Engine) formatTime(sqlTypeName string, t time.Time) (v interface{}) {
	switch sqlTypeName {
	case core.Time:
		s := t.Format("2006-01-02 15:04:05") //time.RFC3339
		v = s[11:19]
	case core.Date:
		v = t.Format("2006-01-02")
	case core.DateTime, core.TimeStamp:
		

            //v = t.Format("2006-01-02 15:04:05")
		//oracle DateTime Bug @ByJanly
		return t

	case core.TimeStampz:
		if engine.dialect.DBType() == core.MSSQL {
			v = t.Format("2006-01-02T15:04:05.9999999Z07:00")
		} else {
			v = t.Format(time.RFC3339Nano)
		}
	case core.BigInt, core.Int:
		v = t.Unix()
	default:
		v = t
	}
	return
}
ORA-01861: literal does not match format string ORA-01843: not a valid month\n" Can be temporary modified `engine.go` 1589 line ``` // formatTime format time as column type func (engine *Engine) formatTime(sqlTypeName string, t time.Time) (v interface{}) { switch sqlTypeName { case core.Time: s := t.Format("2006-01-02 15:04:05") //time.RFC3339 v = s[11:19] case core.Date: v = t.Format("2006-01-02") case core.DateTime, core.TimeStamp: //v = t.Format("2006-01-02 15:04:05") //oracle DateTime Bug @ByJanly return t case core.TimeStampz: if engine.dialect.DBType() == core.MSSQL { v = t.Format("2006-01-02T15:04:05.9999999Z07:00") } else { v = t.Format(time.RFC3339Nano) } case core.BigInt, core.Int: v = t.Unix() default: v = t } return } ```
yixin19 commented 2019-01-29 23:50:14 +00:00 (Migrated from github.com)

This bug still exist, while inserting a type time.Time

This bug still exist, while inserting a type time.Time
jjjachyty commented 2019-01-30 03:17:24 +00:00 (Migrated from github.com)

@yixin19 can you see my mark //oracle DateTime Bug @ByJanly?

@yixin19 can you see my mark //oracle DateTime Bug @ByJanly?
yixin19 commented 2019-01-30 14:09:31 +00:00 (Migrated from github.com)

@yixin19 can you see my mark //oracle DateTime Bug @ByJanly?

Yes, you mean that i have to modify the engine.go file.
But when i need to switch between databases, it's not easy.

> @yixin19 can you see my mark //oracle DateTime Bug @ByJanly? Yes, you mean that i have to modify the engine.go file. But when i need to switch between databases, it's not easy.
jjjachyty commented 2019-01-31 02:38:02 +00:00 (Migrated from github.com)

@yixin19 can you see my mark //oracle DateTime Bug @ByJanly?

Yes, you mean that i have to modify the engine.go file.
But when i need to switch between databases, it's not easy.

you can switch and case your db drivers or if oracle

> > @yixin19 can you see my mark //oracle DateTime Bug @ByJanly? > > Yes, you mean that i have to modify the engine.go file. > But when i need to switch between databases, it's not easy. you can switch and case your db drivers or if oracle
yixin19 commented 2019-02-02 14:36:18 +00:00 (Migrated from github.com)

@yixin19 can you see my mark //oracle DateTime Bug @ByJanly?

Yes, you mean that i have to modify the engine.go file.
But when i need to switch between databases, it's not easy.

you can switch and case your db drivers or if oracle

okay thx

> > > @yixin19 can you see my mark //oracle DateTime Bug @ByJanly? > > > > > > Yes, you mean that i have to modify the engine.go file. > > But when i need to switch between databases, it's not easy. > > you can switch and case your db drivers or if oracle okay thx
dawndiy commented 2019-06-24 07:42:37 +00:00 (Migrated from github.com)

If you use goracle driver, the driver can process time.Time, as the code from @jjjachyty you can directly return t so that goracle can process it.
If you don't want modify the code from xorm, you can create the *sql.DB and use goracle.NewSessionIniter to reset "NLS_DATE_FORMAT" with "yyyy-mm-dd hh24:mi:ss", then It will works!

engine, err := xorm.NewEngine("goracle", dsn)
ctr, err := goracle.NewConnector(dsn, goracle.NewSessionIniter(map[string]string{
	"NLS_DATE_FORMAT": "yyyy-mm-dd hh24:mi:ss",
}))
engine.DB().DB = sql.OpenDB(ctr)
If you use goracle driver, the driver can process time.Time, as the code from @jjjachyty you can directly return `t` so that goracle can process it. If you don't want modify the code from xorm, you can create the *sql.DB and use [goracle.NewSessionIniter](https://godoc.org/gopkg.in/goracle.v2#NewSessionIniter) to reset "NLS_DATE_FORMAT" with "yyyy-mm-dd hh24:mi:ss", then It will works! ```go engine, err := xorm.NewEngine("goracle", dsn) ctr, err := goracle.NewConnector(dsn, goracle.NewSessionIniter(map[string]string{ "NLS_DATE_FORMAT": "yyyy-mm-dd hh24:mi:ss", })) engine.DB().DB = sql.OpenDB(ctr) ```
lunny added this to the 1.1.1 milestone 2020-03-03 12:55:34 +00:00
lunny added the
db
oracle
label 2021-06-06 13:16:54 +00:00
lunny modified the milestone from 1.1.1 to (deleted) 2021-06-07 08:26:51 +00:00
lunny removed this from the (deleted) milestone 2023-07-26 01:23:28 +00:00
Sign in to join this conversation.
No Milestone
No Assignees
1 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: xorm/xorm#799
No description provided.