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 2008 Forums
 Transact-SQL (2008)
 how to increment date automatically every 2nd FRI

Author  Topic 

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-10-01 : 15:31:36
I want to display 2 dates on the webpage like

Start Date:

End Date:

I want to save a static date in the database, my table is like this:

Tbl_Date

id int

start_d datetime

end_d datetime

Now once I manually save a date in these columns (start and end), I want 14 days to be added to both start and end date on every 2nd Friday at 11:30am according to Eastern Standard Time and the new dates values should over-write the original start and end date.

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-10-01 : 15:33:26
i know how to create a schedule task. I am thinking to just display START & END date on a regular asp.net c# page and my schedule task will update the dates in the database automatically. Is my approach correct ?

Talking about schedule tasks, my website is on a hosting server so I can only access the SQL database and if I know correctly the sql jobs/tasks are saved in the sql server (the machine on which it is installed). But since my website is hosted with asp hosting provider I can't access their sql server. I can create a SP in the database and how I will update the dates automatically ?

In my hosting provider control panel, I saw schedule task option and within that schedule task I can do only the following things:


So like I mentioned above, I need help with the SQL statement / Store procedule and in my case, how to make is a schedule task ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 16:12:51
you can write a stored procedure with logic like

UPDATE Tbl_Date
SET Start_d = DATEADD(dd,14,Start_d),
End_d = DATEADD(dd,14,End_d)
WHERE...


you need to put an appropriate condition in WHERE to identify exact row to be updated

Wrap this update in your procedure and call it from SQL agent job to be executed automatically based on a schedule

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-10-01 : 16:25:04
Yes but how stored procedure will run periodically ? because I mentioned above that my website is on a hosting server so I can only access the SQL database NOT SQL SERVER (I can only connect to hosting server and open my database on my pc sql server express) and if I know correctly the sql jobs/tasks are saved in the sql server (the machine on which it is installed). In order to job sql job for my database, a sql job needs to be created on hosting server which is not possible in my case because database is hosting outside. Since my website is hosted with asp hosting provider I can't access their sql server.

Is there any way to solve this problem ?
Go to Top of Page

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-01 : 16:29:50
Do you have access to any server with network access to the SQL server? This might be anywhere on the internet, but you'll want the system to have relatively high reliability / availability. If so, you can create a windows task that runs SQLCMD.EXE to run the SP when you want. I think there are also open source tools to run SQL statements remotely if you have a Linux variant available.

Otherwise, I'd be interested in knowing more about your business requirements as this sounds like A) an odd requirement and B) something that could be solved another way (dynamically).
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-10-01 : 16:38:03
Its not an odd requirement because my webpage is actually online timesheet, so payroll period needs to be changes every 2nd week when payroll is run bi-weekly that's why I want to add 14 days automatically in the database so that I can display those database values on a web page. And if I can get a solution to update the dates automatically, then webpages will always show the updated dates.

My start and End date represent payroll start and payroll end date for filling the timesheet.

Since I have mentioned my situation that database and website is hosted outside with a hosting company like Godaddy, I don't think my situation or requirements are odd because majority of websites online are hosting with hosting company rather than hosted in-house. So along with the website, even database lies on the hosting server.

And obviously, on a shared hosting, a user don't have control over the hosting company's sql server.

any advise ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 16:44:52
quote:
Originally posted by asp__developer

Its not an odd requirement because my webpage is actually online timesheet, so payroll period needs to be changes every 2nd week when payroll is run bi-weekly that's why I want to add 14 days automatically in the database so that I can display those database values on a web page. And if I can get a solution to update the dates automatically, then webpages will always show the updated dates.

My start and End date represent payroll start and payroll end date for filling the timesheet.

Since I have mentioned my situation that database and website is hosted outside with a hosting company like Godaddy, I don't think my situation or requirements are odd because majority of websites online are hosting with hosting company rather than hosted in-house. So along with the website, even database lies on the hosting server.

And obviously, on a shared hosting, a user don't have control over the hosting company's sql server.

any advise ?


do you've remote sql server you've access to?
if yes will you be able to createa a linked server to your hosted database from this? or may be use ssis package to connect to that database and execute this procedure?

then you could schedule you package to be executed according to the schedule?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-10-01 : 16:50:29
quote:
[i]may be use ssis package to connect to that database and execute this procedure?

------------------------------------------------------------------------------------------------------



I want the dates to be updated automatically, so connecting manually to my database and updating the values is not a solution I am looking for.

I do have SQL Server installed on my PC, is it possible that I can create a sql job on my PC SQL Server and the sql job connected to my database on hosted server periodically and updates the dates on the database every 14 days ? If yes, this would be great and a solution to my problem.

If this is the solution, I would deeply appreciate if you could guide me with the steps and SQL command to update these 2 dates on the hosting server database ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 16:55:37
quote:
Originally posted by asp__developer

quote:
[i]may be use ssis package to connect to that database and execute this procedure?

------------------------------------------------------------------------------------------------------



I want the dates to be updated automatically, so connecting manually to my database and updating the values is not a solution I am looking for.

I do have SQL Server installed on my PC, is it possible that I can create a sql job on my PC SQL Server and the sql job connected to my database on hosted server periodically and updates the dates on the database every 14 days ? If yes, this would be great and a solution to my problem.

If this is the solution, I would deeply appreciate if you could guide me with the steps and SQL command to update these 2 dates on the hosting server database ?


i didnt tell you to do it manually. the package will be created and once linked to job, it will get executed periodically and connect and does required modification in your hosted db if you've configured the connection manager in ssis.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-01 : 17:08:46
Can you just do this in code dynamically? Given a set of "seed" dates, you can calculate the current Pay Period using any provided date or just use current date & time (GETDATE()).


DECLARE @PayPeriod TABLE(id INT PRIMARY KEY,BeginDate DATETIME,EndDate DATETIME);
INSERT @PayPeriod VALUES(1,'1/7/1900','1/20/1900');

DECLARE @DateToDeterminePayPeriod DATETIME;
SET @DateToDeterminePayPeriod = '10/1/2012' -- Example Date

SELECT DATEDIFF(d,p.BeginDate,@DateToDeterminePayPeriod) / 14 AS PayPeriodNumber,
DATEADD(d,DATEDIFF(d,p.BeginDate,@DateToDeterminePayPeriod) / 14 * 14, p.BeginDate) AS CurrentPayPeriodBeginDate,
DATEADD(d,DATEDIFF(d,p.BeginDate,@DateToDeterminePayPeriod) / 14 * 14, p.EndDate) AS CurrentPayPeriodBeginDate
FROM @PayPeriod p

/* -- When 10/1/2012 is provided
PayPeriodNumber CurrentPayPeriodBeginDate CurrentPayPeriodBeginDate
2941 2012-09-30 00:00:00.000 2012-10-13 00:00:00.000
*/

/* -- When 10/13/2012 is provided
PayPeriodNumber CurrentPayPeriodBeginDate CurrentPayPeriodBeginDate
2941 2012-09-30 00:00:00.000 2012-10-13 00:00:00.000
*/

/* -- When 10/14/2012 is provided
PayPeriodNumber CurrentPayPeriodBeginDate CurrentPayPeriodBeginDate
2942 2012-10-14 00:00:00.000 2012-10-27 00:00:00.000
*/
Go to Top of Page

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-01 : 17:20:16
quote:
Originally posted by asp__developer

Its not an odd requirement because my webpage is actually online timesheet, so payroll period needs to be changes every 2nd week when payroll is run bi-weekly that's why I want to add 14 days automatically in the database so that I can display those database values on a web page. And if I can get a solution to update the dates automatically, then webpages will always show the updated dates.

My start and End date represent payroll start and payroll end date for filling the timesheet.

Since I have mentioned my situation that database and website is hosted outside with a hosting company like Godaddy, I don't think my situation or requirements are odd because majority of websites online are hosting with hosting company rather than hosted in-house. So along with the website, even database lies on the hosting server.

And obviously, on a shared hosting, a user don't have control over the hosting company's sql server.

any advise ?



It's an odd requirement because in traditional relational design, an entity that embodied PayPeriod would more likely have new entries added vs continually updating one set every two weeks. If you wanted a value that reflects current date/time, you could just calculate from a seed.

If setting up a SQL Agent job on your desktop to update the columns on your host intrigues you, I invite you to reread my entry concerning SQLCMD. It would be cleaner to simply run a windows task from your desktop that calls SQLCMD. You simply specify your connection settings and the command to run and use Windows Scheduler to run it on the schedule you want. As far as the command, create the SP that visakh16 recommended on the host and specify it as your query string.
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-10-02 : 09:13:09
quote:

It's an odd requirement because in traditional relational design, an entity that embodied PayPeriod would more likely have new entries added vs continually updating one set every two weeks. If you wanted a value that reflects current date/time, you could just calculate from a seed.

If setting up a SQL Agent job on your desktop to update the columns on your host intrigues you, I invite you to reread my entry concerning SQLCMD. It would be cleaner to simply run a windows task from your desktop that calls SQLCMD. You simply specify your connection settings and the command to run and use Windows Scheduler to run it on the schedule you want. As far as the command, create the SP that visakh16 recommended on the host and specify it as your query string.




1) lazerath, Now I do understand what you are saying and I see your point that scheduling my windows to run the command periodically is easy - Could you please guide me with the steps since I do not have much experience with this ? I would deeply appreciate it...

2) Also I am also thinking about considering the other options that I have a table in which I store all the pay periods From and To (like for the whole year) and display values from that tables. In that case, How I will display the FROM and TO and change it every 2nd friday ? I mean I can display the results FROM and TO in webpage coding by a SP or something but it is possible to have a SP that gives the output FROM and TO and changes to next row every 2nd friday 11am (EST) ? By this I will just have to code and use the SP and SP will do everything and will give next row values of FROM and TO every 2nd friday 11am and I want those values to stay until next 2nd friday 11am EST... Is it possible ? or You think I am making it too complex ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-02 : 10:38:47
quote:
Originally posted by asp__developer

quote:

It's an odd requirement because in traditional relational design, an entity that embodied PayPeriod would more likely have new entries added vs continually updating one set every two weeks. If you wanted a value that reflects current date/time, you could just calculate from a seed.

If setting up a SQL Agent job on your desktop to update the columns on your host intrigues you, I invite you to reread my entry concerning SQLCMD. It would be cleaner to simply run a windows task from your desktop that calls SQLCMD. You simply specify your connection settings and the command to run and use Windows Scheduler to run it on the schedule you want. As far as the command, create the SP that visakh16 recommended on the host and specify it as your query string.




1) lazerath, Now I do understand what you are saying and I see your point that scheduling my windows to run the command periodically is easy - Could you please guide me with the steps since I do not have much experience with this ? I would deeply appreciate it...

2) Also I am also thinking about considering the other options that I have a table in which I store all the pay periods From and To (like for the whole year) and display values from that tables. In that case, How I will display the FROM and TO and change it every 2nd friday ? I mean I can display the results FROM and TO in webpage coding by a SP or something but it is possible to have a SP that gives the output FROM and TO and changes to next row every 2nd friday 11am (EST) ? By this I will just have to code and use the SP and SP will do everything and will give next row values of FROM and TO every 2nd friday 11am and I want those values to stay until next 2nd friday 11am EST... Is it possible ? or You think I am making it too complex ?



You can have the sp give FROM and TO date values through OUTPUT parameters.
see section OUTPUT variables in below link

http://www.sqlteam.com/article/stored-procedures-returning-data

you can make it change every 2nd Friday. what should be values you need to assign for FROM and TO date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-02 : 10:57:24
How to schedule a task:
[url]http://windows.microsoft.com/en-US/windows7/schedule-a-task[/url]

SQLCMD.EXE
[url]http://msdn.microsoft.com/en-us/library/ms170207(v=sql.105).aspx[/url]

Regarding how to change the pay period on Friday @ 11 AM, just add 37 hours to the datetime when filtering the payperiod:

WHERE DATEADD(hh,37,GETDATE()) BETWEEN PayPeriodBeginDate AND PayPeriodEndDate
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-10-02 : 12:39:32
I have an idea, how about if I create a web service and upload it on my hosting server.

Then I create a sql scheduled job on my PC and set it to call the web-service every 2nd friday at 11pm ?

In my webservice I will execute the update SQL command to update the From and To values and hence, the values of FROM and TO will be updated every 2nd friday ?

Any thoughts ? I thing I am doing the same logic but following different approach, right ?

Question 1) Can you please help me with the SP that I can define in my web-services that will update the FROM and TO values by adding 14 days to each date ?

Question 2) Can you help me with a SP or SQL command that I can define in my SQL scheduled job that can call a web service ?
Go to Top of Page

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-02 : 15:44:51
My 0.02 is that webservice + sql agent job + service broker (to call said webservice) is unnecessarily complex. What is the resistance to windows scheduler + SQLCMD? Alternately, why not prepopulate the table and use the dynamic method to select the period? If it were me, I'd go with the latter, as it involves the least moving parts (not as prone to failure) and the pay periods can be associated with your timesheet entries / other entities for a higher degree of relational integrity.
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-10-03 : 06:49:07
I understood the windows scheduler, could you please help me with setting up the SQLCMD ? I know how to open it but what next ? I would deeply appreciate if you could help me in this ?
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-10-03 : 13:38:37
Hello lazerath,

I know we are talking on other forum too in which I proposed an easy logic but I also want to learn this too - Can you please help me and show me or guide me ?
Go to Top of Page

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-03 : 14:00:37
I provided you a link that explains how to use SQLCMD and how it works. It's just a command line interface to SQL. You will simply need to read that link and test some things out on your local instance. I'm sure you'll get it within 15 minutes as the syntax for what you need to do is pretty easy. It doesn't make sense for me to type out what Microsoft and others on google have explained far easier.
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 2012-10-03 : 14:09:45
1) That I understood but in my situation I am looking to connect to a server outside the network like godaddy hosting server where my database sits ?

2)Another question is, how I will save a command in this because I am assuming that this needs to be saved in order for windows scheduler to run this command periodically ?
Go to Top of Page

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-03 : 14:39:58
1.) Do you have the ability to connect to your hosted SQL Server from your desktop using SSMS or visual studio? If so, whatever connection method you use there (server name, authentication type, credentials) are what you use with SQLCMD.

2.) You can either put the full command directly into the windows task as the command to run OR just save it as a .BAT file and run the .BAT from the Windows Task.
Go to Top of Page
    Next Page

- Advertisement -