problems with query for datefield = 2015

[#1]

Hi there,

i am new to qv and i hope you can help me again.

I want to write in a SQL Table like:

write_table:

LOAD

        Ratenschema,

        lieferant as lieferant_1,

        reason as reason_1,

        AVG(Erlös / cost) as faktor1,

        AVG((DB_neu + Erstattung) / cost) as faktor2,

        Datum_Datenbank as datum_test

resident write_faktor

WHERE

        YEAR(Datum_Datenbank) = 2015

GROUP BY

        Ratenschema,

        lieferant,

        reason,

        Datum_Datenbank;

For i= 0 to NoOfRows('write_table') -1

    LET V_FIELD1 = peek('Ratenschema',$(i),'write_table');

    LET V_FIELD2 = peek('lieferant_1',$(i),'write_table');

    LET V_FIELD3 = peek('reason_1',$(i),'write_table');

    LET V_FIELD4 = peek('faktor1',$(i),'write_table');

    LET V_FIELD5 = peek('faktor2',$(i),'write_table');

    LET V_FIELD6 = peek('datum_test',$(i),'write_table');

    SQL INSERT INTO dba.faktor_ratenschema VALUES(NULL, '$(V_FIELD1)', '$(V_FIELD2)', '$(V_FIELD3)', replace('$(V_FIELD4)', ',' , '.')  , replace('$(V_FIELD5)', ',' , '.') , '$(V_FIELD6)');

NEXT;

But the restriction for the year doesnt work!

Any idea how to input data from year 2015 into the database?

Thanks and regards

Sabrina

[anbu1984]

Is Datum_Datenbank Date field?

Why don't you use Where clause directly on Sql table instead of Resident table?

[#3]

This is the first part of my load script:

qvd_berechnungen:

LOAD * from C:\test\berechnungen.qvd (qvd);

INNER JOIN (qvd_berechnungen)

qvd:

LOAD * from C:\test\verkäufe_neu.qvd (qvd);

write_faktor:

LOAD *, [DB-II] as DB_neu

resident qvd_berechnungen;

drop table qvd_berechnungen;

You see, the datum_datenbank field comes from a qvd file and i can't identify the data type. 😞

[PrashantSangle]

Hi,

Datum_Datenbank is proper datefield???

What format it contains.

Try with

wildmatch(Year(Datum_Datenbank),'2015')

Regards

[PrashantSangle]

Hi,

After loading

datum_datenbank field gives you number or date???

Use Date#() to covert String to date

Regards

[#6]

In the preview of the table structure, the colum for datum_datenbank has the format YYYY-MM-DD

So i think its a date field?

I had allready tried out the Date#() Function but this doesn't work, too.

[#7]

Didn't work too 😞

[PrashantSangle · Accepted solution]

Hi,

Try

Year(Date#(Datum_Datenbank,'YYYY-MM-DD'))=2015

or Left(Datum_Datenbank,4)=2015

or

subfield(Datum_Datenbank,'-',1)=2015

Regards

[#9]

[PrashantSangle]

Hi,

Have you try this

Year(Date#(Datum_Datenbank,'YYYY-MM-DD'))=2015

or

Left(Datum_Datenbank,4)=2015

or

subfield(Datum_Datenbank,'-',1)=2015

Regards