 |
| SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
November 24th, 2005, 01:10 PM
|
|
Registered User
|
|
Join Date: Nov 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Date Range
I'm really stumped on this :(
I've tried all sorts of different approaches to get this to work. Basically I am trying to get a list of names who's Birthdays fall within the next 7 days.
The clause I have at the moment looks like this.
Code:
WHERE CONVERT(datetime,(Month(" & strDBTable & "Author.DOB) + Day(" & strDBTable & "Author.DOB) + Year(GetDate())),101) BETWEEN CONVERT(datetime,GetDate(),101) AND CONVERT(datetime,DateAdd(D,7,GetDate()),101)
This returns no errors but also returns no results.
TIA
|
|
November 24th, 2005, 01:17 PM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
Maybe it's this:
datetime,(Month(" & strDBTable & "Author.DOB) + Day(" & strDBTable & "Author.DOB) + Year(GetDate())),
This doesn't use the year of birth, but the current year. So, this should only return records of people that were born during the next 7 days and they don't exist yet.... ;)
Imar
|
|
November 24th, 2005, 01:29 PM
|
|
Registered User
|
|
Join Date: Nov 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
If you use the actual year in the record then nothing would be with the date range for example someone born in the 11/26/1957 does not fall within the date range of 11/24/2005 to 12/01/2005 which is the root of my problem hence the reason for using Year(GetDate())
|
|
November 24th, 2005, 01:35 PM
|
|
Authorized User
|
|
Join Date: Sep 2005
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Lookup datediff in BOL
âI sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/
|
|
November 24th, 2005, 04:01 PM
|
|
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
Yeah, you're right. I see now what you're trying to do.
Sorry for the confusion.
DateDiff, as suggested by SqlMenace is indeed the way to do it. Construct a date as you've done now, and then use DateDiff to get the difference....
Cheers,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|
|
November 25th, 2005, 05:58 AM
|
|
Registered User
|
|
Join Date: Nov 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thanks for the advice, I'm trying to get it to work with DATEDIFF as of yet no luck but I'll keep going! :)
|
|
November 25th, 2005, 08:36 AM
|
|
Authorized User
|
|
Join Date: Sep 2005
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Here is an example
create table #test (testdate datetime)
insert into #test
select '2005-11-24 00:00:00.000' union all
select getdate() union all
select getdate() + 7
select * from #test where datediff(d,getdate(),testdate) = 7
âI sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/
|
|
|
