Skip to Content
0

Convert varchar to date in sql instruction

Nov 23, 2017 at 09:25 PM

78

avatar image
Former Member

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Ravi kiran Pagidi Nov 24, 2017 at 09:22 AM
1

$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' );

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks Ravi,

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

Regards.

0
Xiaoming Wu
Nov 24, 2017 at 03:02 AM
1
Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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.

0

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').

0