using a query to return the next Sunday

[dougancil]

I have the following query:

select
a.MyDate,
Sunday = dateadd(dd,((datediff(dd,'17530107',a.MyDate)/7)*7)+7,'17530107')
from
( -- Test Data
select MyDate = convert(datetime,'20100930')
) a

and what I'd like to do is instead of offering a datetime, I have a SQL field that I need to pull from with the date. Can anyone offer me a suggestion on how to do that?

What I see changing is the

select MyDate = [exceptions].exceptiondate.

I tried that though and I get an error:

The column prefix 'exceptions' does not match with a table name or alias name used in the query.

Thank you,

Doug

[ck9663]

This

 
select MyDate = [exceptions].exceptiondate. 
 
 

has to be a complete subquery. If your intention is to get it from a table, do a

 
select MyDate = exceptiondate
from exceptions
 
 

or a straight forward:

 
select
exceptiondate, 
Sunday = dateadd(dd,((datediff(dd,'17530107',exceptiondate)/7)*7)+7,'17530107')
from exceptions
 

Good Luck!!!

~~ CK

[dougancil]

CK,

here's what I went with:

select payrolldate,
Sunday = dateadd(dd, ((datediff(dd, '17530107', payrolldate)/7)*7)+7, '17530107')
from payroll
where payrollran = 'no'

that returns the value that I need back to me.

[ck9663]

Yes, the straight forward option....Am glad you made it to work...

Happy Coding!!!

~~ CK