CODEWITHSUNDEEP
c#sqlsql-serverdatabase
user3613744
user3613744

Reputation: 75

Automatically change value in sql database based on a given date

Is it possible to change value in sql automatically. For an example it´s a rent-a-car database and a customer wants to make a reservation for a car for, let´s say, 15th of july (now it's 4th). when i run my C# program and click on a button to reserve that car it writes in the database in the RentalDate Column July 15th(future date).Now in the Availability Column (is the car available) it should stay True until that date (15th) comes and then should be automatically changed to False. Is it possible to do something like that?

Upvotes: 0

Views: 4941

Answers (2)

Tanner
Tanner

Reputation: 22733

You can create a SQL Agent Job that executes a stored procedure to perform the update on a given schedule, i.e. every day at midnight.

Stored procedure code would contain something like this to compare the RentalDate with today's date to perform the update:

UPDATE RentalsTable
SET Availability = 0 -- False as a bit
WHERE RentalDate = CAST(GETDATE() as DATE) -- takes date portion of GETDATE()

Sample script to create a job to run a stored procedure would look something like this:

--Add a job
EXEC dbo.sp_add_job
    @job_name = N'MY UPDATE JOB';

--Add a job step named process step. This step runs the stored procedure
EXEC sp_add_jobstep
    @job_name = N'MY UPDATE JOB',
    @step_name = N'process step',
    @subsystem = N'TSQL',
    @command = N'EXEC [YOUR_STORED_PROC_TO_PERFORM_UPDATE]'

--Schedule the job at a specified date and time
exec sp_add_jobschedule @job_name = N'MY UPDATE JOB',
@name = 'MySchedule',
@freq_type=1,
@active_start_date = '2014-07-03',
@active_start_time = '00:00:00'

-- Add the job to the SQL Server Server
EXEC dbo.sp_add_jobserver
    @job_name =  N'MY UPDATE JOB',
    @server_name = N'[SERVER NAME]'

Alternately, you can use Management Studio and a wizard to create scheduled jobs.

The above script was adapted from the following site, which also has a step by step guide to setting up job via management studio.

Further Reading: How to: Create a SQL Server Agent Job (Transact-SQL)

Upvotes: 1

TGH
TGH

Reputation: 39248

I would consider a different design where you keep a booking table and a car table. Then if you reserve a car you can write a row to the booking table with the time span. You can then check against this table to see if the car is available for an arbitrary requested time span.

Upvotes: 3

Related Questions