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 |
|
aperson
Starting Member
3 Posts |
 Posted - 2009-09-01 : 14:24:24
|
| Hello,Before I begin, I'd like to thank any of you for advice. I'm new to SQL and I'm not actually running a server. I work for a restaurant Franchise that has databases coded in VisualFox Pro. Navicat 8 for mySQL allows editing of .dbf databases. I've imported data into tables and connected to an SQL server hosted locally. I have no problems having data pull up and I can apply filters. To summarize, I need to (every monday) pull up this table (sfil), filter out to only show records from that date to a week before (1 business week for accounting purposes).Right now, my code works by manually editing the date weekly. I'd like to automate the process, but I have to take care of this variable field. Code:WHEREslfil.S_DATE >= '2009-08-24' ANDslfil.E_DATE <= '2009-08-30'S_DATE is the starting date column, E_DATE is ending date. What I can't figure out how to do is to make the S_DATE be today's date minus 7 days and the E_DATE as "today's" date.Any help or advice would be greatly appreciated. I've searched around and my lack of experience is probably hindering me from deriving a solution from similar posts.Thank you!AP |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-09-01 : 14:34:19
|
| [code]WHERE datediff(dd,slfil.S_DATE,getdate()) <= 7[/code] |
 |
|
|
aperson
Starting Member
3 Posts |
Posted - 2009-09-01 : 14:39:07
|
| Hey vijay, Thanks for the advice! Once again, please excuse my inexperience. When I copied the code over, I get error:[Err] 1582 - Incorrect parameter count in the call to native function 'datediff'I'm assuming it's probably something I did wrong, just can't figure it out.EDIT: Ok, so I'm gessing it's because the date ranges in my document show up as 2009-08-24 and the getdate function is showing something difference. Can I program getdate to output the current date as 2009-08-24 (year-month-day)? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-09-02 : 05:17:26
|
try likeWHEREslfil.S_DATE >= DATE_ADD(Now(),INTERVAL '-7' DAY) ANDslfil.E_DATE <= Now() |
|
|
|
aperson
Starting Member
3 Posts |
Posted - 2009-09-02 : 11:00:50
|
| Visak, You are my savior. If I could hug you, I would. I made a small change so the code is:WHEREslfil.S_DATE >= DATE_ADD(Now(),INTERVAL '-8' DAY) ANDslfil.E_DATE < Now()And that pulls the exact date range that I need. So if I set scheduled queries to run on, say, monday...it will pull data from the day before (sunday), saturday, friday...etc all the way to the monday before. thanks again! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-09-02 : 11:02:30
|
welcome  |
|
|
|
|
|
|
|
|
Queries, Last week to today's date?