| Author |
Topic |
|
stylishjm
Starting Member
17 Posts |
 Posted - 2012-02-09 : 04:56:37
|
Hello, I'm attempting to create a batch file that exports all columns from a table from the past 7 days using SQLCMD.I've got the exporting process working, but have no idea how to get it to do the past 7 days.sqlcmd -UUsername -PPassword -S SERVER\QSR01 -d ePic -Q "select * from User.SpeedOfService" -o "R:\export\DB.csv" -h-1 -s"," -w 700 This batch file will be scheduled to run automatically every week, so I need a way for it to draw the last 7 days without having to manually input the date range manually. I need similar functionality to Crystal Reports "Last7Days" basically.Can anyone help please? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-02-09 : 05:04:43
|
The table structure of "User.SpeedOfService"? Is there a datetime column? do you have example data?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
stylishjm
Starting Member
17 Posts |
Posted - 2012-02-09 : 05:32:12
|
| Yes there is a datetime column, called TimeStamp.Here is some example data:100,2011-11-11 12:09:02.000,2012,1,NULL,2,1,EAT IN,0,100,SVR |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-02-09 : 05:42:11
|
Try this select:select * from User.SpeedOfServicewhere [TimeStamp] >= dateadd(day,-7,dateadd(day,datediff(day,0,[TimeStamp]),0))
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
stylishjm
Starting Member
17 Posts |
Posted - 2012-02-09 : 05:59:52
|
| Thank you, will try that out now. |
|
|
|
stylishjm
Starting Member
17 Posts |
Posted - 2012-02-09 : 06:13:07
|
| Hi, unfortunately it didn't work.sqlcmd -UUser -PPassword -S SERVER\QSR01 -d ePic -Q "select * from QsrUser.SpeedOfService where [TimeStamp] >= dateadd(day,-7,dateadd(day,datediff(day,0,[TimeStamp]),0))" -o "C:\test.csv" -h-1 -s"," -w 700It created a 132mb CSV containing all data!SERVER\QSR01\Databases\ePic\Tables\QsrUser.SpeedOfService\Columns\Timestamp (datetime, not null) |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-02-09 : 06:18:49
|
Argh! My bad, sorry.where [TimeStamp] >= dateadd(day,-7,dateadd(day,datediff(day,0,[TimeStamp]getdate()),0))
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
stylishjm
Starting Member
17 Posts |
Posted - 2012-02-09 : 06:22:47
|
| Just used this:sqlcmd -UUser -PPassword -S SERVER\QSR01 -d ePic -Q "select * from QsrUser.SpeedOfService where TimeStamp >= DATEADD(day, -7, GETDATE())" -o "C:\test.csv" -h-1 -s"," -w 700And it worked!Thank you! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-02-09 : 06:30:24
|
But that is including the time part when calculating -7 days, if that is ok for you then it is fine else use my solution where I am setting the time part to zeroes.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
Using SQLCMD to export data from the past x days