All Forums Tools
jbpagani 3 posts Joined 07/12
30 Oct 2013
Multiload with date in where condition

I'm using this MLOAD script:
(...)

CREATE MULTISET TABLE "DB"."TABLE" (FIELD1 TIMESTAMP(0) NOT NULL) ;
.BEGIN IMPORT MLOAD TABLES "DB"."TABLE" ;

.LAYOUT Layout1 INDICATORS ;

.FIELD  FIELD1 * CHAR(19) ;

.DML LABEL LabelA ;

INSERT INTO "DB"."TABLE" (FIELD1)

    VALUES ( :FIELD1 ) ;

.IMPORT INFILE Connection.amj

               AXSMOD Oledb_Axsmod 'noprompt'

               LAYOUT Layout1

               APPLY LabelA WHERE FIELD1 >= 01/01/2013;

.END MLOAD ;

.LOGOFF ;

 

I used 01/01/2013 as an example, but it's not working as MLOAD considers it a division (01 divided 01 divided 2013 = 0). How can I use a date in the where clause? I've tried casting but it doen't work.

Thanks in advance.

feinholz 1234 posts Joined 05/08
31 Oct 2013

Have you tried treating the value as a string (put quotes around it)?

--SteveF

jbpagani 3 posts Joined 07/12
01 Nov 2013

Yes, I've tried quoting it an it doesn't work.

Raja_KT 1246 posts Joined 07/09
01 Nov 2013

Hi,
Did you try casting  (date, format 'mm/dd/yyyy').
Cheers,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
01 Nov 2013

You need to compare the CHAR field1 to another CHAR. 
If Field1 is formatted like 'yyyy-mm-dd hh:mi:ss, then this should work:

 APPLY LabelA WHERE FIELD1 >= '2013-01-01 00:00:00';

If it's not YMD, bad luck.
 
 
Dieter
 

Dieter

jbpagani 3 posts Joined 07/12
01 Nov 2013

@Raja_KT: casting didn't work. I'm not sure but i believe it's no possible to cast in MLOAD.
@dnoeth: thanks, that was the correct sintax. It's working perfectly. I've tried it before with timestamp in DMY format and it didn't work.

You must sign in to leave a comment.