I have a query like this:
SELECT *
FROM myTable
WHERE myDate BETWEEN '08/13/2003' AND '08/14/2003 23:59:30'
myDate is a datetime
anyways, this query returns records with a myDate of 8/15/2003 00:00:00
if i change the last line of the query to be this:
WHERE myDate BETWEEN '08/13/2003' AND '08/14/2003 23:59:29'
it no longer returns the unwanted record. can anyone make sense of this?this may help:
BETWEEN
Specifies a range to test.
Syntax
test_expression [ NOT ] BETWEEN begin_expression AND end_expression
Arguments
test_expression
Is the expression to test for in the range defined by begin_expression and end_expression. test_expression must be the same data type as both begin_expression and end_expression.
NOT
Specifies that the result of the predicate be negated.
begin_expression
Is any valid Microsoft?SQL Server?expression. begin_expression must be the same data type as both test_expression and end_expression.
end_expression
Is any valid SQL Server expression. end_expression must be the same data type as both test_expression and begin_expression.
AND
Acts as a placeholder indicating that test_expression should be within the range indicated by begin_expression and end_expression.
Result Types
Boolean
Result Value
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
NOT BETWEEN returns TRUE if the value of test_expression is less than the value of begin_expression or greater than the value of end_expression.
Remarks
To specify an exclusive range, use the greater than (>) and less than operators (<). If any input to the BETWEEN or NOT BETWEEN predicate is NULL, the result is UNKNOWN.
Examples
A. Use BETWEEN
This example returns title identifiers for books with year-to-date unit sales from 4,095 through 12,000.
USE pubs
GO
SELECT title_id, ytd_sales
FROM titles
WHERE ytd_sales BETWEEN 4095 AND 12000
GO
Here is the result set:
title_id ytd_sales
-------- -----------
BU1032 4095
BU7832 4095
PC1035 8780
PC8888 4095
TC7777 4095
(5 row(s) affected)
B. Use > and < instead of BETWEEN
This example, which uses greater than (>) and less than (<) operators, returns different results because these operators are not inclusive.
USE pubs
GO
SELECT title_id, ytd_sales
FROM titles
WHERE ytd_sales > 4095 AND ytd_sales < 12000
GO
Here is the result set:
title_id ytd_sales
-------- -----------
PC1035 8780
(1 row(s) affected)
C. Use NOT BETWEEN
This example finds all rows outside a specified range (from 4,095 through 12,000).
USE pubs
GO
SELECT title_id, ytd_sales
FROM titles
WHERE ytd_sales NOT BETWEEN 4095 AND 12000
GO
Here is the result set:
title_id ytd_sales
-------- -----------
BU1111 3876
BU2075 18722
MC2222 2032
MC3021 22246
PS1372 375
PS2091 2045
PS2106 111
PS3333 4072
PS7777 3336
TC3218 375
TC4203 15096
(11 row(s) affected)
See Also
> (Greater Than)
< (Less Than)
Expressions
Functions
Operators (Logical Operators)
SELECT (Subqueries)
WHERE
?988-2000 Microsoft Corporation. All Rights Reserved.also, you may want to consider using the datediff() function:
DATEDIFF
Returns the number of date and time boundaries crossed between two specified dates.
Syntax
DATEDIFF ( datepart , startdate , enddate )
Arguments
datepart
Is the parameter that specifies on which part of the date to calculate the difference. The table lists dateparts and abbreviations recognized by Microsoft?SQL Server?
Datepart Abbreviations
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms
startdate
Is the beginning date for the calculation. startdate is an expression that returns a datetime or smalldatetime value, or a character string in a date format.
Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.
If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if the two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.
For more information about specifying time values, see Time Formats. For more information about specifying dates, see datetime and smalldatetime.
enddate
Is the ending date for the calculation. enddate is an expression that returns a datetime or smalldatetime value, or a character string in a date format.
Return Types
integer
Remarks
startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned.
DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years.
The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result given by DATEDIFF consistent across all data types. The result is a signed integer value equal to the number of datepart boundaries crossed between the first and second date. For example, the number of weeks between Sunday, January 4, and Sunday, January 11, is 1.
Examples
This example determines the difference in days between the current date and the publication date for titles in the pubs database.
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
See Also
CAST and CONVERT
Data Types
Date and Time Functions
?988-2000 Microsoft Corporation. All Rights Reserved.finally, the way I use datediff() is as follows:
select * from tablename where
dateDiff(dd, '#form.fromDate#', a.loggedDate)>=0
and
dateDiff(dd, '#form.toDate#', a.loggedDate)<=0
where the #form.fromDate# and #form.toDate# are the user selected values.just an FYI: if he is using mysql you don't get the chance to use datediff() it is not valid function in mysqlhmmn... thought dateDiff was ansi compliant. Here are some pointers for you mySql'rs
<!-- m --><a class="postlink" href="http://www.faqts.com/knowledge_base/view.phtml/aid/12241/fid/13">http://www.faqts.com/knowledge_base/vie ... 241/fid/13</a><!-- m -->
SELECT *
FROM myTable
WHERE myDate BETWEEN '08/13/2003' AND '08/14/2003 23:59:30'
myDate is a datetime
anyways, this query returns records with a myDate of 8/15/2003 00:00:00
if i change the last line of the query to be this:
WHERE myDate BETWEEN '08/13/2003' AND '08/14/2003 23:59:29'
it no longer returns the unwanted record. can anyone make sense of this?this may help:
BETWEEN
Specifies a range to test.
Syntax
test_expression [ NOT ] BETWEEN begin_expression AND end_expression
Arguments
test_expression
Is the expression to test for in the range defined by begin_expression and end_expression. test_expression must be the same data type as both begin_expression and end_expression.
NOT
Specifies that the result of the predicate be negated.
begin_expression
Is any valid Microsoft?SQL Server?expression. begin_expression must be the same data type as both test_expression and end_expression.
end_expression
Is any valid SQL Server expression. end_expression must be the same data type as both test_expression and begin_expression.
AND
Acts as a placeholder indicating that test_expression should be within the range indicated by begin_expression and end_expression.
Result Types
Boolean
Result Value
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
NOT BETWEEN returns TRUE if the value of test_expression is less than the value of begin_expression or greater than the value of end_expression.
Remarks
To specify an exclusive range, use the greater than (>) and less than operators (<). If any input to the BETWEEN or NOT BETWEEN predicate is NULL, the result is UNKNOWN.
Examples
A. Use BETWEEN
This example returns title identifiers for books with year-to-date unit sales from 4,095 through 12,000.
USE pubs
GO
SELECT title_id, ytd_sales
FROM titles
WHERE ytd_sales BETWEEN 4095 AND 12000
GO
Here is the result set:
title_id ytd_sales
-------- -----------
BU1032 4095
BU7832 4095
PC1035 8780
PC8888 4095
TC7777 4095
(5 row(s) affected)
B. Use > and < instead of BETWEEN
This example, which uses greater than (>) and less than (<) operators, returns different results because these operators are not inclusive.
USE pubs
GO
SELECT title_id, ytd_sales
FROM titles
WHERE ytd_sales > 4095 AND ytd_sales < 12000
GO
Here is the result set:
title_id ytd_sales
-------- -----------
PC1035 8780
(1 row(s) affected)
C. Use NOT BETWEEN
This example finds all rows outside a specified range (from 4,095 through 12,000).
USE pubs
GO
SELECT title_id, ytd_sales
FROM titles
WHERE ytd_sales NOT BETWEEN 4095 AND 12000
GO
Here is the result set:
title_id ytd_sales
-------- -----------
BU1111 3876
BU2075 18722
MC2222 2032
MC3021 22246
PS1372 375
PS2091 2045
PS2106 111
PS3333 4072
PS7777 3336
TC3218 375
TC4203 15096
(11 row(s) affected)
See Also
> (Greater Than)
< (Less Than)
Expressions
Functions
Operators (Logical Operators)
SELECT (Subqueries)
WHERE
?988-2000 Microsoft Corporation. All Rights Reserved.also, you may want to consider using the datediff() function:
DATEDIFF
Returns the number of date and time boundaries crossed between two specified dates.
Syntax
DATEDIFF ( datepart , startdate , enddate )
Arguments
datepart
Is the parameter that specifies on which part of the date to calculate the difference. The table lists dateparts and abbreviations recognized by Microsoft?SQL Server?
Datepart Abbreviations
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms
startdate
Is the beginning date for the calculation. startdate is an expression that returns a datetime or smalldatetime value, or a character string in a date format.
Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.
If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if the two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.
For more information about specifying time values, see Time Formats. For more information about specifying dates, see datetime and smalldatetime.
enddate
Is the ending date for the calculation. enddate is an expression that returns a datetime or smalldatetime value, or a character string in a date format.
Return Types
integer
Remarks
startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned.
DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years.
The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result given by DATEDIFF consistent across all data types. The result is a signed integer value equal to the number of datepart boundaries crossed between the first and second date. For example, the number of weeks between Sunday, January 4, and Sunday, January 11, is 1.
Examples
This example determines the difference in days between the current date and the publication date for titles in the pubs database.
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
See Also
CAST and CONVERT
Data Types
Date and Time Functions
?988-2000 Microsoft Corporation. All Rights Reserved.finally, the way I use datediff() is as follows:
select * from tablename where
dateDiff(dd, '#form.fromDate#', a.loggedDate)>=0
and
dateDiff(dd, '#form.toDate#', a.loggedDate)<=0
where the #form.fromDate# and #form.toDate# are the user selected values.just an FYI: if he is using mysql you don't get the chance to use datediff() it is not valid function in mysqlhmmn... thought dateDiff was ansi compliant. Here are some pointers for you mySql'rs
<!-- m --><a class="postlink" href="http://www.faqts.com/knowledge_base/view.phtml/aid/12241/fid/13">http://www.faqts.com/knowledge_base/vie ... 241/fid/13</a><!-- m -->