while executing the following statement:
select convert(datetime,'Nov 31, 2002 10:05pm')
got the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
the datetime set is mdy
is there any way to make this work though this is not according to the iso standarts.
regards,
harsh.Does november contain 31 days ... hmm .. something is very wrong with your code
this works :-
select convert(datetime,'Nov 30, 2002 10:41PM',107)|||Originally posted by Enigma
Does november contain 31 days ... hmm .. something is very wrong with your code
this works :-
select convert(datetime,'Nov 30, 2002 10:41PM',107)
:D :D :D
how foolish of me!!
actually this was the input to a proc and the parameters were provided by the programmers, so i just tried to check with the same.
nways thnxs.
regards,
harsh.
Showing posts with label executing. Show all posts
Showing posts with label executing. Show all posts
Thursday, March 8, 2012
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)
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.
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)
Friday, February 17, 2012
DateDiff Function February question
I'm executing the following datediff function and getting
a value of 4 instead of 5. I don't understand why.
Please help.
select DateDiff(mm,'10/01/2004','02/28/2005')
TIA,
Vic
DATEDIFF(mm) liteterally counts the nubmer of months, regardless of when in
the month the queries are made. For instance:
select DateDiff(mm,'20041031','20050101')
This returns 3 -- November, December, January
In your case, it looks like you want to "round" based on day of the month.
You could try something like:
DECLARE @.startdate smalldatetime
DECLARE @.enddate smalldatetime
SET @.startdate = '20041001'
SET @.enddate = '20050228'
SELECT DateDiff(mm,@.startdate,@.enddate)
+ CASE
WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
ELSE 0
END
This is probably flawed in one or more ways, so you should tweak it to get
whatever logic you actually need.
Also, please notice that I used the date format YYYYMMDD instead of
MM/DD/YYYY -- The latter format is ambiguous, as it can change based on
locale. Please try to stick with the ISO standard format instead.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vic" <vduran@.specpro-inc.com> wrote in message
news:20b201c50add$73dc1320$a601280a@.phx.gbl...
> I'm executing the following datediff function and getting
> a value of 4 instead of 5. I don't understand why.
> Please help.
> select DateDiff(mm,'10/01/2004','02/28/2005')
> TIA,
> Vic
|||It still doesn't make sense, I don't need a round base if
i'm using the first and last day of the month. If I
execute datediff using 20050101 to 20050131, it will
return one. This is also true for all the month of the
year with the exception of February.
>--Original Message--
>DATEDIFF(mm) liteterally counts the nubmer of months,
regardless of when in
>the month the queries are made. For instance:
>select DateDiff(mm,'20041031','20050101')
>This returns 3 -- November, December, January
>In your case, it looks like you want to "round" based on
day of the month.
>You could try something like:
>DECLARE @.startdate smalldatetime
>DECLARE @.enddate smalldatetime
>SET @.startdate = '20041001'
>SET @.enddate = '20050228'
>SELECT DateDiff(mm,@.startdate,@.enddate)
> + CASE
> WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
> WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
> ELSE 0
> END
>This is probably flawed in one or more ways, so you
should tweak it to get
>whatever logic you actually need.
>Also, please notice that I used the date format YYYYMMDD
instead of
>MM/DD/YYYY -- The latter format is ambiguous, as it can
change based on
>locale. Please try to stick with the ISO standard
format instead.[vbcol=seagreen]
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Vic" <vduran@.specpro-inc.com> wrote in message
>news:20b201c50add$73dc1320$a601280a@.phx.gbl...
getting
>
>.
>
|||Try again. Datediff does NOT return 1 using the first and last dates of
January 2005. If you see different results, please post the code you are
using. Below is an example of datediff and the values returned using the
first and last days of Jan and Feb. Examine the last statement closely
since it should clarify how datediff works. Adam over-simplified the
explanation, but BOL clearly documents the behavior. Datediff counts the
unit BOUNDARIES between the arguments, not the units. It looks like
datediff is not the solution you need; no one will be able to offer an
alternative without knowing what you are specifically trying to accomplish.
set nocount on
select datediff (mm, '20050101', '20050131'), datediff (mm, '20050131',
'20050101')
select datediff (mm, '20050201', '20050228'), datediff (mm, '20050201',
'20050301')
select datediff (mm, '20050228', '20050228'), datediff (mm, '20050228',
'20050301')
<anonymous@.discussions.microsoft.com> wrote in message
news:210901c50ae5$2e8c43a0$a601280a@.phx.gbl...[vbcol=seagreen]
> It still doesn't make sense, I don't need a round base if
> i'm using the first and last day of the month. If I
> execute datediff using 20050101 to 20050131, it will
> return one. This is also true for all the month of the
> year with the exception of February.
>
> regardless of when in
> day of the month.
> should tweak it to get
> instead of
> change based on
> format instead.
> getting
|||If you run
select DateDiff(mm,'20050101','20050131')
and the result is 1, there is a serious problem with your
SQL Server installation. The result is 0.
Please show exactly what you are doing - exactly what is true "for all
the month of the year with the exception of February"? You have not
shown us a specific query that gives the wrong answer.
Steve Kass
Drew University
anonymous@.discussions.microsoft.com wrote:
[vbcol=seagreen]
>It still doesn't make sense, I don't need a round base if
>i'm using the first and last day of the month. If I
>execute datediff using 20050101 to 20050131, it will
>return one. This is also true for all the month of the
>year with the exception of February.
>
>
>regardless of when in
>
>day of the month.
>
>should tweak it to get
>
>instead of
>
>change based on
>
>format instead.
>
>getting
>
a value of 4 instead of 5. I don't understand why.
Please help.
select DateDiff(mm,'10/01/2004','02/28/2005')
TIA,
Vic
DATEDIFF(mm) liteterally counts the nubmer of months, regardless of when in
the month the queries are made. For instance:
select DateDiff(mm,'20041031','20050101')
This returns 3 -- November, December, January
In your case, it looks like you want to "round" based on day of the month.
You could try something like:
DECLARE @.startdate smalldatetime
DECLARE @.enddate smalldatetime
SET @.startdate = '20041001'
SET @.enddate = '20050228'
SELECT DateDiff(mm,@.startdate,@.enddate)
+ CASE
WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
ELSE 0
END
This is probably flawed in one or more ways, so you should tweak it to get
whatever logic you actually need.
Also, please notice that I used the date format YYYYMMDD instead of
MM/DD/YYYY -- The latter format is ambiguous, as it can change based on
locale. Please try to stick with the ISO standard format instead.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vic" <vduran@.specpro-inc.com> wrote in message
news:20b201c50add$73dc1320$a601280a@.phx.gbl...
> I'm executing the following datediff function and getting
> a value of 4 instead of 5. I don't understand why.
> Please help.
> select DateDiff(mm,'10/01/2004','02/28/2005')
> TIA,
> Vic
|||It still doesn't make sense, I don't need a round base if
i'm using the first and last day of the month. If I
execute datediff using 20050101 to 20050131, it will
return one. This is also true for all the month of the
year with the exception of February.
>--Original Message--
>DATEDIFF(mm) liteterally counts the nubmer of months,
regardless of when in
>the month the queries are made. For instance:
>select DateDiff(mm,'20041031','20050101')
>This returns 3 -- November, December, January
>In your case, it looks like you want to "round" based on
day of the month.
>You could try something like:
>DECLARE @.startdate smalldatetime
>DECLARE @.enddate smalldatetime
>SET @.startdate = '20041001'
>SET @.enddate = '20050228'
>SELECT DateDiff(mm,@.startdate,@.enddate)
> + CASE
> WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
> WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
> ELSE 0
> END
>This is probably flawed in one or more ways, so you
should tweak it to get
>whatever logic you actually need.
>Also, please notice that I used the date format YYYYMMDD
instead of
>MM/DD/YYYY -- The latter format is ambiguous, as it can
change based on
>locale. Please try to stick with the ISO standard
format instead.[vbcol=seagreen]
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Vic" <vduran@.specpro-inc.com> wrote in message
>news:20b201c50add$73dc1320$a601280a@.phx.gbl...
getting
>
>.
>
|||Try again. Datediff does NOT return 1 using the first and last dates of
January 2005. If you see different results, please post the code you are
using. Below is an example of datediff and the values returned using the
first and last days of Jan and Feb. Examine the last statement closely
since it should clarify how datediff works. Adam over-simplified the
explanation, but BOL clearly documents the behavior. Datediff counts the
unit BOUNDARIES between the arguments, not the units. It looks like
datediff is not the solution you need; no one will be able to offer an
alternative without knowing what you are specifically trying to accomplish.
set nocount on
select datediff (mm, '20050101', '20050131'), datediff (mm, '20050131',
'20050101')
select datediff (mm, '20050201', '20050228'), datediff (mm, '20050201',
'20050301')
select datediff (mm, '20050228', '20050228'), datediff (mm, '20050228',
'20050301')
<anonymous@.discussions.microsoft.com> wrote in message
news:210901c50ae5$2e8c43a0$a601280a@.phx.gbl...[vbcol=seagreen]
> It still doesn't make sense, I don't need a round base if
> i'm using the first and last day of the month. If I
> execute datediff using 20050101 to 20050131, it will
> return one. This is also true for all the month of the
> year with the exception of February.
>
> regardless of when in
> day of the month.
> should tweak it to get
> instead of
> change based on
> format instead.
> getting
|||If you run
select DateDiff(mm,'20050101','20050131')
and the result is 1, there is a serious problem with your
SQL Server installation. The result is 0.
Please show exactly what you are doing - exactly what is true "for all
the month of the year with the exception of February"? You have not
shown us a specific query that gives the wrong answer.
Steve Kass
Drew University
anonymous@.discussions.microsoft.com wrote:
[vbcol=seagreen]
>It still doesn't make sense, I don't need a round base if
>i'm using the first and last day of the month. If I
>execute datediff using 20050101 to 20050131, it will
>return one. This is also true for all the month of the
>year with the exception of February.
>
>
>regardless of when in
>
>day of the month.
>
>should tweak it to get
>
>instead of
>
>change based on
>
>format instead.
>
>getting
>
DateDiff Function February question
I'm executing the following datediff function and getting
a value of 4 instead of 5. I don't understand why.
Please help.
select DateDiff(mm,'10/01/2004','02/28/2005')
TIA,
VicDATEDIFF(mm) liteterally counts the nubmer of months, regardless of when in
the month the queries are made. For instance:
select DateDiff(mm,'20041031','20050101')
This returns 3 -- November, December, January
In your case, it looks like you want to "round" based on day of the month.
You could try something like:
DECLARE @.startdate smalldatetime
DECLARE @.enddate smalldatetime
SET @.startdate = '20041001'
SET @.enddate = '20050228'
SELECT DateDiff(mm,@.startdate,@.enddate)
+ CASE
WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
ELSE 0
END
This is probably flawed in one or more ways, so you should tweak it to get
whatever logic you actually need.
Also, please notice that I used the date format YYYYMMDD instead of
MM/DD/YYYY -- The latter format is ambiguous, as it can change based on
locale. Please try to stick with the ISO standard format instead.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vic" <vduran@.specpro-inc.com> wrote in message
news:20b201c50add$73dc1320$a601280a@.phx.gbl...
> I'm executing the following datediff function and getting
> a value of 4 instead of 5. I don't understand why.
> Please help.
> select DateDiff(mm,'10/01/2004','02/28/2005')
> TIA,
> Vic|||It still doesn't make sense, I don't need a round base if
i'm using the first and last day of the month. If I
execute datediff using 20050101 to 20050131, it will
return one. This is also true for all the month of the
year with the exception of February.
>--Original Message--
>DATEDIFF(mm) liteterally counts the nubmer of months,
regardless of when in
>the month the queries are made. For instance:
>select DateDiff(mm,'20041031','20050101')
>This returns 3 -- November, December, January
>In your case, it looks like you want to "round" based on
day of the month.
>You could try something like:
>DECLARE @.startdate smalldatetime
>DECLARE @.enddate smalldatetime
>SET @.startdate = '20041001'
>SET @.enddate = '20050228'
>SELECT DateDiff(mm,@.startdate,@.enddate)
> + CASE
> WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
> WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
> ELSE 0
> END
>This is probably flawed in one or more ways, so you
should tweak it to get
>whatever logic you actually need.
>Also, please notice that I used the date format YYYYMMDD
instead of
>MM/DD/YYYY -- The latter format is ambiguous, as it can
change based on
>locale. Please try to stick with the ISO standard
format instead.
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Vic" <vduran@.specpro-inc.com> wrote in message
>news:20b201c50add$73dc1320$a601280a@.phx.gbl...
>> I'm executing the following datediff function and
getting
>> a value of 4 instead of 5. I don't understand why.
>> Please help.
>> select DateDiff(mm,'10/01/2004','02/28/2005')
>> TIA,
>> Vic
>
>.
>|||Try again. Datediff does NOT return 1 using the first and last dates of
January 2005. If you see different results, please post the code you are
using. Below is an example of datediff and the values returned using the
first and last days of Jan and Feb. Examine the last statement closely
since it should clarify how datediff works. Adam over-simplified the
explanation, but BOL clearly documents the behavior. Datediff counts the
unit BOUNDARIES between the arguments, not the units. It looks like
datediff is not the solution you need; no one will be able to offer an
alternative without knowing what you are specifically trying to accomplish.
set nocount on
select datediff (mm, '20050101', '20050131'), datediff (mm, '20050131',
'20050101')
select datediff (mm, '20050201', '20050228'), datediff (mm, '20050201',
'20050301')
select datediff (mm, '20050228', '20050228'), datediff (mm, '20050228',
'20050301')
<anonymous@.discussions.microsoft.com> wrote in message
news:210901c50ae5$2e8c43a0$a601280a@.phx.gbl...
> It still doesn't make sense, I don't need a round base if
> i'm using the first and last day of the month. If I
> execute datediff using 20050101 to 20050131, it will
> return one. This is also true for all the month of the
> year with the exception of February.
>
> >--Original Message--
> >DATEDIFF(mm) liteterally counts the nubmer of months,
> regardless of when in
> >the month the queries are made. For instance:
> >
> >select DateDiff(mm,'20041031','20050101')
> >
> >This returns 3 -- November, December, January
> >
> >In your case, it looks like you want to "round" based on
> day of the month.
> >You could try something like:
> >
> >DECLARE @.startdate smalldatetime
> >DECLARE @.enddate smalldatetime
> >
> >SET @.startdate = '20041001'
> >SET @.enddate = '20050228'
> >
> >SELECT DateDiff(mm,@.startdate,@.enddate)
> > + CASE
> > WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
> > WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
> > ELSE 0
> > END
> >
> >This is probably flawed in one or more ways, so you
> should tweak it to get
> >whatever logic you actually need.
> >
> >Also, please notice that I used the date format YYYYMMDD
> instead of
> >MM/DD/YYYY -- The latter format is ambiguous, as it can
> change based on
> >locale. Please try to stick with the ISO standard
> format instead.
> >
> >
> >--
> >Adam Machanic
> >SQL Server MVP
> >http://www.sqljunkies.com/weblog/amachanic
> >--
> >
> >
> >"Vic" <vduran@.specpro-inc.com> wrote in message
> >news:20b201c50add$73dc1320$a601280a@.phx.gbl...
> >> I'm executing the following datediff function and
> getting
> >> a value of 4 instead of 5. I don't understand why.
> >> Please help.
> >>
> >> select DateDiff(mm,'10/01/2004','02/28/2005')
> >>
> >> TIA,
> >> Vic
> >
> >
> >.
> >|||If you run
select DateDiff(mm,'20050101','20050131')
and the result is 1, there is a serious problem with your
SQL Server installation. The result is 0.
Please show exactly what you are doing - exactly what is true "for all
the month of the year with the exception of February"? You have not
shown us a specific query that gives the wrong answer.
Steve Kass
Drew University
anonymous@.discussions.microsoft.com wrote:
>It still doesn't make sense, I don't need a round base if
>i'm using the first and last day of the month. If I
>execute datediff using 20050101 to 20050131, it will
>return one. This is also true for all the month of the
>year with the exception of February.
>
>
>>--Original Message--
>>DATEDIFF(mm) liteterally counts the nubmer of months,
>>
>regardless of when in
>
>>the month the queries are made. For instance:
>>select DateDiff(mm,'20041031','20050101')
>>This returns 3 -- November, December, January
>>In your case, it looks like you want to "round" based on
>>
>day of the month.
>
>>You could try something like:
>>DECLARE @.startdate smalldatetime
>>DECLARE @.enddate smalldatetime
>>SET @.startdate = '20041001'
>>SET @.enddate = '20050228'
>>SELECT DateDiff(mm,@.startdate,@.enddate)
>>+ CASE
>> WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
>> WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
>> ELSE 0
>>END
>>This is probably flawed in one or more ways, so you
>>
>should tweak it to get
>
>>whatever logic you actually need.
>>Also, please notice that I used the date format YYYYMMDD
>>
>instead of
>
>>MM/DD/YYYY -- The latter format is ambiguous, as it can
>>
>change based on
>
>>locale. Please try to stick with the ISO standard
>>
>format instead.
>
>>--
>>Adam Machanic
>>SQL Server MVP
>>http://www.sqljunkies.com/weblog/amachanic
>>--
>>
>>"Vic" <vduran@.specpro-inc.com> wrote in message
>>news:20b201c50add$73dc1320$a601280a@.phx.gbl...
>>
>>I'm executing the following datediff function and
>>
>getting
>
>>a value of 4 instead of 5. I don't understand why.
>>Please help.
>>select DateDiff(mm,'10/01/2004','02/28/2005')
>>TIA,
>>Vic
>>
>>.
>>
a value of 4 instead of 5. I don't understand why.
Please help.
select DateDiff(mm,'10/01/2004','02/28/2005')
TIA,
VicDATEDIFF(mm) liteterally counts the nubmer of months, regardless of when in
the month the queries are made. For instance:
select DateDiff(mm,'20041031','20050101')
This returns 3 -- November, December, January
In your case, it looks like you want to "round" based on day of the month.
You could try something like:
DECLARE @.startdate smalldatetime
DECLARE @.enddate smalldatetime
SET @.startdate = '20041001'
SET @.enddate = '20050228'
SELECT DateDiff(mm,@.startdate,@.enddate)
+ CASE
WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
ELSE 0
END
This is probably flawed in one or more ways, so you should tweak it to get
whatever logic you actually need.
Also, please notice that I used the date format YYYYMMDD instead of
MM/DD/YYYY -- The latter format is ambiguous, as it can change based on
locale. Please try to stick with the ISO standard format instead.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vic" <vduran@.specpro-inc.com> wrote in message
news:20b201c50add$73dc1320$a601280a@.phx.gbl...
> I'm executing the following datediff function and getting
> a value of 4 instead of 5. I don't understand why.
> Please help.
> select DateDiff(mm,'10/01/2004','02/28/2005')
> TIA,
> Vic|||It still doesn't make sense, I don't need a round base if
i'm using the first and last day of the month. If I
execute datediff using 20050101 to 20050131, it will
return one. This is also true for all the month of the
year with the exception of February.
>--Original Message--
>DATEDIFF(mm) liteterally counts the nubmer of months,
regardless of when in
>the month the queries are made. For instance:
>select DateDiff(mm,'20041031','20050101')
>This returns 3 -- November, December, January
>In your case, it looks like you want to "round" based on
day of the month.
>You could try something like:
>DECLARE @.startdate smalldatetime
>DECLARE @.enddate smalldatetime
>SET @.startdate = '20041001'
>SET @.enddate = '20050228'
>SELECT DateDiff(mm,@.startdate,@.enddate)
> + CASE
> WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
> WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
> ELSE 0
> END
>This is probably flawed in one or more ways, so you
should tweak it to get
>whatever logic you actually need.
>Also, please notice that I used the date format YYYYMMDD
instead of
>MM/DD/YYYY -- The latter format is ambiguous, as it can
change based on
>locale. Please try to stick with the ISO standard
format instead.
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Vic" <vduran@.specpro-inc.com> wrote in message
>news:20b201c50add$73dc1320$a601280a@.phx.gbl...
>> I'm executing the following datediff function and
getting
>> a value of 4 instead of 5. I don't understand why.
>> Please help.
>> select DateDiff(mm,'10/01/2004','02/28/2005')
>> TIA,
>> Vic
>
>.
>|||Try again. Datediff does NOT return 1 using the first and last dates of
January 2005. If you see different results, please post the code you are
using. Below is an example of datediff and the values returned using the
first and last days of Jan and Feb. Examine the last statement closely
since it should clarify how datediff works. Adam over-simplified the
explanation, but BOL clearly documents the behavior. Datediff counts the
unit BOUNDARIES between the arguments, not the units. It looks like
datediff is not the solution you need; no one will be able to offer an
alternative without knowing what you are specifically trying to accomplish.
set nocount on
select datediff (mm, '20050101', '20050131'), datediff (mm, '20050131',
'20050101')
select datediff (mm, '20050201', '20050228'), datediff (mm, '20050201',
'20050301')
select datediff (mm, '20050228', '20050228'), datediff (mm, '20050228',
'20050301')
<anonymous@.discussions.microsoft.com> wrote in message
news:210901c50ae5$2e8c43a0$a601280a@.phx.gbl...
> It still doesn't make sense, I don't need a round base if
> i'm using the first and last day of the month. If I
> execute datediff using 20050101 to 20050131, it will
> return one. This is also true for all the month of the
> year with the exception of February.
>
> >--Original Message--
> >DATEDIFF(mm) liteterally counts the nubmer of months,
> regardless of when in
> >the month the queries are made. For instance:
> >
> >select DateDiff(mm,'20041031','20050101')
> >
> >This returns 3 -- November, December, January
> >
> >In your case, it looks like you want to "round" based on
> day of the month.
> >You could try something like:
> >
> >DECLARE @.startdate smalldatetime
> >DECLARE @.enddate smalldatetime
> >
> >SET @.startdate = '20041001'
> >SET @.enddate = '20050228'
> >
> >SELECT DateDiff(mm,@.startdate,@.enddate)
> > + CASE
> > WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
> > WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
> > ELSE 0
> > END
> >
> >This is probably flawed in one or more ways, so you
> should tweak it to get
> >whatever logic you actually need.
> >
> >Also, please notice that I used the date format YYYYMMDD
> instead of
> >MM/DD/YYYY -- The latter format is ambiguous, as it can
> change based on
> >locale. Please try to stick with the ISO standard
> format instead.
> >
> >
> >--
> >Adam Machanic
> >SQL Server MVP
> >http://www.sqljunkies.com/weblog/amachanic
> >--
> >
> >
> >"Vic" <vduran@.specpro-inc.com> wrote in message
> >news:20b201c50add$73dc1320$a601280a@.phx.gbl...
> >> I'm executing the following datediff function and
> getting
> >> a value of 4 instead of 5. I don't understand why.
> >> Please help.
> >>
> >> select DateDiff(mm,'10/01/2004','02/28/2005')
> >>
> >> TIA,
> >> Vic
> >
> >
> >.
> >|||If you run
select DateDiff(mm,'20050101','20050131')
and the result is 1, there is a serious problem with your
SQL Server installation. The result is 0.
Please show exactly what you are doing - exactly what is true "for all
the month of the year with the exception of February"? You have not
shown us a specific query that gives the wrong answer.
Steve Kass
Drew University
anonymous@.discussions.microsoft.com wrote:
>It still doesn't make sense, I don't need a round base if
>i'm using the first and last day of the month. If I
>execute datediff using 20050101 to 20050131, it will
>return one. This is also true for all the month of the
>year with the exception of February.
>
>
>>--Original Message--
>>DATEDIFF(mm) liteterally counts the nubmer of months,
>>
>regardless of when in
>
>>the month the queries are made. For instance:
>>select DateDiff(mm,'20041031','20050101')
>>This returns 3 -- November, December, January
>>In your case, it looks like you want to "round" based on
>>
>day of the month.
>
>>You could try something like:
>>DECLARE @.startdate smalldatetime
>>DECLARE @.enddate smalldatetime
>>SET @.startdate = '20041001'
>>SET @.enddate = '20050228'
>>SELECT DateDiff(mm,@.startdate,@.enddate)
>>+ CASE
>> WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
>> WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
>> ELSE 0
>>END
>>This is probably flawed in one or more ways, so you
>>
>should tweak it to get
>
>>whatever logic you actually need.
>>Also, please notice that I used the date format YYYYMMDD
>>
>instead of
>
>>MM/DD/YYYY -- The latter format is ambiguous, as it can
>>
>change based on
>
>>locale. Please try to stick with the ISO standard
>>
>format instead.
>
>>--
>>Adam Machanic
>>SQL Server MVP
>>http://www.sqljunkies.com/weblog/amachanic
>>--
>>
>>"Vic" <vduran@.specpro-inc.com> wrote in message
>>news:20b201c50add$73dc1320$a601280a@.phx.gbl...
>>
>>I'm executing the following datediff function and
>>
>getting
>
>>a value of 4 instead of 5. I don't understand why.
>>Please help.
>>select DateDiff(mm,'10/01/2004','02/28/2005')
>>TIA,
>>Vic
>>
>>.
>>
DateDiff Function February question
I'm executing the following datediff function and getting
a value of 4 instead of 5. I don't understand why.
Please help.
select DateDiff(mm,'10/01/2004','02/28/2005')
TIA,
VicDATEDIFF(mm) liteterally counts the nubmer of months, regardless of when in
the month the queries are made. For instance:
select DateDiff(mm,'20041031','20050101')
This returns 3 -- November, December, January
In your case, it looks like you want to "round" based on day of the month.
You could try something like:
DECLARE @.startdate smalldatetime
DECLARE @.enddate smalldatetime
SET @.startdate = '20041001'
SET @.enddate = '20050228'
SELECT DateDiff(mm,@.startdate,@.enddate)
+ CASE
WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
ELSE 0
END
This is probably flawed in one or more ways, so you should tweak it to get
whatever logic you actually need.
Also, please notice that I used the date format YYYYMMDD instead of
MM/DD/YYYY -- The latter format is ambiguous, as it can change based on
locale. Please try to stick with the ISO standard format instead.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vic" <vduran@.specpro-inc.com> wrote in message
news:20b201c50add$73dc1320$a601280a@.phx.gbl...
> I'm executing the following datediff function and getting
> a value of 4 instead of 5. I don't understand why.
> Please help.
> select DateDiff(mm,'10/01/2004','02/28/2005')
> TIA,
> Vic|||It still doesn't make sense, I don't need a round base if
i'm using the first and last day of the month. If I
execute datediff using 20050101 to 20050131, it will
return one. This is also true for all the month of the
year with the exception of February.
>--Original Message--
>DATEDIFF(mm) liteterally counts the nubmer of months,
regardless of when in
>the month the queries are made. For instance:
>select DateDiff(mm,'20041031','20050101')
>This returns 3 -- November, December, January
>In your case, it looks like you want to "round" based on
day of the month.
>You could try something like:
>DECLARE @.startdate smalldatetime
>DECLARE @.enddate smalldatetime
>SET @.startdate = '20041001'
>SET @.enddate = '20050228'
>SELECT DateDiff(mm,@.startdate,@.enddate)
> + CASE
> WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
> WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
> ELSE 0
> END
>This is probably flawed in one or more ways, so you
should tweak it to get
>whatever logic you actually need.
>Also, please notice that I used the date format YYYYMMDD
instead of
>MM/DD/YYYY -- The latter format is ambiguous, as it can
change based on
>locale. Please try to stick with the ISO standard
format instead.
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Vic" <vduran@.specpro-inc.com> wrote in message
>news:20b201c50add$73dc1320$a601280a@.phx.gbl...
getting[vbcol=seagreen]
>
>.
>|||Try again. Datediff does NOT return 1 using the first and last dates of
January 2005. If you see different results, please post the code you are
using. Below is an example of datediff and the values returned using the
first and last days of Jan and Feb. Examine the last statement closely
since it should clarify how datediff works. Adam over-simplified the
explanation, but BOL clearly documents the behavior. Datediff counts the
unit BOUNDARIES between the arguments, not the units. It looks like
datediff is not the solution you need; no one will be able to offer an
alternative without knowing what you are specifically trying to accomplish.
set nocount on
select datediff (mm, '20050101', '20050131'), datediff (mm, '20050131',
'20050101')
select datediff (mm, '20050201', '20050228'), datediff (mm, '20050201',
'20050301')
select datediff (mm, '20050228', '20050228'), datediff (mm, '20050228',
'20050301')
<anonymous@.discussions.microsoft.com> wrote in message
news:210901c50ae5$2e8c43a0$a601280a@.phx.gbl...[vbcol=seagreen]
> It still doesn't make sense, I don't need a round base if
> i'm using the first and last day of the month. If I
> execute datediff using 20050101 to 20050131, it will
> return one. This is also true for all the month of the
> year with the exception of February.
>
>
> regardless of when in
> day of the month.
> should tweak it to get
> instead of
> change based on
> format instead.
> getting|||If you run
select DateDiff(mm,'20050101','20050131')
and the result is 1, there is a serious problem with your
SQL Server installation. The result is 0.
Please show exactly what you are doing - exactly what is true "for all
the month of the year with the exception of February"? You have not
shown us a specific query that gives the wrong answer.
Steve Kass
Drew University
anonymous@.discussions.microsoft.com wrote:
[vbcol=seagreen]
>It still doesn't make sense, I don't need a round base if
>i'm using the first and last day of the month. If I
>execute datediff using 20050101 to 20050131, it will
>return one. This is also true for all the month of the
>year with the exception of February.
>
>
>
>regardless of when in
>
>day of the month.
>
>should tweak it to get
>
>instead of
>
>change based on
>
>format instead.
>
>getting
>
a value of 4 instead of 5. I don't understand why.
Please help.
select DateDiff(mm,'10/01/2004','02/28/2005')
TIA,
VicDATEDIFF(mm) liteterally counts the nubmer of months, regardless of when in
the month the queries are made. For instance:
select DateDiff(mm,'20041031','20050101')
This returns 3 -- November, December, January
In your case, it looks like you want to "round" based on day of the month.
You could try something like:
DECLARE @.startdate smalldatetime
DECLARE @.enddate smalldatetime
SET @.startdate = '20041001'
SET @.enddate = '20050228'
SELECT DateDiff(mm,@.startdate,@.enddate)
+ CASE
WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
ELSE 0
END
This is probably flawed in one or more ways, so you should tweak it to get
whatever logic you actually need.
Also, please notice that I used the date format YYYYMMDD instead of
MM/DD/YYYY -- The latter format is ambiguous, as it can change based on
locale. Please try to stick with the ISO standard format instead.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Vic" <vduran@.specpro-inc.com> wrote in message
news:20b201c50add$73dc1320$a601280a@.phx.gbl...
> I'm executing the following datediff function and getting
> a value of 4 instead of 5. I don't understand why.
> Please help.
> select DateDiff(mm,'10/01/2004','02/28/2005')
> TIA,
> Vic|||It still doesn't make sense, I don't need a round base if
i'm using the first and last day of the month. If I
execute datediff using 20050101 to 20050131, it will
return one. This is also true for all the month of the
year with the exception of February.
>--Original Message--
>DATEDIFF(mm) liteterally counts the nubmer of months,
regardless of when in
>the month the queries are made. For instance:
>select DateDiff(mm,'20041031','20050101')
>This returns 3 -- November, December, January
>In your case, it looks like you want to "round" based on
day of the month.
>You could try something like:
>DECLARE @.startdate smalldatetime
>DECLARE @.enddate smalldatetime
>SET @.startdate = '20041001'
>SET @.enddate = '20050228'
>SELECT DateDiff(mm,@.startdate,@.enddate)
> + CASE
> WHEN DAY (@.startdate) > DAY (@.enddate) THEN - 1
> WHEN DAY (@.startdate) < DAY (@.enddate) THEN 1
> ELSE 0
> END
>This is probably flawed in one or more ways, so you
should tweak it to get
>whatever logic you actually need.
>Also, please notice that I used the date format YYYYMMDD
instead of
>MM/DD/YYYY -- The latter format is ambiguous, as it can
change based on
>locale. Please try to stick with the ISO standard
format instead.
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Vic" <vduran@.specpro-inc.com> wrote in message
>news:20b201c50add$73dc1320$a601280a@.phx.gbl...
getting[vbcol=seagreen]
>
>.
>|||Try again. Datediff does NOT return 1 using the first and last dates of
January 2005. If you see different results, please post the code you are
using. Below is an example of datediff and the values returned using the
first and last days of Jan and Feb. Examine the last statement closely
since it should clarify how datediff works. Adam over-simplified the
explanation, but BOL clearly documents the behavior. Datediff counts the
unit BOUNDARIES between the arguments, not the units. It looks like
datediff is not the solution you need; no one will be able to offer an
alternative without knowing what you are specifically trying to accomplish.
set nocount on
select datediff (mm, '20050101', '20050131'), datediff (mm, '20050131',
'20050101')
select datediff (mm, '20050201', '20050228'), datediff (mm, '20050201',
'20050301')
select datediff (mm, '20050228', '20050228'), datediff (mm, '20050228',
'20050301')
<anonymous@.discussions.microsoft.com> wrote in message
news:210901c50ae5$2e8c43a0$a601280a@.phx.gbl...[vbcol=seagreen]
> It still doesn't make sense, I don't need a round base if
> i'm using the first and last day of the month. If I
> execute datediff using 20050101 to 20050131, it will
> return one. This is also true for all the month of the
> year with the exception of February.
>
>
> regardless of when in
> day of the month.
> should tweak it to get
> instead of
> change based on
> format instead.
> getting|||If you run
select DateDiff(mm,'20050101','20050131')
and the result is 1, there is a serious problem with your
SQL Server installation. The result is 0.
Please show exactly what you are doing - exactly what is true "for all
the month of the year with the exception of February"? You have not
shown us a specific query that gives the wrong answer.
Steve Kass
Drew University
anonymous@.discussions.microsoft.com wrote:
[vbcol=seagreen]
>It still doesn't make sense, I don't need a round base if
>i'm using the first and last day of the month. If I
>execute datediff using 20050101 to 20050131, it will
>return one. This is also true for all the month of the
>year with the exception of February.
>
>
>
>regardless of when in
>
>day of the month.
>
>should tweak it to get
>
>instead of
>
>change based on
>
>format instead.
>
>getting
>
Subscribe to:
Posts (Atom)