When is Now() calculated

Asked May 27 '16 at 14:26

Active May 27 '16 at 15:29

Viewed 49 times

If I have a SQL statement in a way like this: Is the result of Now() the same in the complete statement? Or, if some sub-queries take several minutes, different at several places?

select 
     *
from 
     table_x 
          join (select * from table_z where <different criteria and y = now() ) tablez on <joincontext>   
where 
     <different criteria> and x = now()
union 
select 
     * 
from 
     table_x 
where 
     <different criteria> and x = now()

edited May 27 '16 at 15:29

Stivan

1,128
1
15
24

asked May 27 '16 at 14:26

PSVSupporter

4

According to the ANSI SQL specification, it should be "calculated" once for the whole statement, i.e. you should expect to get the same value everywhere within the same statement. – jarlh May 27 '16 at 14:26
Calculated may be the wrong term, what is the correct English term then? – PSVSupporter May 27 '16 at 14:27
1

I'd say evaluated. – jarlh May 27 '16 at 14:28
1

The correct term should be `Evaluated` because `NOW()` is a function. So the function is evaluated once – Juan Carlos Oropeza May 27 '16 at 14:28
@jarlh - are you sure? Because I was always led to believe that the ansi standard specifies `CURRENT_TIMESTAMP`. `NOW`() is non-standard (and doesn't exist in SQL Server, so I'm really confused by this question) – Damien_The_Unbeliever May 27 '16 at 14:29
You could test it on a big table with `SELECT *, now() FROM bigTable` all row will have same time – Juan Carlos Oropeza May 27 '16 at 14:30
@Damien_The_Unbeliever, oops, of course I meant current_timestamp... Simply assumed now() had the same behavior. – jarlh May 27 '16 at 14:30
Is there some version of SQL Server where NOW() is a valid function? – Tab Alleman May 27 '16 at 14:46
`now()` exists in both mysql and postgresql, so it might just be that this question is mistagged. – Andy Nichols May 27 '16 at 15:21

When is Now() calculated

0 Answers0