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.
| Author |
Topic |
|
lw1990
Yak Posting Veteran
85 Posts |
 Posted - 2007-07-12 : 14:09:53
|
| Hi, I need to create a stored procedure that pulls records from a table/view on every 15th and last day of each month and only pulls the records in the last half month. There is a field "startTime" in the table has been defined as "datetime" for the data type, which I can use to work on the date/time. But how to handle the different last date of the month and the length of the period is the key point.For example:02/28/2007, data from 02/16/2007 to 02/28/2007 with 13 days' records02/29/2008, data from 02/16/2008 to 02/29/2007 with 14 days' records (Leap Year)06/30/2007, data from 06/16/2007 to 06/30/2007 with 15 days' records07/15/2007, data from 07/01/2007 to 07/15/2007 with 15 day's records 07/31/2007, data from 07/16/2007 to 07/31/2007 with 16 days' records.I'm not just working on these sample date, so can't just hardcode on these days, I'll run this procedure every month and for 10+ years.Please help on how to handle this issue, Thanks. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-12 : 14:20:13
|
| [code] select dateadd(m, datediff(m, 0, dateadd(m, 1, @dateValue)), -1) [/code]This will give you last day of the month.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-07-12 : 14:42:54
|
This gives you the 1st day of the month:select dateadd(mm,datediff(mm,0,getdate()),0) This gives you the 15th day of the month:select dateadd(mm,datediff(mm,0,getdate()),14) This gives you the last day of the month:select dateadd(mm,datediff(mm,-1,getdate()),-1) CODO ERGO SUM |
|
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2007-07-12 : 17:28:29
|
| Thank you Dinakar and Michael,It is important to use the 1st day, 15th day and last day of the month. I'll use them in my stored procedures:select ---from 'table'where startTime between '1st day' and '15th day'Andselect ---from 'table'where startTime between '15th day' and 'last day'And I'll schedule a Job at the 15th day of the month to run the first stored procedure, and schedule another Job to run the second one. So each month, I'll get 2 export files. Now, I believe the first Job should have no problem, the problem is if set up a schedule at 31th Day of each month in the Job schedule setting, is this Job going to run at 06/30/2007, 09/30/2007, and 02/29/2008...? Did anybody set in this way and got running well?Please tell me the experiences you have.Thanks. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-12 : 17:35:20
|
| That is why you use the "last day" calculation. Assuming,the job will run every day but will only do the actual processing when the day is 1st or 15th. So you might as well put this condition to check if its the last day and let it process if so.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-07-12 : 18:05:57
|
| Why don't you run the jobs on the 16th day of the month for days 1 through 15 and run on the 1st day of the next month for days 16 through end of month so that all the data for the 15th and last days of the month is available?CODO ERGO SUM |
|
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2007-07-13 : 17:35:09
|
| Thank you Dinakar and Michael,It works, I made 2 views, 2 DTSs and 2 Jobs for the 1st day to 15th day and 16th day to last day of the month. And the Jobs scheduled on 1st and 16th of the month.Thank you for your helps. |
|
|
|
|
|
|
|
|
How to work around the date