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 |
|
1821
Starting Member
18 Posts |
 Posted - 2008-05-07 : 13:34:37
|
| Hey guys,I am trying to design a query that will return all the records based on a single date but because of the date/time data type in MS SQL 2005 it defaults to the date and the time 00:00:00 which will return no records for me.Is it possible to ignore the time part in the select statement?Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-07 : 13:38:19
|
| SELECT * FROM YourTable WHERE DateCol > @YourDate AND DateCol < DATEADD(d,1,@YourDate)where @YourDate is variable holding the date part value alone. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-07 : 13:39:51
|
| Yes via:DATEADD(Day, DATEDIFF(Day, 0, DateTimeColumnGoesHere), 0)Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-05-07 : 14:13:46
|
| [code]declare @date datetimeset @date = '20080513'Select *from NyTableWhere MyDateColumn >= @date and MyDateColumn < dateadd(dd,1,@date)[/code]Notice that you are asking for greater than or equal to the beginning of the date, and less than the following date. You can apply the same general query for any range of days. This is almost always the best way to write a query of this type, because it allows SQL Server to use any index that exists on the datetime column, and it uses less resources than a query that applies a function to the datetime column.There is more info on datetime queries on the link below:Date/Time Info and Script Linkshttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762CODO ERGO SUM |
|
|
|
|
|
|
|
|
select date/time issue