|
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!
|
|
|
|
|
Use mod on the year component to determine even years, check that the year component is the same in curredate.
or
Create table to hold the event dates
A 2 year event cycle is very ambitious, a lot can happen in that cycle.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Could you kindly explain please, I am not sure about the mod with year component? I am rather saying I don't quite understand please.
I still don't know how to get the second issue to call the stored procs every two years as SQL Agent Job doesn't seem to offer the solution.
|
|
|
|
|
A simple search for TSQL Mod would have given you the information. However this may help, this year return 0, next year 1.
SELECT DATEPART(YEAR,GETDATE()) % 2
SELECT DATEPART(YEAR,DATEADD(YEAR,1,GETDATE())) % 2
As for the job not offering year - you are building a test method so a proc will run under only defined circumstances, so run the job every month, if it does not meet your criteria then let it wait for next month.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Does this look efficient and OK?
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
Second Issue - "so run the job every month, if it does not meet your criteria then let it wait for next month."
How do you make it meet your criteria i.e. if it falls on 31st December of each year?
modified 28-May-14 4:49am.
|
|
|
|
|
If you want to excecute your query on every 31st December add a below condition
IF (MONTH(GETDATE()) = 12 AND DAY(GETDATE()) = 31)
BEGIN
END
|
|
|
|
|
Hi Again
"so run the job every month, if it does not meet your criteria then let it wait for next month."
Would it mean I have to somehow implement a code within the Job Step List in the SQL Agent Job?
Many Thanks
|
|
|
|
|
Seuss wrote: implement a code within the Job Step List in the SQL Agent Job
No your job is calling a procedure. In the procedure I would first check that the current date matches the next process date and if so has the process been run for this date.
If it fails either test then exit without processing.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ah I think I understand what you are saying, have the SQL Job Agent set up and each month - when it calls the stored procedure - if the dates don't match up - it needs to exit out of the SQL Job Agent?
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?
Huge thanks to you for your patience. I know I am a bit persistent on this matter 
|
|
|
|
|
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?
|
|
|
|
|
You could set the job to recur every 730 days, or 104 weeks, though this wouldn't take in to account leap years or the fact that there are 52 weeks + 1 or 2 days in each year
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Really tricky - you are right about the leap year as well. Still googing to find out how a job can be done every 2 years. Whether implementating a job step with some code to check the dates?
I can't find an example on google yet.
|
|
|
|
|
Hi,
I have mysql database and employee details table is there with the column joining date.
i need to write a query in mysql in such a way that i should compare joining date with today date since i need to display employee details who are going to complete exactly 1 yr, 2 yr , 3yr ... and so on.
For ex:
Joining_Date Current_Date Years_Completed
2010-05-26 2014-05-26 4 years
2000-05-26 2014-05-26 14 years
2010-04-26 2014-05-26 4 years this row should not come since one month is less
only for todays date they should exactly complete years
How to achieve this. If anybody knows, please reply me.
Thanks in advance.
|
|
|
|
|
What happens with the following joining dates if todays date is 2014-05-26
1. 2012-05-25
2. 2012-05-26
3. 2012-05-27
4. 2013-05-27
|
|
|
|
|
if todays date is 2014-05-26 then
1. 2012-05-25 if u consider 2 years means 730 (365 X 2) days will come but in this 731 that 2 years 1 day so we should display this, it should complete exactly year
2. 2012-05-26 i think this is coming 730 days means 2 years exactly we can display
3. 2012-05-27 in this 1 day required to complete exactly 2 years now it is 729 days
4. 2013-05-27 in this 364 is coming it should be 365 to complete a year so we should display this record
How to achieve this. Please reply me.
|
|
|
|
|
Don't know MySQL but you should be able to break the datetime object into day/month/year/time components. Select from the table where the day = day and the month = month, ignoring the year and time components.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You have to use date formatting in year
|
|
|
|
|
Hi,
Try below :
DECLARE @JoiningDate DATE
SET @JoiningDate = '10/10/2013'
IF (YEAR(GETDATE()) > YEAR(@JoiningDate))
BEGIN
IF (DAY(@JoiningDate) = DAY(GETDATE()) AND DAY(@JoiningDate) = DAY(GETDATE()))
BEGIN
PRINT CONVERT(NVARCHAR,(YEAR(GETDATE()) - YEAR(@JoiningDate))) + ' Year(s)'
END
END
|
|
|
|
|
Hi gurus!
I have created a report in Access2010 and seems like the data alignment in report view is not what I want. My current view is like the pix in the following URL. Is it possible to have those tasks shown just below the headers (months)?
I tried to merge those cells for display or even those criteria like Does Not Equal blank but both are not what I want. Or should I better modify my query?
my current tabular view
There are two tables for such layout.
tables involved:
Tasks: TaskTitle, ProjectID, StartDate, DueDate
Projects: ProjectName, ProjectID
key: ProjectID (number)
For the query I am using, it is a bit "weird" and I wonder that is the main reason I can't get those tasks aligned to top. However, some of the tasks may have StartDate and/or DueDate empty. A big thank you in advance for your help since I seldom work with MS Access. Thanks in advance!!!
TRANSFORM [Tasks].[TaskTitle] AS [the value]
SELECT [Projects].[ProjectName]
FROM Projects LEFT JOIN Tasks ON [Projects].[ID] =[Tasks].[ProjectID]
GROUP BY [Projects].[ProjectName], [Tasks].[TaskTitle]
ORDER BY [Projects].[ProjectName]
PIVOT Format ([Tasks].[StartDate], "MMM-YYYY");
|
|
|
|
|
Dear members,
I have recently installed SQL Server 2008 Enterprise Version. I have a database built in SQL SERVER 2000 which I attached in 2008.
I have a stored procedure which calculates the Inventory Stock.
In some cases the sp deducts qty twice when call from front end (VB 6.0) whereas when I run the sp in SQL Server it returns correct result
It was running perfectly in SQL Server 2000 from VB 6.0
What could be the cause of this abnormal behavior?
Note: The Compatibility Level of the database is set on SQL Server 2000 (80)
Waiting for your kind replies.
Thanks & Regards,
Syed Adnan Shah
|
|
|
|
|
There is a bug in your code, but no one here can guess what your code is doing.
|
|
|
|
|
Dear Richard MacCutchan
I have written in my post that it is running perfectly through code (VB 6) using SQL Server 2000.
The problem occurred when i attached 2000 database in 2008 Enterprise.
|
|
|
|
|
You can write anything you like, it proves nothing. And, as I said before, no one here has the remotest chance of guessing what your code is doing.
|
|
|
|
|
Turn on Profiler and log what is happening when you call the stored procedure directly versus when you call it from VB 6.
|
|
|
|
|
Thanks for your reply
When I call The Stored Procedure directly from Sql Server it adds or
subtracts stock qty properly, but when i run it through vb6 code it adds or
subtracts qty twice. But its not in every execution of SP.
I m running my software for 3 to 4 years it never happened like this in SQL
Server 2000
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
