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 |
|
Johnf62
Starting Member
7 Posts |
 Posted - 2010-10-13 : 03:52:51
|
| Hello,Please can you help with the following query. I need to return the records for the last 7 days but I have no idea where to put the convert command so that the query loses the time element and just returns results on the day part of the date. The query I have isWHERE (Instruction.CreatedDate >= GETDATE() - 7)Any help would be greatly appreciated.thanksJohnJohn Frederick |
|
|
kunal.mehta
Yak Posting Veteran
83 Posts |
Posted - 2010-10-13 : 04:01:32
|
| WHERE (convert(date,Instruction.CreatedDate) >= GETDATE() - 7) |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-13 : 04:03:22
|
Try:WHERE (Instruction.CreatedDate >= dateadd(d,-7,dateadd(d,datediff(d,0,getdate()),0)))
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-13 : 04:06:13
|
quote:
Originally posted by kunal.mehta
WHERE (convert(date,Instruction.CreatedDate) >= GETDATE() - 7)
Datatype DATE isn't present in 2005.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Johnf62
Starting Member
7 Posts |
Posted - 2010-10-13 : 04:39:53
|
| Thanks Webfred this works perfectly.John Frederick |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-13 : 06:03:09
|
welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-10-13 : 06:19:26
|
quote:
Originally posted by webfred
quote:
Originally posted by kunal.mehta
WHERE (convert(date,Instruction.CreatedDate) >= GETDATE() - 7)
Datatype DATE isn't present in 2005.
No, you're never too old to Yak'n'Roll if you're too young to die.
And, wrapping a column in a function is a BAD PLAN (tm) If you do then you are unable to use any index on that column. This is why Webred's approach is better even on 2008.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Date query