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 |
cms9651
Starting Member
28 Posts |
Posted - 2013-03-05 : 04:19:27
|
Extract only the last three dayshi all, hope in your help.this is my procedure for export in txt file the values of the table in db sql server 2008.the table is on a remote server and the field [myDateString] is nvarchar 255 and I'm not admin.I need extract only the last three days for the table, in this moment extract all current year.Can you help me ?thank you EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE EXEC xp_cmdshell 'bcp "SELECT * FROM tbl WHERE YEAR(CAST(SUBSTRING([myDateString], 7, 4) AS DATETIME)) = 2013;" queryout "\\myserver\public\tkt.txt" -T -c -t;' |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-05 : 04:35:02
|
make it likeEXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE EXEC xp_cmdshell 'bcp "SELECT * FROM tbl WHERE CAST([myDateString] AS datetime)> = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-3) AND CAST([myDateString] AS datetime) < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1) ;" queryout "\\myserver\public\tkt.txt" -T -c -t;' I hope your [myDateString] field is having values in valid date format------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
cms9651
Starting Member
28 Posts |
Posted - 2013-03-05 : 04:49:36
|
thank you for help.your query not working... the output is nullthe values of [myDateString] is 31/12/2012 09:01SELECT *FROM tblWHERE ( CAST ( SUBSTRING ([myDateString], 7, 4) AS DATETIME ) > = DATEADD( dd, DATEDIFF(dd, 0, GETDATE()) ,- 3 ) AND CAST ( SUBSTRING ([myDateString], 7, 4) AS datetime ) < DATEADD( dd, DATEDIFF(dd, 0, GETDATE()), 1 )); |
|
|
cms9651
Starting Member
28 Posts |
Posted - 2013-03-05 : 05:11:59
|
If tried:[SQL] SELECT *FROM tblWHERE ( CAST ( [myDateString] AS DATETIME ) > = DATEADD( dd, DATEDIFF(dd, 0, GETDATE()) ,- 3 ) AND CAST ( [myDateString] AS datetime ) < DATEADD( dd, DATEDIFF(dd, 0, GETDATE()), 1 ))[Err] 22007 - [SQL Server]Conversion failed when converting date and/or time from character string. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-05 : 05:13:14
|
use it as suggested. I've not applied any SUBSTRING . copy and paste it as given------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
cms9651
Starting Member
28 Posts |
Posted - 2013-03-05 : 05:17:49
|
Ok, but:[SQL] SELECT *FROM tblWHERE ( CAST ( [myDateString] AS DATETIME ) > = DATEADD( dd, DATEDIFF(dd, 0, GETDATE()) ,- 3 ) AND CAST ( [myDateString] AS datetime ) < DATEADD( dd, DATEDIFF(dd, 0, GETDATE()), 1 ))[Err] 22007 - [SQL Server]Conversion failed when converting date and/or time from character string. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-05 : 05:18:25
|
quote: Originally posted by cms9651 If tried:[SQL] SELECT *FROM tblWHERE ( CAST ( [myDateString] AS DATETIME ) > = DATEADD( dd, DATEDIFF(dd, 0, GETDATE()) ,- 3 ) AND CAST ( [myDateString] AS datetime ) < DATEADD( dd, DATEDIFF(dd, 0, GETDATE()), 1 ))[Err] 22007 - [SQL Server]Conversion failed when converting date and/or time from character string.
Thats the problem with not using proper datatype for fields.make it like this and tryEXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE EXEC xp_cmdshell 'bcp "SELECT * FROM tbl WHERE CONVERT(datetime,[myDateString],103)> = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-3) AND CONVERT(datetime,[myDateString],103) < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1) ;" queryout "\\myserver\public\tkt.txt" -T -c -t;' also make sure you read thishttp://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
cms9651
Starting Member
28 Posts |
Posted - 2013-03-05 : 05:42:40
|
thanks a lot! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-05 : 06:06:44
|
wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|