SQL Server 2012 stored procedure Issue URGENT

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

SQL Server 2012 Forums

Transact-SQL (2012)

SQL Server 2012 stored procedure Issue URGENT

Author

Topic

Seuss
Starting Member

5 Posts

Posted - 2014-05-27 : 17:17:37

Hi all,

I am in desperate need of help please.

I have created a stored procedure with a while loop using a hard coded date 31-12-2014 to compare against current date produced in a while loop. I need to make the hard coded date reflect every two years i.e. 31-12-2016, 31-12-2018 etc.
How can the date be automatic instead of hard coded please?
i.e While (currdate <= '31-12-2014')

Second issue, how do I make the stored procedure to run every two years as well?

SQL Agent Job doesn't have a yearly frequency at all.

Can someone please offer an example coding and suggestions to those two issues which I will be grateful please.

Thank You!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-27 : 17:24:32

So up until 2014-12-31 you want to get a value of 2014-12-31. on 2015-01-01 you want to get 2016-12-31, etc.. correct?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-05-28 : 01:47:55

Given the limitation of scheduling the jobs only yearly, I'd recommend you set the sql agent job and use another scheduler to execute the code - such as an Enterprise scheduler.
Many things can change in one year, it may be prudent to have approval from multiple parties , before running the job after 2 years

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Seuss
Starting Member

5 Posts

Posted - 2014-05-28 : 04:49:11

Hi Lamprey

Not exactly.

At the moment, when our calendar reaches 31-12-2014

I need this to be like this:

i.e. While (currdate <= '31-12-2016)
Do the work

Then when it finished the while loop of adding all the entries with dates upto 31-12-2016.

Next time, when our calendar reaches 31-12-2016, it now needs to be

i.e. While (currdate <= '31-12-2018)
Do the work

But we need to remove the hardcoded date and somehow have it automatic with some TSQL in it - which is something I am not sure. I have done this coding as below but don't know if there is a better approach?

DECLARE @LastDayOfYear Date, @GetHour INT , @NextNewDate DateTime

SET @LastDayOfYear = DATEADD(yy, DATEDIFF(yy,0,GETDATE()) + 1, -1)

SET @GetHour = datepart(hour, GETDATE()) -- returns hour

--Get Today date to compare to the last day of December in current year
IF (FORMAT(GETDATE(), 'yyyy-MM-dd') = FORMAT(@LastDayOfYear, 'yyyy-MM-dd')) AND @GetHour >= 20
BEGIN
--If Equal - then increase the next 2 years
SET @NextNewDate = FORMAT(DATEADD(YEAR, 2, @LastDayOfYear), 'yyyy-MM-dd HH:mm:ss')
END
ELSE
SET @NextNewDate = FORMAT(@LastDayOfYear, 'yyyy-MM-dd HH:mm:ss')

PRINT @NextNewDate

WHILE (CONVERT(DATE,@currDate, 101) <= @NextNewDate)
BEGIN

Any examples will be most helpful.....

Many Thanks

Seuss
Starting Member

5 Posts

Posted - 2014-05-28 : 05:02:46

Hi Jackey

I have also been googling and there were some examples where you could implement a job step code to do it? Not sure if this is feasible or a good idea?

I haven't found an answer or example to do this....

Many thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-28 : 12:05:09

Here is a sample of one way to get the date, I think you want.

DECLARE @Foo TABLE (Val DATE)

INSERT @Foo VALUES
('2013-02-04')
,('2014-01-01')
,('2014-02-04')
,('2014-12-31')
,('2015-01-01')
,('2015-02-04')
,('2015-12-31')
,('2016-01-01')
,('2016-02-04')
,('2016-12-31')


SELECT
	Val,
	DATEADD(YEAR, + (YEAR(Val) % 2), DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0,Val) + 1, 0)))
FROM
	@Foo

EDIT: Cut-n-paste error.

Qualis
Posting Yak Master

145 Posts

Posted - 2014-05-28 : 13:53:16

Here is what I came up with:


------------------------
-- Generate Test Data --
------------------------
Declare @TestDates Table (ID int identity(1,1), TestDate date)
Declare @YearCnt int
Set @YearCnt = 2014
While @YearCnt <= 2030 Begin
	Insert Into @TestDates Select '1-1-' + Cast(@YearCnt as varchar(4))
	Insert Into @TestDates Select '6-15-' + Cast(@YearCnt as varchar(4)) 
	Insert Into @TestDates Select '12-30-' + Cast(@YearCnt as varchar(4)) 
	Insert Into @TestDates Select '12-31-' + Cast(@YearCnt as varchar(4))
	Set @YearCnt = @YearCnt + 1
End

-----------------------------------
-- Set up StartDate and Interval --
-----------------------------------
Declare @StartDate date
Declare @YearInterval decimal(9,2)
Set @StartDate = '12-31-2014'
Set @YearInterval = 2

-------------------------------------------------------------
-- View Results (use the DateAdd statement for your needs) --
-------------------------------------------------------------
Select @StartDate, @YearInterval, TestDate, 
DateAdd(yyyy, 
	Case 
		When Right(TestDate, 5) < Right(@StartDate, 5) Then 
			Ceiling((Datepart(yyyy, TestDate) - Datepart(yyyy, @StartDate)) / @YearInterval)
		Else 
			Floor((Datepart(yyyy, TestDate) - Datepart(yyyy, @StartDate)) / @YearInterval) + 1
	End * @YearInterval,
	@StartDate)
From @TestDates
Order by ID

Seuss
Starting Member

5 Posts

Posted - 2014-05-28 : 15:44:11

Thanks so much Qualis - I will definitely try this out tomorrow for the inside stored procedure.

I have a separate issue which I now trying to get SQL Agent job to run the stored procedure every 2 years i.e. 31-12-2014, 31-12-2016, 31-12-2018 etc and to run at 8pm.

So as a test I did this, I have created a step 1 in the SQL Agent Job:

DECLARE @LastDayOfYear Date, @GetHour INT , @NextNewDate DateTime

SET @LastDayOfYear = DATEADD(yy, DATEDIFF(yy,0,GETDATE()) + 1, -1)

SET @GetHour = datepart(hour, GETDATE()) -- returns hour

--Get Today date to compare to the last day of December in current year
IF (FORMAT(GETDATE(), 'yyyy-MM-dd') = FORMAT('2014-05-28', 'yyyy-MM-dd')) AND (@GetHour >=15)
BEGIN
SELECT 2
END
ELSE
EXEC msdb.dbo.sp_stop_job @job_name='Testjob'

Then created step 2 to call the stored procedure I have created

I then created a scheduler to test it out with today date starting from 11am and every 5 mins with recurring mode.

For some reason, it nevers call the stored proc which should have from step 1 to step 2

Any ideas please?

Many thanks

Qualis
Posting Yak Master

145 Posts

Posted - 2014-05-28 : 16:12:40

Just set up the job to run every night at 8pm and use something like this:


Declare @TestDate date
--Set @TestDate = '12-30-2016'
Set @TestDate = GetDate()

Declare @StartDate date
Declare @YearInterval decimal(9,2)
Set @StartDate = '12-31-2014'
Set @YearInterval = 2

If Right(@TestDate, 5) = Right(@StartDate, 5) And
	(DatePart(yyyy, @TestDate) - DatePart(yyyy, @StartDate)) % @YearInterval = 0 Begin
	Insert Into ExecutionLog Select 'Executing', @TestDate
	Select 'Execute Code'
End
Else Begin
	Insert Into ExecutionLog Select 'Running, but not executing', @TestDate
	Select 'Do Not Execute Code'
End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources