No data from table with TO_DATE(SYSDATE,'dd.mm.yyyy')

[IrwinJuice]

Hi,

I get no data from table with query below

"select name from snavigation where idgr=1 and parent is null and date_b<=TO_DATE(SYSDATE,'dd.mm.yyyy')"

 query := "select name from snavigation where idgr=1 and parent is null and date_b<=TO_DATE(SYSDATE,'dd.mm.yyyy')"
 rows, err := h.DB.Query(query)
    if err != nil {
        log.Println("ERROR: ", err)
        return c.JSON(500, err)
    }
    var names []string

    for rows.Next() {

        var name string
        if err := rows.Scan(&name); err != nil {
            log.Fatal("ERR: ", err)
        }
        names = append(names, name)
    }

rows.Next() returns false, but this is valid oracle sql, because Pl/SQL Developer execute it properly and return data from table.

When I remove date_b<=TO_DATE(SYSDATE,'dd.mm.yyyy') I can get data from table.

What am I doing wrong? Or this is issue with driver?

Thanks in advance

[sijms]

the sql is valid and no error happen just no data return. so it is likely related to data would you please give example table with some data to test

[IrwinJuice]

Oracle version Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

create table SNAV
(
    IDNAV       NUMBER                     not null
        constraint PK_SNAV
            primary key,
    IDGR        NUMBER                     not null,
    NAME        VARCHAR2(120 char),
    DATE_B      DATE       default SYSDATE not null,
    PARENT      NUMBER
        constraint FK_SNAV_SELF
            references SNAV
                on delete cascade
)

insert into snav (idnav, idgr, name, date_b) values (7782,1, 'A012 Якесь значення 1', '24.08.1991');
insert into snav (idnav, idgr, name, date_b) values (1191,1, 'A02 Якесь значення 2', '24.08.1991');
insert into snav (idnav, idgr, name, date_b) values (8080,1, 'A03 Якесь значення 3', '24.08.1991');
insert into snav (idnav, idgr, name, date_b) values (5400,1, 'A04 Якесь значення 4', '24.08.1991');

select * from snav where idgr=1 and parent is null and date_b<=TO_DATE(SYSDATE,'dd.mm.yyyy');

Attached few screenshots from PL/Sql Developer and from Goland debug

[sijms]

why you are not using sql like this SELECT name from snav WHERE idgr=1 and parent is null and date_b < sysdate you are comparing dates so no need to use to_date function

[sijms]

to_date accept first parameter as string so oracle will convert sysdate into string using default date format (which is different in 2 drivers) to solve the problem use the following code after sql.Open

err = go_ora.AddSessionParam(db, "nls_date_format", "dd.mm.yyyy")
if err != nil {
    fmt.Println("can't add session param: ", err)
    return
}

[IrwinJuice]

Thank you! My fault, now I understand how to_date works. All work correctly with first parameter as string like 'TO_DATE('24.08.1991', 'dd.mm.yyyy' ). Sorry for your time