Wrox Programmer Forums
|
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
 
Old November 24th, 2005, 01:10 PM
Registered User
 
Join Date: Nov 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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



 
Old November 24th, 2005, 01:17 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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
 
Old November 24th, 2005, 01:29 PM
Registered User
 
Join Date: Nov 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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())

 
Old November 24th, 2005, 01:35 PM
Authorized User
 
Join Date: Sep 2005
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
 
Old November 24th, 2005, 04:01 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old November 25th, 2005, 05:58 AM
Registered User
 
Join Date: Nov 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the advice, I'm trying to get it to work with DATEDIFF as of yet no luck but I'll keep going! :)

 
Old November 25th, 2005, 08:36 AM
Authorized User
 
Join Date: Sep 2005
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find a date range between another date rang tayvonne Access 2 August 3rd, 2006 09:50 AM
Need Help With Date Range rpainter Crystal Reports 0 June 20th, 2005 01:01 PM
Help with Date Range PacMed Access 1 February 1st, 2005 12:30 PM
Date Range mrideout BOOK: Beginning ASP.NET 1.0 0 January 2nd, 2005 07:18 PM
Query with a date range... PLEASE HELP! tlcable7 Access ASP 2 October 1st, 2003 03:41 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.