Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
john.lam
Starting Member
12 Posts |
 Posted - 2010-01-30 : 08:04:51
|
| Hi all,I have two datetime fields in the table: HireDateFrom and HireDateTo and there are some records where the HireDateFrom and HireDateTo of these records fall into the range of 16/01/2010 and 26/01/2010.I try to search the records with:WHERE HireDateFrom >= 16/01/2010 AND HireDateTo <= 20/01/2010But which returns nothing. Anyone knows where is the problem?ThanksJohn Lam |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-01-30 : 08:12:56
|
specify the date in as a string. WHERE HireDateFrom >= '16/01/2010' AND HireDateTo <= '20/01/2010' Preferably in ISO format YYYYMMDDWHERE HireDateFrom >= '20100116' AND HireDateTo <= '20100120'
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-30 : 08:13:54
|
Perhaps one of these two? 1st one uses unambiguous date in case SQL is casting yours wrongly (but you probably would have got an error, rather than "no rows")WHERE HireDateFrom >= '20100116' AND HireDateTo <= '20100120'WHERE (HireDateFrom >= '20100116' AND HireDateFrom <= '20100120') OR (HireDateTo >= '20100116' AND HireDateTo <= '20100120') |
|
|
|
john.lam
Starting Member
12 Posts |
Posted - 2010-01-30 : 08:22:15
|
quote:
Originally posted by Kristen
Perhaps one of these two? 1st one uses unambiguous date in case SQL is casting yours wrongly (but you probably would have got an error, rather than "no rows")WHERE HireDateFrom >= '20100116' AND HireDateTo <= '20100120'WHERE (HireDateFrom >= '20100116' AND HireDateFrom <= '20100120') OR (HireDateTo >= '20100116' AND HireDateTo <= '20100120')
It acts a bit strange... There are some records with 16/01/2010 for HireDateFrom and 20/01/2010 for HireDateTo. If I use the format WHERE HireDateFrom >= '20100116' AND HireDateTo <= '20100116', which returns nothing. If I change it to WHERE HireDateFrom >= '20100116' AND HireDateTo <= '20100117', which still returns nothing and not until WHERE HireDateFrom >= '20100116' AND HireDateTo <= '20100120'......John Lam |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-30 : 08:58:16
|
If you have records with a date of 20/01/2010 which also INCLUDE a Time then they will NOT be included in:MyDate = '20100120' the traditional way to handle this is to use MyDate >= '20100120'AND MyDate < '20100121' instead. So:WHERE HireDateFrom >= '20100116' AND HireDateTo <= '20100116' will only find HireDateFrom that has NO time (No time = midnight = 00:00:00) |
|
|
|
|
|
|
DateTime Comparison