CODEWITHSUNDEEP
mysqlsql
user1601082
user1601082

Reputation: 1

Birthday next 7 days from 2 columns

I need to query my database regarding birthdays in next 7 days - with birthday date in two columns

  Name  Day   Month   
  Peter 15    8       
  Susi  20    8      
  Thor  14    9

Query = who has birthday next 7 days Return 

Peter 15 - 8   
Susi  20 - 8

any help welcome - Regards Thorsten

Upvotes: 0

Views: 254

Answers (2)

Taryn
Taryn

Reputation: 247880

You did not specify RDBMS but this should return the results you want in SQL Server. These will check if the month in the table is less than the current month, if so then it will use the following year:

select *
from 
(
    select name,
        cast(case 
                when datepart(month, getdate()) > [month] 
                 and datepart(day, getdate()) > [day]
                then cast(datepart(year, dateadd(year, 1, getdate())) as varchar(4))
                else cast(datepart(year, getdate()) as varchar(4)) end + '-'
            + cast([month] as varchar(2)) + '-'
            + cast([day] as varchar(2)) as datetime) bd
    from t1
) x
where datediff(day, getdate(), bd) >= 0 
  and datediff(day, getdate(), bd) < 7

See SQL Fiddle with Demo

Or:

select *
from 
(
    select name,
        cast(case 
        when datepart(month, getdate()) > [month] 
            and datepart(day, getdate()) > [day]
        then cast(datepart(year, dateadd(year, 1, getdate())) as varchar(4))
        else cast(datepart(year, getdate()) as varchar(4)) end + '-'
    + cast([month] as varchar(2)) + '-'
    + cast([day] as varchar(2)) as datetime) bd
    from t1
) x
where bd >= DateAdd(day, DateDiff(day, 0, getdate()), 0)
    and bd < DateAdd(day, DateDiff(day, 0, getdate()), 7)

MySQL version:

SELECT *
FROM
(
  select name,
    str_to_date(concat(case 
      when month(current_date()) > month
           and day(current_date()) > day
      then year(date_add(current_date, interval 1 year))
      else year(current_date()) 
    end, '-', month, '-', day), '%Y-%m-%d') birthdate
  from t1
) x
where birthdate >= Date(current_date())
  and birthdate < Date(Date_Add(current_date(), interval 7 day))

See SQL Fiddle with Demo

Upvotes: 4

Greg
Greg

Reputation: 3522

Taking the second select statement from @bluefeet answer you can do something like this:

select * 
from  
( 
    select name,
        case when(
         cast(cast(datepart(year, getdate()) as varchar(4)) + '-' 
        + cast([bmonth] as varchar(2)) + '-' 
        + cast([bday] as varchar(2)) as datetime) > getdate())
        THEN
            cast(cast(datepart(year, getdate()) as varchar(4)) + '-' 
            + cast([bmonth] as varchar(2)) + '-' 
            + cast([bday] as varchar(2)) as datetime)

        ELSE

            cast(cast(datepart(year, getdate())+1 as varchar(4)) + '-' 
            + cast([bmonth] as varchar(2)) + '-' 
            + cast([bday] as varchar(2)) as datetime)
        END
        as bd 
    from @Names 
) x 
where bd >= DateAdd(day, DateDiff(day, 0, GETDATE()), 0) 
    and bd < DateAdd(day, DateDiff(day, 0, GETDATE()), 7)

Upvotes: 1

Related Questions