Multiload with date in where condition

Tools

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.

5 REPLIES
Teradata Employee

Re: Multiload with date in where condition

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

-- SteveF

Re: Multiload with date in where condition

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

Enthusiast

Re: Multiload with date in where condition

Hi,

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

Cheers,

Raja

Senior Apprentice

Re: Multiload with date in where condition

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

Re: Multiload with date in where condition

@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.