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 |
|
newbert
Starting Member
1 Post |
 Posted - 2007-06-14 : 03:15:19
|
| i'm trying to select * from users where mydate=getdate()but I want to select all users where the date is today but not by timewhat's the best way to do this? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-14 : 03:17:56
|
| [code]select * from users where mydate >= DATEADD(Day, 0, DATEDIFF(Day, 0, getdate())) AND mydate < DATEADD(Day, 0, DATEDIFF(Day, 0, getdate()+1)[/code]Sorry its so convoluted!Kristen |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-06-14 : 03:18:08
|
[code] where mydate = dateadd(day, datediff(day, 0, getdate()), 0)[/code]
KH |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-06-14 : 03:19:05
|
KH |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-14 : 03:20:46
|
| Note: This method is regarded as performing well, but is hard to understand read.You could do:WHERE DATEDIFF(Day, mydate, GetDate()) = 0but that would not use any index etc. that you might happen to have on [mydate]Sadly SQL Server does not, yet, have a Date datatype, only a composite DateTime. So you have to take anything from, and including, midnight-last-night up to, and NOT including, midnight tonight.Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-14 : 03:21:52
|
| Ah! If your [mydate] column stores ONLY a date, with NO time (well, actually a time of 00:00:00.000) then you can use khtan's equals-method.Kristen |
|
|
|
melody_ph
Starting Member
11 Posts |
Posted - 2007-06-14 : 04:26:17
|
| is there a difference if newbert will use the ff?WHERE CONVERT(VARCHAR(20),MYDATE,101) = CONVERT(VARCHAR(20),GETDATE(),101) |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-06-14 : 04:33:19
|
Performance. This will discourage the use of index on column mydate. As applying convert() on column mydate will result the table scan.
KH |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-14 : 09:12:21
|
quote:
Originally posted by khtan
where mydate = dateadd(day, datediff(day, 0, getdate()), 0)
KH
Isnt it the following? where dateadd(day, datediff(day, 0, mydate), 0)= dateadd(day, datediff(day, 0, getdate()), 0) MadhivananFailing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-14 : 09:13:49
|
quote:
Originally posted by melody_ph
is there a difference if newbert will use the ff?WHERE CONVERT(VARCHAR(20),MYDATE,101) = CONVERT(VARCHAR(20),GETDATE(),101)
www.sql-server-performance.com/fk_datetime.aspMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-14 : 09:17:55
|
| From my tests (some time ago)! times for the follow (in seconds) were: 6.813 SELECT @TempDate = MyDate FROM MyTable 6.623 SELECT @TempDate = DATEADD(day, DATEDIFF(day, 0, MyDate), 0) FROM MyTable 37.203 SELECT @TempString = CONVERT(varchar(8), MyDate, 101) FROM MyTableand that's not including the effect on a WHERE clause if MyDate was indexed - using a function will bypass any Index (as khtan says) and then you will be doing a table-scan instead of an index-seek; that's REALLY bad news!Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-14 : 09:31:01
|
Test, Thanks for the test  MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
date=getdate