Convert varchar to date in sql instruction

[fcorodriguezl]

Hi everybody!

I have a scrip to execute a SQL instruction to get a max date, but my field StrDATE is like varchar(8). I need get the max value, but i dont know how to convert to date in sql instruction.

I try with something like this, but not works.

$G_UPDATE = sql('DB','select max(Strdate ) from tb01' );

Please somebody can help me.?

Thanks for your help.

[former_member208402 · Accepted solution]

$G_UPDATE = sql('DB','select max(Strdate ) from tb01' ); ---- >$G_UPDATE this variable should be varchar

This will give max data and will be store in the variable $G_UPDATE

Now create a new global variable $G_MAX_STR_DATE and in the same BODS script write

$G_MAX_STR_DATE=to_date($G_UPDATE,'YYYYMMDD')

or directly write

$G_UPDATE = sql('DB','select max(to_date(Strdate,'YYYYMMDD' ) from tb01' );

[fcorodriguezl · Accepted solution]

Thanks Ravi,

I tried to do it in one instruction but I could not. Anyway, your suggestion worked very well.

Regards.

[xiaoming_wu]

Hi Francisco,

The sql() function will return varchar as listed by document.
https://help.sap.com/viewer/8092b085a68941f6aaa6708685a62b0d/4.2.9/en-US/575fdf8f6d6d1014b3fc9283b0e...

You can use function to_date() to convert varchar to Date.
https://help.sap.com/viewer/8092b085a68941f6aaa6708685a62b0d/4.2.9/en-US/576031786d6d1014b3fc9283b0e...

[fcorodriguezl]

Thanks Xia!

The problem is that apparently when I use the quotes in the sql to define format date 'yyyymmdd', for example:

$G_UPDATE = sql('DS','select max(to_date(DateStr,'yyyymmdd')) from tb01' );

When I check the syntax I get an error: "near <yyyymmdd> found <identifier> expecting < +,||..."

Regards.

[xiaoming_wu]

Hi Francisco,

By using to_date() function, I mean you can use this function for the return value of sql(), like this $G_UPDATE=to_data(sql('DB','select max(Strdate ) from tb01' ), 'yyyymmdd').