How to get 30 days from now from SQL table

Question

I am doing my project in MVC4 using c# and sql.. I have a table MemberDetails which contain table

CREATE TABLE [dbo].[MemberDetails] (
[Id]          INT           IDENTITY (1, 1) NOT NULL,
[Mem_FirstNA] VARCHAR (100) NOT NULL,
[Mem_LastNA]  VARCHAR (100) NOT NULL,
[Mem_Occ]     VARCHAR (100) NOT NULL,
[Mem_DOB]     DATETIME      NOT NULL,
[Mem_Email]   VARCHAR (50)  NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
 );

I just want to select the names and date of birth where whose birthday in next 30 days and I use the following query

SELECT 
    Mem_FirstNA, Mem_LastNA, Mem_DOB 
FROM 
    MemberDetails 
WHERE 
    Mem_DOB >= getdate() - 1 AND Mem_DOB <= getdate() + 30

Is that correct, I got 0 item selected , I use the following table.

 1  Pal Software    08-03-1987 AM 12:00:00
 3  mn  Par Bussiness   19-10-1967 AM 12:00:00
 4  man George  Business    13-11-1956 AM 12:00:00
 5  Smi Kan Housewife   22-10-1980 AM 12:00:00

Are you writing **SQL** (structured query language) and really mean Microsoft **SQL Server** (the actual product) by this? If yes: please add `sql-server` tag to make this clear. If not: **what** database system is this for? — marc_s, Oct 18 '13 at 04:53
I know this is an ancient question but everyone has missed a key point: `Mem_DOB` contains dates from the 1900s and then people want to check `Mem_DOB >= getdate()`! Last century dates will never be greater than today's date. — youcantryreachingme, Aug 23 '19 at 01:17

Yuriy Rozhovetskiy · Answer 1 · 2013-10-18T06:41:19.860

2

Updated

SELECT 
    Mem_FirstNA, Mem_LastNA, Mem_DOB 
FROM 
    MemberDetails 
WHERE 
    DATEDIFF(DAY, GETDATE(), DATEADD(YEAR, DATEDIFF(YEAR, Mem_DOB, GETDATE()), Mem_DOB)) BETWEEN 0 AND 30

edited Oct 18 '13 at 06:41

answered Oct 18 '13 at 04:58

Yuriy Rozhovetskiy

22,270
4
37
68

1

I don't think this query will work. `Mem_DOB` is in the past, and `getdate()` is today. How will they ever be equal? Example: say dob `18-Oct-1980`, how will it match with `dateadd(day, -n, getdate())`? Because `getdate()` will return `2013`. – unlimit Oct 18 '13 at 05:24
@unlimit, you're right. I forget to set current year to date of birth – Yuriy Rozhovetskiy Oct 18 '13 at 06:42

score 2 · Answer 2 · answered Oct 18 '13 at 05:12

2

try this

        SELECT 
           Mem_FirstNA, Mem_LastNA, Mem_DOB
        FROM 
             MemberDetails 
       WHERE DAYOFYEAR(Mem_DOB)-DAYOFYEAR(getdate())<=30

answered Oct 18 '13 at 05:12

Nitu Bansal

3,826
3
18
24

`DAYOFYEAR`? Neat, did not think of it. – unlimit Oct 18 '13 at 05:14
1

Fails at year boundaries. What happens if DOB := '1990-01-01' and getdate() == '2013-12-31' ? (I gave you +1 in haste, however, so enjoy it! :)) – pilcrow Oct 23 '13 at 14:13

score 2 · Answer 3 · answered Oct 21 '13 at 13:33

2

SELECT 
    Mem_FirstNA, Mem_LastNA, Mem_DOB 
FROM 
    MemberDetails 
WHERE 
    Mem_DOB >= Cast(current_timestamp As Date) AND Mem_DOB < DATEADD(d, 30, Cast(current_timestamp As Date));

answered Oct 21 '13 at 13:33

Joel Coehoorn

399,467
113
570
794

evhen14 · Answer 4 · 2013-10-22T00:23:57.980

Should work. Between usage was removed based on comments. Will work for January birth dates as well

DECLARE @now DATETIME
    SET @now = GETDATE()

 SELECT Mem_FirstNA, Mem_LastNA, Mem_DOB
   FROM MemberDetails
  WHERE 
   CASE WHEN month(Mem_DOB) = 1
   THEN DATEADD(YY, YEAR(DATEADD(DAY, 30, DATEADD(m,  month(@now) - 1, DAY(@now) - 1))) - 1900, DATEADD(m,  month(Mem_DOB) - 1, DAY(Mem_DOB) - 1))
   ELSE DATEADD(m,  month(Mem_DOB) - 1, DAY(Mem_DOB) - 1)
    END
      > DATEADD(m,  month(@now) - 1, DAY(@now) - 1) 
    AND 
   CASE WHEN month(Mem_DOB) = 1
   THEN DATEADD(YY, YEAR(DATEADD(DAY, 30, DATEADD(m,  month(@now) - 1, DAY(@now) - 1))) - 1900, DATEADD(m,  month(Mem_DOB) - 1, DAY(Mem_DOB) - 1))
   ELSE DATEADD(m,  month(Mem_DOB) - 1, DAY(Mem_DOB) - 1)
    END
      < DATEADD(DAY, 30, DATEADD(m,  month(@now) - 1, DAY(@now) - 1))

Hope it helps

Er, Please don't use `BETWEEN` with date/time types, [especially on SQL Server](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common). Personally, `BETWEEN` should be removed from all statements (and the standard)... — Clockwork-Muse, Oct 21 '13 at 04:57

Amit · Answer 5 · 2013-10-18T05:14:12.167

0

Try this

SELECT 
    Mem_FirstNA, Mem_LastNA, Mem_DOB 
FROM 
    MemberDetails 
WHERE 
    WHERE DATEPART(mm,Mem_DOB) BETWEEN DATEPART(mm,getDate()) AND DATEPART(mm,getDate())+1;

edited Oct 18 '13 at 05:14

answered Oct 18 '13 at 04:55

Amit

15,217
8
46
68

Agarwal: still i got 0 rows selected – neel Oct 18 '13 at 05:00
I don't think this is going to work. You will have to consider the current year. – unlimit Oct 18 '13 at 05:01
@AmitAgrawal: Is any date format problem in my database and query – neel Oct 18 '13 at 05:07
Run query in Query Editor, then tell me what is the output. – Amit Oct 18 '13 at 05:08
@AmitAgrawal - `Mem_DOB` is in the past, and `getdate()` is today. How will they ever be equal? Example: say dob `18-Oct-1980`, how will it match with `dateadd(day, -n, getdate())`? Because `getdate()` will return `2013`. – unlimit Oct 18 '13 at 05:08

score 0 · Answer 6 · edited Oct 18 '13 at 05:20

0

why not use this where clause:

WHERE MEM_DOB BETWEEN GETDATE()-1 AND GETDATE()+30

edited Oct 18 '13 at 05:20

Radim Köhler

122,561
47
239
335

answered Oct 18 '13 at 04:59

elvis

16

Please don't use `BETWEEN` with date/time types, [especially in SQL Server](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common). – Clockwork-Muse Oct 21 '13 at 04:58

score -2 · Accepted Answer · edited May 23 '17 at 10:29

Try this... SELECT Mem_FirstNA, Mem_LastNA, Mem_DOB FROM MemberDetails WHERE ltrim(str(year(GETDATE()))) + '-' + ltrim(str(month(Mem_DOB))) + '-' + ltrim(str(day(Mem_DOB))) >= getdate() - 1 AND ltrim(str(year(GETDATE()))) + '-' + ltrim(str(month(Mem_DOB))) + '-' + ltrim(str(day(Mem_DOB))) <= getdate() + 30

EDIT: The comments for this answer have rightly pointed out that it will not work if the current year is a leap year. So this update. The list of dates can be more efficiently generated by using Get a list of dates between two dates using a function

Select Mem_FirstNA, Mem_LastNA, Mem_DOB from MemberDetails m, (
Select datepart(dd,getdate()) as d,  datepart(mm,getdate()) as m
union
Select datepart(dd,getdate() + 1) as d,  datepart(mm,getdate() + 1) as m
union
Select datepart(dd,getdate() + 2) as d,  datepart(mm,getdate() + 2) as m
union
Select datepart(dd,getdate() + 3) as d,  datepart(mm,getdate() + 3) as m
union
Select datepart(dd,getdate() + 4) as d,  datepart(mm,getdate() + 4) as m
union
Select datepart(dd,getdate() + 5) as d,  datepart(mm,getdate() + 5) as m
union
Select datepart(dd,getdate() + 6) as d,  datepart(mm,getdate() + 6) as m
union
Select datepart(dd,getdate() + 7) as d,  datepart(mm,getdate() + 7) as m
union
Select datepart(dd,getdate() + 8) as d,  datepart(mm,getdate() + 8) as m
union
Select datepart(dd,getdate() + 9) as d,  datepart(mm,getdate() + 9) as m
union
Select datepart(dd,getdate() + 10) as d,  datepart(mm,getdate() + 10) as m
union
Select datepart(dd,getdate() + 11) as d,  datepart(mm,getdate() + 11) as m
union
Select datepart(dd,getdate() + 12) as d,  datepart(mm,getdate() + 12) as m
union
Select datepart(dd,getdate() + 13) as d,  datepart(mm,getdate() + 13) as m
union
Select datepart(dd,getdate() + 14) as d,  datepart(mm,getdate() + 14) as m
union
Select datepart(dd,getdate() + 15) as d,  datepart(mm,getdate() + 15) as m
)X
where   
datepart(dd, m.Mem_DOB) = x.d and datepart(mm, m.Mem_DOB) = x.m

If you are downvoting, please comment why.

Great, but do consider the efficiency. I have not tested how efficient it is. — unlimit, Oct 18 '13 at 05:12
This is the **wrong** way to do this. If you find yourself converting dates to strings, you're doing something very wrong. It's the slowest way to do the conversion, and thanks to possible different database collations/operating system date formats, it's an way to introduce bugs into the process. — Joel Coehoorn, Oct 21 '13 at 01:30
@JoelCoehoorn - I know and that is why I have specifically mentioned in my comment that I have not tested its efficiency. But at that moment this is what came to my mind and as this answer is the accepted one I will update the answer with a efficient query. — unlimit, Oct 21 '13 at 07:21
Even ignoring efficiency and formatting for a moment, this technique fails when attempting to update a leap day birthday into a common (non-leap) year. — pilcrow, Oct 22 '13 at 02:23
@pilcrow - Updated the answer, comments will be appreciated. — unlimit, Oct 23 '13 at 06:44

How to get 30 days from now from SQL table

7 Answers7