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 |
|
GS1
Starting Member
27 Posts |
 Posted - 2003-01-14 : 08:52:25
|
| HiI have a report that runs and uses a query that selects the data that exists in a table based on the value of a date field in that table. It selects all data from the first day of the previous 2 months onwards.I have used AND ((DatePart("m",[Activities].[Date]))>DatePart("m",Now())-2) AND ((DatePart("yyyy",[Activities].[Date]))=DatePart("yyyy",Now())))to do this in the past. However, it being January, I have now noticed that the code 'falls over' because it is looking for records dated after 1/12/03!Anyone have any bright ideas on a better way of doing this?Thanks! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-14 : 09:06:37
|
| Use DateAdd() to calculate previous date values:AND Activities.Date>=DateAdd("d", 1-Day(Date()), DateAdd("m", -2, Date()))Something like that should work. It will subtract 2 months from today's date, then subtract the number of days (giving the last day of the previous month), then add one day to give the first day of the month. |
 |
|
|
GS1
Starting Member
27 Posts |
Posted - 2003-01-14 : 09:19:05
|
| ZOIKSS!Fast and a perfect answer too!Thanks very much!:D :D :D |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-14 : 09:20:33
|
I, uh, have had similar experience with this problem.  |
|
|
|
|
|
|
|
|
PANIC -bug in sql statement needs urgent attention