Data Manipulation inside Load Script

[#1]

Hi to all,

I have several issues to resolve. I need to:

1 convert string in date format in the load script

2 create two calculate field that are the difference in network days

For the first isse a tried

Table1:

SQL SELECT DateField,

Position

Number

FROM Table_from_the_system;

Load Position,

Number,

date(date#(DateField,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY')as NewDate RESIDENT Table_from_the_system;

But doesn't work !! The NewDate field is Empty

( Datefield is a string )

[Gysbert_Wassenaar]

networkdays(Date1,Date2) returns the net number of workdays between Date1 and Dat2 (as long as they are dates and not strings).

Check that the date format matches the format of the DateField string.

Also you use resident Table_from_the_system. But looking at your example don't you need resident Table1 instead?

[#3]

Thanks Gysbert,

the networkdays works.

But the main problem is the string "date(date#(DateField,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY')as NewDate"

that doesn't work and I don't know why.

I wrong to copy "Resident Table_from_the_system is wrong" the correct string is "Resident Table1"

[Anonymous]

I'd expect that the DateField in the database has date format already, so the syntax is:

date(DateField) as NewDate

or

date(floor(DateField)) as NewDate                              // to remove time part

or

date(floor(DateField), 'DD/MM/YYYY') as NewDate     // to enforce format

If it is not a date field, you need to use date#() as well - make sure you got the format right in this case.

Regards,

Michael

[MayilVahanan · Accepted solution]

Hi

Did you try

date(DateField,'DD/MM/YYYY') as NewField

or

date(timestamp(DateField,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY')

Am not sure why you use date#(), are you sure , its text format??

[#6]

It Works !!

with the second solution, but isn't clear for me why works.

What do the timestamp function ?

Thanks

[MayilVahanan]

HI

From help:

The timestamp function formats the expression as a date and time according to the string given as format-code. If the format code is omitted, the date and time formats set in the operating system are used.

And i think, from sql format is like DD/MM/YYYY hh:mm:ss format..so timestamp calculate the value exactly n give the result..