Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 having birthdays in next two months

Author  Topic 

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-11 : 04:58:28
DDL and solution.
Is there a better solution? :)

--CREATE TABLE
create table birthday
(customer_id int
,birthday smalldatetime)

--POPULATE TABLE
insert into birthday
select 12351,'1980-09-20 00:00:00'
union all select 12353,'1989-01-02 00:00:00'
union all select 12350,'1976-10-02 00:00:00'
union all select 12354,'1951-11-16 00:00:00'
union all select 12355,'1952-03-18 00:00:00'
union all select 12356,'1952-03-18 00:00:00'
union all select 12357,'1974-11-05 00:00:00'
union all select 12358,'1976-03-20 00:00:00'
union all select 12359,'1988-08-06 00:00:00'
union all select 12361,'1989-05-04 00:00:00'
union all select 12362,'1943-12-03 00:00:00'
union all select 12363,'1970-07-03 00:00:00'
union all select 12364,'1953-01-11 00:00:00'
--(13 row(s) affected)


--GET customers which have in next 2 months birthdays
select customer_id
from
birthday
where
right(convert(nvarchar(20), birthday, 102),5) between
right(convert(nvarchar(20), getdate(), 102),5)
and right(convert(nvarchar(20),dateadd(day, datediff(day, -60, (getdate())),0), 102),5)

rupalim
Starting Member

20 Posts

Posted - 2010-08-11 : 05:14:39
Hi Slimt,

is there any code available for calling SSIS Package in a stored procedure ?
Go to Top of Page

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-08-11 : 05:15:05
Try this


Select * ,dateadd(yy,(year(getdate())-year(birthday)),birthday)
from birthday
where dateadd(yy,(year(getdate())-year(birthday)),birthday) between getdate() and dateadd(m,2,getdate())

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/
Go to Top of Page

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-11 : 05:36:18
senthil_nagore -> nice one :) thank you
Go to Top of Page

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-11 : 05:48:12
rupalim -> yes it can be done.
one way - that pops into my mind and has been tested - is to do the following:

0. build your SSIS package/solution
1. create a job that will run your SSIS package. no need to set the schedule or anything else.
2. create a procedure that will start this particular job with SSIS package; something like:

create procedure run_job
as
declare @sql nvarchar(max)
set @sql = ''
set @sql = 'exec msdb.dbo.sp_start_job N''JOB_TEST'''
exec (@Sql)

Go to Top of Page
   

- Advertisement -