SQL function returning NULL value .

[venkataramana_paidi]

Hi ,

I have to capture the max date from the table and I am using the below function .

$G_LastUpdate = sql('Omega00', 'select max(LAST_UPDATE) from EMP_DIM');

print($G_LastUpdate);

But it is returning the NULL value instead of the value. I have tried different varchar field in the  table but it is returning the value properly.

Please suggest what is wrong here . Why not it is working for datetime and working for varchar

I have tried in the SQL editor it is working fine . But It is not giving the DS script .

Thanks & Regards,

Venkata Ramana Paidi

[venkataramana_paidi · Accepted solution]

If I change the data type for $G_LastUpdate  from datetime to date it is working. But  I don't understand why it is not working for datetime datatype

[Former Member · Accepted solution]

Your backend database?

[venkataramana_paidi · Accepted solution]

Sybase ASE

[Former Member · Accepted solution]

There is a limitation with timestamp column.

I found it in SAP BODS technical manual 4.2..

[venkataramana_paidi · Accepted solution]

Thanks Swapnil, May be it wont working with Sybase ASE datetime data type .

[Former Member · Accepted solution]

No.. It is generalized limitation not specific to any database!!! For all database it would be same.

[Former Member · Accepted solution]

Have you tried this -

$G_LastUpdate = sql('Omega00', 'select to_char(max(LAST_UPDATE),\'DD-MM-YYYY\') from EMP_DIM');

$G_ConvertedDate = to_date($G_LastUpdate,'DD-MM-YYY');

Arun

[former_member187605 · Accepted solution]

To avoid this type of problems never use date(time) variables as results of a DS sql function (which actually returns varchar results only) and always convert your database date(time)s to varchar in the select statement. Both internal and external date(time) formats may differ significantly from one environment to another.

 
$G_LastUpdate = sql('Omega00', 'select to_char(max(LAST_UPDATE),\'DD-MM-YYYY\') from EMP_DIM'); 
$G_ConvertedDate = to_date($G_LastUpdate,'DD-MM-YYY');

To make this work, make sure to declare $G_LastUpdate as varchar(10) and $G_ConvertedDate as date.