Showing posts with label supposed. Show all posts
Showing posts with label supposed. Show all posts

Saturday, February 25, 2012

Dates problem in SQL Server Evrywhere edition.

I have created a sample Database for the school project,

After executing the query below, the Date column is supposed to have the dates I have entered before,

However the dates shown are 1900.

Any idea why is this happening?

I appreciate your help.

Thank you.

Query:

Drop table AccountReceivable

GO

--BEGIN TRANSACTION

Create table AccountReceivable

(

AccountRecID int identity (1,1) not null,

PatientID int not null,

PresentCharges int default 0 not null,

PaymentMade money default 0 not null,

PreviousBalance money default 0 not null,

BalanceDue money default 0 not null,

LastPaymentDate datetime not null,

PresentDate datetime default GetDate() not null

)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

PK_AccountRecID Primary Key (AccountRecID)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)

GO

--COMMIT

--query to find delinquent accounts

--DATEDIFF (d, LastPaymentDate, PresentDate)

--Populate the Accounts Table

DELETE AccountReceivable

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )

VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)

GO

--sample query

select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable

GO

--result

PatientID PresentCharges LastPaymentDate PresentDate

-- -- -- --

913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

(24 row(s) affected)

Replied in another thread (same question)|||Replied in another thread. (for same question)

Posted by inh at 11:20 PM 0 comments
Labels: , , , , , , , , , , , , , , , , , , ,

Dates problem in SQL Server Everywhere edition.

I have created a sample Database for the school project,

After executing the query below, the Date column is supposed to have the dates I have entered before,

However the dates shown are 1900.

Any idea why is this happening?

I appreciate your help.

Thank you.

Query:

Drop table AccountReceivable

GO

--BEGIN TRANSACTION

Create table AccountReceivable

(

AccountRecID int identity (1,1) not null,

PatientID int not null,

PresentCharges int default 0 not null,

PaymentMade money default 0 not null,

PreviousBalance money default 0 not null,

BalanceDue money default 0 not null,

LastPaymentDate datetime not null,

PresentDate datetime default GetDate() not null

)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

PK_AccountRecID Primary Key (AccountRecID)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)

GO

--COMMIT

--query to find delinquent accounts

--DATEDIFF (d, LastPaymentDate, PresentDate)

--Populate the Accounts Table

DELETE AccountReceivable

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )

VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)

GO

--sample query

select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable

GO

--result

PatientID PresentCharges LastPaymentDate PresentDate

-- -- -- --

913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

(24 row(s) affected)

Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds.

Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0–59.

Please use quote for inserting datetime value, it will solve the problem.

use like

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )

VALUES (913235,451.34,50,0,401.34,'4/7/2006',DEFAULT)

Thanks

Sachin

|||

Thank you,

The information was very good,

the problem with the dates is now solved.

Toni.

Posted by inh at 11:20 PM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,

Dates problem in SQL Server Everywhere edition.

I have created a sample Database for the school project,

After executing the query below, the Date column is supposed to have the dates I have entered before,

However the dates shown are 1900.

Any idea why is this happening?

I appreciate your help.

Thank you.

Query:

Drop table AccountReceivable

GO

--BEGIN TRANSACTION

Create table AccountReceivable

(

AccountRecID int identity (1,1) not null,

PatientID int not null,

PresentCharges int default 0 not null,

PaymentMade money default 0 not null,

PreviousBalance money default 0 not null,

BalanceDue money default 0 not null,

LastPaymentDate datetime not null,

PresentDate datetime default GetDate() not null

)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

PK_AccountRecID Primary Key (AccountRecID)

GO

ALTER TABLE AccountReceivable ADD CONSTRAINT

FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)

GO

--COMMIT

--query to find delinquent accounts

--DATEDIFF (d, LastPaymentDate, PresentDate)

--Populate the Accounts Table

DELETE AccountReceivable

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )

VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)

GO

INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)

VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)

GO

--sample query

select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable

GO

--result

PatientID PresentCharges LastPaymentDate PresentDate

-- -- -- --

913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297

900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313

941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327

900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343

(24 row(s) affected)

Repliedin another thread (same question)

Posted by inh at 11:20 PM 0 comments
Labels: , , , , , , , , , , , , , , , , , , ,

Tuesday, February 14, 2012

dateadd update works but adds 2 or 3 yrs-not 1

I have a page that is supposed to add a year to a record when it loads. The problem is that it adds 2 or three years instead.
Here is the page_load event:
Sub page_load(sender as object, e as eventargs)
 Try
 Dim connection As SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
 Dim command As SqlCommand = new SqlCommand("Updateexpiredate", connection)
 command.CommandType = CommandType.StoredProcedure
 Dim param0 As SqlParameter = new SqlParameter("@.memberid",SqlDbType.Int)
 param0.Direction = ParameterDirection.Input
 param0.Value = memberid
 command.Parameters.Add(param0)
 connection.Open()
command.ExecuteNonQuery()
 connection.Close()
 myerror.Text = "Thank You! Your account was updated"
 Catch ex As Exception
 myerror.Text = ex.Message
 End Try
End Sub
And here is the SPROC:
CREATE PROCEDURE Updateexpiredate
 (
 @.memberid int
 )
 AS
 UPDATE
 members
 SET
 expiredate=(dateadd(year,1,expiredate))<--I also tried expiredate=(dateadd(month,12,expiredate)) with the same results
 WHERE
 memberID = @.memberID
GO
... I'm assuming you're calling that method or procedure 2 or three time. :)

edited: Step through your app. It could be that it's called multiple times.|||How do I do that?
I don't even know where to begin with that.
Thanks|||The first thing I would do is turn tracing on for the page, and do a trace.write as the command right before the ExecuteNonQuery. This should give you more information on how many times that part of your code is being executed.

If you are not familiar with trace.write, look here:Trace Logging to Page Output. That is a good debugging tool to have in your pocket.

Terri|||What do you use to develop in? Is it Visual Studio, DreamWeaver, WebMatrix, notepad?

Posted by inh at 5:56 AM 0 comments
Labels: , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)