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 |
|
rudba
Constraint Violating Yak Guru
415 Posts |
 Posted - 2009-03-18 : 11:19:22
|
| how to use between for datetime field.i have data like this:2007-01-25 00:00:00.000I am using SQL Server 2005, and my field is datetimeI tried this but it does not work.select * from tbl1 where TranDate between '03/06/2009' and '03/06/2009' |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-18 : 11:35:17
|
Don't use BETWEEN for datetime ranges.A selection for a datetime range should be coded as shown below.select *from tbl1where -- Greater than or equal to start datetime TranDate >= '20090306' and -- Less than end datetime TranDate < '20090307' CODO ERGO SUM |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-03-18 : 11:54:42
|
quote:
Don't use BETWEEN for datetime ranges.
Why not? (you may be asking)Because using the >= < syntax your query can hit any INDEX(s) on the columns and because BETWEEN is an inclusive operator. You will often get 1 more day than you wanted to return with it.Sorry -- just a clarification of why not.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-18 : 13:28:21
|
quote:
Originally posted by Transact Charlie..Why not? (you may be asking)...
Why not? Because I said so!  CODO ERGO SUM |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-03-19 : 06:05:47
|
| Well obviously we should obey the orders of the 'Colonel - That's a given.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
how to use between in datetime field?