Using a query to return the next Sunday

Question

Post reply

Using a query to return the next Sunday

doug 40899

SSCommitted

Points: 1656
More actions
October 26, 2010 at 10:02 am

#230942

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.
I've also tried this query:
select
a.MyDate,
Sunday = dateadd(dd,((datediff(dd,'17530107',a.MyDate)/7)*7)+7,'17530107')
from
( -- Test Data
select MyDate payrolldate from payroll
) a
and got this error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'MyDate'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyDate'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MyDate'.
can anyone offer assistance on this?
Thank you,
Doug

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

you are referencing objects that don't exist.

What does this return?

select MyDate payrolldate from payroll

If it's the same error, then MyDate is not a column in this table. Pick a valid column.

select column_name from information_schema.tables where table_name = 'Payroll'

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 2

Does this return anything on your database?

select payrolldate,

Sunday = dateadd(dd, ((datediff(dd, '17530107', payrolldate)/7)*7)+7, '17530107')

from payroll

doug 40899 SSCommitted Points: 1656 More actions · Answer 3

andrew,

yes that does. How would I go about making sure it only returns the last entry though? In other words, I have 3 records in my table, all 3 with different dates, but I only want the most current date to be returned back to me.

Thank you

Doug

doug 40899 SSCommitted Points: 1656 More actions · Answer 4

Andrew,

I forgot, I have another field that has a yes or no value that I can use as a "qualifier" for the payroll date.

so I have this:

select payrolldate,

Sunday = dateadd(dd, ((datediff(dd, '17530107', payrolldate)/7)*7)+7, '17530107')

from payroll

where payrollran = 'no'

GSquared SSC Guru Points: 260824 More actions · Answer 5

Wouldn't the Max() operator get you what you need?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

doug 40899 SSCommitted Points: 1656 More actions · Answer 6

G,

It might but since I already have this in place and I'm going to be using the variable anyway, why not use it for this as well.

Doug

GSquared SSC Guru Points: 260824 More actions · Answer 7

Because you can't guarantee which row will be assigned to a variable if your assignment can return multiple rows, and you need the last one per your posted requirements. Or am I misunderstanding something in your requirements?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

David Burrows SSC Guru Points: 65142 More actions · Answer 8

doug 40899 (10/26/2010)
G,
It might but since I already have this in place and I'm going to be using the variable anyway, why not use it for this as well.
Doug

Notwithstanding what has already been stated, an alternative

select top 1 payrolldate,

Sunday = dateadd(dd, ((datediff(dd, '17530107', payrolldate)/7)*7)+7, '17530107')

from payroll

where payrollran = 'no'

order by payrolldate desc

but this would not be as efficient as using MAX

Far away is close at hand in the images of elsewhere.
Anon.

doug 40899 SSCommitted Points: 1656 More actions · Answer 9

David,

Then can you give me an example of max being used? This query is being included in an application that's being built and if I can improve it by writing better code, I'm all for it.

Thanks

Doug

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

Doug

Please supply table DDL and sample data, and then we will be able to provide a tested solution.

Thanks

John

David Burrows SSC Guru Points: 65142 More actions · Answer 11

doug 40899 (10/27/2010)
David,
Then can you give me an example of max being used? This query is being included in an application that's being built and if I can improve it by writing better code, I'm all for it.
Thanks
Doug

As John pointed out without DDL and sample data I'd guess at

select MAX(payrolldate) AS [payrolldate],

dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate))/7)*7)+7, '17530107') AS [Sunday]

from payroll

where payrollran = 'no'

Far away is close at hand in the images of elsewhere.
Anon.

doug 40899 SSCommitted Points: 1656 More actions · Answer 12

doug 40899

SSCommitted

Points: 1656

October 27, 2010 at 9:42 am

#1242993

David,

Thanks. I'll use that one to be more precise.