datepart() and select ASE 16

[jmtorres]

Hi Guys,

Running ASE 16 SP02 PL03 on HP-UX, and trying this SQL:

declare @a tinyint

declare @e datetime

select @e=getdate() , @a=datepart(dw,@e)

select @e,@a

if @a in (1,3) print "OK"

The result is :

@e @a

Oct 9,2018 10:55:51 AM NULL /**--> null value **/

but then, if modifying the 3 line to :

select @e=getdate()

select @a=datepart(dw,@e)

The result is :

@e @a

Oct 9,2018:10:56:00 AM 3 /*->*value **/

OK

Any known behaviour?

Thank you

Regards

Jose

[former_member188958 · Accepted solution]

Hi Jose,

SQL handles each of the clauses independently, as if they were being processed in parallel. The results do not flow from left to right, each clause sees the same initial value for the variable.

This is documented here:

https://help.sap.com/viewer/b65d6a040c4a4709afd93068071b2a76/16.0.3.5/en-US/aa7c9648bc2b1014a8b98802...

"Do not use a single select statement to assign a value to one variable and then to another whose value is based on the first. Doing so can yield unpredictable results."

Cheers,

-bret

[jmtorres · Accepted solution]

Thanks a lot Bret!

Regards

Jose

[jmtorres · Accepted solution]

Bret a one last question:

If I use the "wrong syntax" could this impact the result of the following IF conditional(which should be the same):

if @var in( x,y)

vs

if @var=<x> or @var=<y>

tThank you

Jose