How automatically add 1 year date to an existing d

问题:

I have a task to automatically bill all registered patients in PatientsInfo table an Annual Bill of N2,500 base on the DateCreated column.

Certainly I will use a stored procedure to insert these records into the PatientDebit table and create a SQL Job to perform this procedure.

How will I select * patients in PatientsInfo table where DateCreated is now 1 yr old for me to insert into another table PatientDebit.

I have my algorithm like this:

1. select date of registration for patients from PatientsInfo table
2. Add 1 year to their DateCreated
3. Is date added today? if yes,
4. Insert record into PatientDebit table with the bill of N2,500
5. If no, do nothing.

Please how do I write the script?

回答1:

Use DATEADD, i.e.:

SELECT DATEADD(year, 1, '2006-08-30')

Ref.: http://msdn.microsoft.com/en-us/library/ms186819.aspx

回答2:

Assuming the columns of the 2 tables are the same:

INSERT INTO PatientDebit
SELECT * from PatientsInfo WHERE DateCreated<DATEADD(year, -1, GETDATE())

Make sure you have an index on DateCreated if PatientsInfo has a lot of records as it could potentially be slow otherwise

回答3:

there should be .add or addyear() function in sql. You add like .add(year, day, month). Read upon sql datetime add year, month and seconds. It is pretty straightforward. It is just like c#.

Dateadd(info). now time is. getdate().