|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Date RangeSELECT CQTLOAN.LOAN_NUMBER, CQTLOAN.LOAN_CREATED_DATE FROM CQDS50.dbo.CQT_LOAN CQTLOAN WHERE MONTH(CQTLOAN.LOAN_CREATED_DATE) = '06' --WHERE CQTLOAN.LOAN_CREATED_DATE BETWEEN '06/01/2006' AND '06/30/2006' ORDER BY CQTLOAN.LOAN_CREATED_DATE SELECT CQTLOAN.LOAN_NUMBER, CQTLOAN.LOAN_CREATED_DATE FROM CQDS50.dbo.CQT_LOAN CQTLOAN --WHERE MONTH(CQTLOAN.LOAN_CREATED_DATE) = '06' WHERE CQTLOAN.LOAN_CREATED_DATE BETWEEN '06/01/2006' AND '06/30/2006' ORDER BY CQTLOAN.LOAN_CREATED_DATE These are the rows that I am NOT getting back with the between statement... 2500050030 2006-06-30 10:46:23.437 2500050031 2006-06-30 14:17:03.450 2500050032 2006-06-30 16:09:16.467 The column CQTLOAN.LOAN_CREATED_DATE is defined as datetime. What am I missing here??? Is there some sort of limitation using "BETWEEN"??? Any reply is GREATLY appreciated. Thanks! wnfisba WHERE Loan_Created_Date >= '20060601'
AND Loan_Created_Date < '20060701' Show quoteHide quote "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message news:1FFAFA4F-76F1-4987-BFCB-7ABAC8A4636E@microsoft.com... > Why would the following SQL return different rows??? > > SELECT CQTLOAN.LOAN_NUMBER, > CQTLOAN.LOAN_CREATED_DATE > FROM CQDS50.dbo.CQT_LOAN CQTLOAN > WHERE MONTH(CQTLOAN.LOAN_CREATED_DATE) = '06' > --WHERE CQTLOAN.LOAN_CREATED_DATE BETWEEN '06/01/2006' AND '06/30/2006' > ORDER BY CQTLOAN.LOAN_CREATED_DATE > > SELECT CQTLOAN.LOAN_NUMBER, > CQTLOAN.LOAN_CREATED_DATE > FROM CQDS50.dbo.CQT_LOAN CQTLOAN > --WHERE MONTH(CQTLOAN.LOAN_CREATED_DATE) = '06' > WHERE CQTLOAN.LOAN_CREATED_DATE BETWEEN '06/01/2006' AND '06/30/2006' > ORDER BY CQTLOAN.LOAN_CREATED_DATE > > > > These are the rows that I am NOT getting back with the between > statement... > 2500050030 2006-06-30 10:46:23.437 > 2500050031 2006-06-30 14:17:03.450 > 2500050032 2006-06-30 16:09:16.467 > > > The column CQTLOAN.LOAN_CREATED_DATE is defined as datetime. > > What am I missing here??? Is there some sort of limitation using > "BETWEEN"??? > > Any reply is GREATLY appreciated. > > Thanks! > > wnfisba > What's the deal with BETWEEN not working though???
Is there an anomaly with BETWEEN that I'm not aware of??? Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > WHERE Loan_Created_Date >= '20060601' > AND Loan_Created_Date < '20060701' > > > > > > "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message > news:1FFAFA4F-76F1-4987-BFCB-7ABAC8A4636E@microsoft.com... > > Why would the following SQL return different rows??? > > > > SELECT CQTLOAN.LOAN_NUMBER, > > CQTLOAN.LOAN_CREATED_DATE > > FROM CQDS50.dbo.CQT_LOAN CQTLOAN > > WHERE MONTH(CQTLOAN.LOAN_CREATED_DATE) = '06' > > --WHERE CQTLOAN.LOAN_CREATED_DATE BETWEEN '06/01/2006' AND '06/30/2006' > > ORDER BY CQTLOAN.LOAN_CREATED_DATE > > > > SELECT CQTLOAN.LOAN_NUMBER, > > CQTLOAN.LOAN_CREATED_DATE > > FROM CQDS50.dbo.CQT_LOAN CQTLOAN > > --WHERE MONTH(CQTLOAN.LOAN_CREATED_DATE) = '06' > > WHERE CQTLOAN.LOAN_CREATED_DATE BETWEEN '06/01/2006' AND '06/30/2006' > > ORDER BY CQTLOAN.LOAN_CREATED_DATE > > > > > > > > These are the rows that I am NOT getting back with the between > > statement... > > 2500050030 2006-06-30 10:46:23.437 > > 2500050031 2006-06-30 14:17:03.450 > > 2500050032 2006-06-30 16:09:16.467 > > > > > > The column CQTLOAN.LOAN_CREATED_DATE is defined as datetime. > > > > What am I missing here??? Is there some sort of limitation using > > "BETWEEN"??? > > > > Any reply is GREATLY appreciated. > > > > Thanks! > > > > wnfisba > > > > > > What's the deal with BETWEEN not working though??? BETWEEN works fine. The problem is the misconception of how SQL Server treats a datetime without a time value. > Is there an anomaly with BETWEEN that I'm not aware of??? You should go and read the links that Arnie and I submitted.Simple.
When using BETWEEN and datetime datatypes, you have to remember that a datetime value that does not include the time portion will be limited to exactly midnight on that date. So, in this case, [AND '06/30/2006'] does NOT include any records after midnight on June 30th. There are issues with using BETWEEN. You might benefit from this material related to working with datetime datatypes. Datetime -Calendar Table http://www.aspfaq.com/show.asp?id=2519 Datetime -How to count the number of business days http://www.aspfaq.com/show.asp?id=2453 DateTime -Working with http://www.karaszi.com/SQLServer/info_datetime.asp http://omnibuzz-sql.blogspot.com/2006/06/date-functions-in-sql-server-faq.html http://www.sqlservercentral.com/columnists/dasanka/datetimevaluesandtimezones.asp http://realsqlguy.com/twiki/bin/view/RealSQLGuy/TipsForWorkingWithDateTimeValues http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp http://www.murach.com/books/sqls/article.htm http://www.aspfaq.com/show.asp?id=2081 -- Show quoteHide quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message news:1FFAFA4F-76F1-4987-BFCB-7ABAC8A4636E@microsoft.com... > Why would the following SQL return different rows??? > > SELECT CQTLOAN.LOAN_NUMBER, > CQTLOAN.LOAN_CREATED_DATE > FROM CQDS50.dbo.CQT_LOAN CQTLOAN > WHERE MONTH(CQTLOAN.LOAN_CREATED_DATE) = '06' > --WHERE CQTLOAN.LOAN_CREATED_DATE BETWEEN '06/01/2006' AND '06/30/2006' > ORDER BY CQTLOAN.LOAN_CREATED_DATE > > SELECT CQTLOAN.LOAN_NUMBER, > CQTLOAN.LOAN_CREATED_DATE > FROM CQDS50.dbo.CQT_LOAN CQTLOAN > --WHERE MONTH(CQTLOAN.LOAN_CREATED_DATE) = '06' > WHERE CQTLOAN.LOAN_CREATED_DATE BETWEEN '06/01/2006' AND '06/30/2006' > ORDER BY CQTLOAN.LOAN_CREATED_DATE > > > > These are the rows that I am NOT getting back with the between > statement... > 2500050030 2006-06-30 10:46:23.437 > 2500050031 2006-06-30 14:17:03.450 > 2500050032 2006-06-30 16:09:16.467 > > > The column CQTLOAN.LOAN_CREATED_DATE is defined as datetime. > > What am I missing here??? Is there some sort of limitation using > "BETWEEN"??? > > Any reply is GREATLY appreciated. > > Thanks! > > wnfisba > And
http://databases.aspfaq.com/general/should-i-use-between-in-my-database-queries.html Show quoteHide quote "Arnie Rowland" <ar***@1568.com> wrote in message news:%23Bfk1SD2GHA.1304@TK2MSFTNGP05.phx.gbl... > Simple. > > When using BETWEEN and datetime datatypes, you have to remember that a > datetime value that does not include the time portion will be limited to > exactly midnight on that date. > > So, in this case, [AND '06/30/2006'] does NOT include any records after > midnight on June 30th. > > There are issues with using BETWEEN. You might benefit from this material > related to working with datetime datatypes. > > Datetime -Calendar Table > http://www.aspfaq.com/show.asp?id=2519 > > Datetime -How to count the number of business days > http://www.aspfaq.com/show.asp?id=2453 > > DateTime -Working with > http://www.karaszi.com/SQLServer/info_datetime.asp > http://omnibuzz-sql.blogspot.com/2006/06/date-functions-in-sql-server-faq.html > http://www.sqlservercentral.com/columnists/dasanka/datetimevaluesandtimezones.asp > http://realsqlguy.com/twiki/bin/view/RealSQLGuy/TipsForWorkingWithDateTimeValues > http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp > http://www.murach.com/books/sqls/article.htm > http://www.aspfaq.com/show.asp?id=2081 Thanks Aaron, I knew there one a link somewhere -and I couldn't find it in
my resource db. -- Show quoteHide quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:OhjJeVD2GHA.3372@TK2MSFTNGP04.phx.gbl... > And > http://databases.aspfaq.com/general/should-i-use-between-in-my-database-queries.html > > > > > > "Arnie Rowland" <ar***@1568.com> wrote in message > news:%23Bfk1SD2GHA.1304@TK2MSFTNGP05.phx.gbl... >> Simple. >> >> When using BETWEEN and datetime datatypes, you have to remember that a >> datetime value that does not include the time portion will be limited to >> exactly midnight on that date. >> >> So, in this case, [AND '06/30/2006'] does NOT include any records after >> midnight on June 30th. >> >> There are issues with using BETWEEN. You might benefit from this material >> related to working with datetime datatypes. >> >> Datetime -Calendar Table >> http://www.aspfaq.com/show.asp?id=2519 >> >> Datetime -How to count the number of business days >> http://www.aspfaq.com/show.asp?id=2453 >> >> DateTime -Working with >> http://www.karaszi.com/SQLServer/info_datetime.asp >> http://omnibuzz-sql.blogspot.com/2006/06/date-functions-in-sql-server-faq.html >> http://www.sqlservercentral.com/columnists/dasanka/datetimevaluesandtimezones.asp >> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/TipsForWorkingWithDateTimeValues >> http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp >> http://www.murach.com/books/sqls/article.htm >> http://www.aspfaq.com/show.asp?id=2081 > > In your case, it's because BETWEEN '06/01/2006' AND '06/30/2006' returns
datetimes between '06/01/2006 00:00:00' AND '06/30/2006 00:00:00', so you miss any entry on 6/30 with a time > 00:00:00. The correct way to write this is WHERE CQTLOAN.LOAN_CREATED_DATE >= '200606/01' AND CQTLOAN.LOAN_CREATED_DATE < '20060701' Two possible reasons, MONTH(...) = 6 just checks for month of 6, so will return any row with a month of 6 from ANY yoer, for example 2003-06-02. '06/01/2006' can be an ambigious form for a date (for example, it's Jan 6, 1006 in most places other than the US), so you should enter your dates like yyyymmdd which will be interpeted the same on any server. Tom Show quoteHide quote "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message news:1FFAFA4F-76F1-4987-BFCB-7ABAC8A4636E@microsoft.com... > Why would the following SQL return different rows??? > > SELECT CQTLOAN.LOAN_NUMBER, > CQTLOAN.LOAN_CREATED_DATE > FROM CQDS50.dbo.CQT_LOAN CQTLOAN > WHERE MONTH(CQTLOAN.LOAN_CREATED_DATE) = '06' > --WHERE CQTLOAN.LOAN_CREATED_DATE BETWEEN '06/01/2006' AND '06/30/2006' > ORDER BY CQTLOAN.LOAN_CREATED_DATE > > SELECT CQTLOAN.LOAN_NUMBER, > CQTLOAN.LOAN_CREATED_DATE > FROM CQDS50.dbo.CQT_LOAN CQTLOAN > --WHERE MONTH(CQTLOAN.LOAN_CREATED_DATE) = '06' > WHERE CQTLOAN.LOAN_CREATED_DATE BETWEEN '06/01/2006' AND '06/30/2006' > ORDER BY CQTLOAN.LOAN_CREATED_DATE > > > > These are the rows that I am NOT getting back with the between > statement... > 2500050030 2006-06-30 10:46:23.437 > 2500050031 2006-06-30 14:17:03.450 > 2500050032 2006-06-30 16:09:16.467 > > > The column CQTLOAN.LOAN_CREATED_DATE is defined as datetime. > > What am I missing here??? Is there some sort of limitation using > "BETWEEN"??? > > Any reply is GREATLY appreciated. > > Thanks! > > wnfisba > Got Ya. So its not defaulting the time to 23:59:59...
You people are the BEST. Thanks! And sooooooooo prompt! Show quoteHide quote "Tom Cooper" wrote: > In your case, it's because BETWEEN '06/01/2006' AND '06/30/2006' returns > datetimes between '06/01/2006 00:00:00' AND '06/30/2006 00:00:00', so you > miss any entry on 6/30 with a time > 00:00:00. The correct way to write > this is > WHERE CQTLOAN.LOAN_CREATED_DATE >= '200606/01' > AND CQTLOAN.LOAN_CREATED_DATE < '20060701' > > > Two possible reasons, > MONTH(...) = 6 just checks for month of 6, so will return any row with a > month of 6 from ANY yoer, for example 2003-06-02. > > '06/01/2006' can be an ambigious form for a date (for example, it's Jan 6, > 1006 in most places other than the US), so you should enter your dates like > yyyymmdd which will be interpeted the same on any server. > > Tom > > "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message > news:1FFAFA4F-76F1-4987-BFCB-7ABAC8A4636E@microsoft.com... > > Why would the following SQL return different rows??? > > > > SELECT CQTLOAN.LOAN_NUMBER, > > CQTLOAN.LOAN_CREATED_DATE > > FROM CQDS50.dbo.CQT_LOAN CQTLOAN > > WHERE MONTH(CQTLOAN.LOAN_CREATED_DATE) = '06' > > --WHERE CQTLOAN.LOAN_CREATED_DATE BETWEEN '06/01/2006' AND '06/30/2006' > > ORDER BY CQTLOAN.LOAN_CREATED_DATE > > > > SELECT CQTLOAN.LOAN_NUMBER, > > CQTLOAN.LOAN_CREATED_DATE > > FROM CQDS50.dbo.CQT_LOAN CQTLOAN > > --WHERE MONTH(CQTLOAN.LOAN_CREATED_DATE) = '06' > > WHERE CQTLOAN.LOAN_CREATED_DATE BETWEEN '06/01/2006' AND '06/30/2006' > > ORDER BY CQTLOAN.LOAN_CREATED_DATE > > > > > > > > These are the rows that I am NOT getting back with the between > > statement... > > 2500050030 2006-06-30 10:46:23.437 > > 2500050031 2006-06-30 14:17:03.450 > > 2500050032 2006-06-30 16:09:16.467 > > > > > > The column CQTLOAN.LOAN_CREATED_DATE is defined as datetime. > > > > What am I missing here??? Is there some sort of limitation using > > "BETWEEN"??? > > > > Any reply is GREATLY appreciated. > > > > Thanks! > > > > wnfisba > > > > > > Got Ya. So its not defaulting the time to 23:59:59... Why would it? You would need to tell it that.Do you expect WHERE x BETWEEN 5 and 7 to return 7.99? A It is a normal mindset to expect a date to equal a date regardless of time
of day. I think it is a problem that we all encountered when we first worked with a datetime datatype. The problem is that databases ASSUME or DEFAULT missing date components. Most people who are not experienced with datetime datatypes would reasonably expect that between to work. It is rather unfair to assume that this should be obvious to someone who has not been burned by it at least once (or specifically warned by someone else). I know Oracle offers a TRUNC function which strips the time from the date (basically setting it to midnight). Both Oracle and SQL Server assume midnight if no time is given. In Oracle, you can convert a string to a date and leave out any number of date components. It will default the month, day, and year if they are not specified. Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:%23FiKfpD2GHA.4752@TK2MSFTNGP05.phx.gbl... > > Got Ya. So its not defaulting the time to 23:59:59... > > Why would it? You would need to tell it that. > > Do you expect WHERE x BETWEEN 5 and 7 to return 7.99? > > A > > > worked with a datetime datatype. The problem is that databases ASSUME or But if the OP inserted dates without time component, the between would have > DEFAULT missing date components. worked fine. A It is a 3rd party application...So the opportunity to insert just dates was
sort of out of our control. Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > > worked with a datetime datatype. The problem is that databases ASSUME or > > DEFAULT missing date components. > > But if the OP inserted dates without time component, the between would have > worked fine. > > A > > > Ahhh...
But we are also assuming that the OP created the code which does the inserts and is not simply trying to get data out of a DB App that someone else built. I'm just saying it is an honest mistake for datetime newbies that is easy for us to take for granted. Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:uuOCgEE2GHA.480@TK2MSFTNGP06.phx.gbl... > > worked with a datetime datatype. The problem is that databases ASSUME or > > DEFAULT missing date components. > > But if the OP inserted dates without time component, the between would have > worked fine. > > A > > Also, BETWEEN is a bad choice for comparing datetimes (IMHO) because of the
fact that SQL Server doesn't really handle all values of a datetime down to the milllisecond. So, if you say '20060630 23:59:59.999', SQL Server treats it as '20060701 00:00:00.000' because that is the closest datetime value SQL Server can actually store in a datetime variable or column. So when I want this sort of condition I always use WHERE date >= ... AND date < ... For example, try running the following: Set NoCount On use pubs go Create Table fooTime (MyDate datetime) go Insert fooTime (MyDate) Values ('20060630 23:59:59.997') Insert fooTime (MyDate) Values ('20060701 00:00:00.000') Select * From fooTime Where MyDate Between '20060601 00:00:00.000' And '20060630 23:59:59.999' Select * From fooTime Where MyDate >= '20060601' And MyDate < '20060701' go Drop Table fooTime Tom Show quoteHide quote "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message news:4BB9191A-6814-4436-9015-BD01D43A5108@microsoft.com... > Got Ya. So its not defaulting the time to 23:59:59... > > You people are the BEST. > > Thanks! > > And sooooooooo prompt! > > "Tom Cooper" wrote: > >> In your case, it's because BETWEEN '06/01/2006' AND '06/30/2006' returns >> datetimes between '06/01/2006 00:00:00' AND '06/30/2006 00:00:00', so you >> miss any entry on 6/30 with a time > 00:00:00. The correct way to write >> this is >> WHERE CQTLOAN.LOAN_CREATED_DATE >= '200606/01' >> AND CQTLOAN.LOAN_CREATED_DATE < '20060701' >> >> >> Two possible reasons, >> MONTH(...) = 6 just checks for month of 6, so will return any row with a >> month of 6 from ANY yoer, for example 2003-06-02. >> >> '06/01/2006' can be an ambigious form for a date (for example, it's Jan >> 6, >> 1006 in most places other than the US), so you should enter your dates >> like >> yyyymmdd which will be interpeted the same on any server. >> >> Tom >> >> "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message >> news:1FFAFA4F-76F1-4987-BFCB-7ABAC8A4636E@microsoft.com... >> > Why would the following SQL return different rows??? >> > >> > SELECT CQTLOAN.LOAN_NUMBER, >> > CQTLOAN.LOAN_CREATED_DATE >> > FROM CQDS50.dbo.CQT_LOAN CQTLOAN >> > WHERE MONTH(CQTLOAN.LOAN_CREATED_DATE) = '06' >> > --WHERE CQTLOAN.LOAN_CREATED_DATE BETWEEN '06/01/2006' AND '06/30/2006' >> > ORDER BY CQTLOAN.LOAN_CREATED_DATE >> > >> > SELECT CQTLOAN.LOAN_NUMBER, >> > CQTLOAN.LOAN_CREATED_DATE >> > FROM CQDS50.dbo.CQT_LOAN CQTLOAN >> > --WHERE MONTH(CQTLOAN.LOAN_CREATED_DATE) = '06' >> > WHERE CQTLOAN.LOAN_CREATED_DATE BETWEEN '06/01/2006' AND '06/30/2006' >> > ORDER BY CQTLOAN.LOAN_CREATED_DATE >> > >> > >> > >> > These are the rows that I am NOT getting back with the between >> > statement... >> > 2500050030 2006-06-30 10:46:23.437 >> > 2500050031 2006-06-30 14:17:03.450 >> > 2500050032 2006-06-30 16:09:16.467 >> > >> > >> > The column CQTLOAN.LOAN_CREATED_DATE is defined as datetime. >> > >> > What am I missing here??? Is there some sort of limitation using >> > "BETWEEN"??? >> > >> > Any reply is GREATLY appreciated. >> > >> > Thanks! >> > >> > wnfisba >> > >> >> >>  
Other interesting topics
UNDO
XP_SENDMAIL Overload in SQL 2005 how to do this Small problem with NOT EXISTS... Problem with repeated use of temp tables How best to create a unique identifier across two or more tables... Win Server 2003 SP2 - SQL2k5 Performance Improvements Union with the results of two SP Linked Server Error: Login failed for user NT AUTHORITY\ANONYMOUS LOGON Statistic Problem |
|||||||||||||||||||||||
