SQL Studio Express - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Studio Express

Author

Topic

stuarta99
Starting Member

11 Posts

Posted - 2008-10-14 : 07:50:17

I have a situation where I have my ISA server spooling data out to a SQL Express database.

At the beginning of everymonth, I want to be able to delete all the previous months.

At the moment I've used the following SQL queries

DELETE FROM WebProxyLog
WHERE (logtime <'30/09/2008 23:58:53')

and

DELETE FROM WebProxyLog
WHERE (logtime BETWEEN '09/09/2008 12:48:09 AND '30/09/2008 23:58:53')

Although some got deleted, I'm now getting an error

SQL Execution Error.
Executed SQL statement: DELETE FROM WebProxyLog where (logtime BETWEEN ‘09/09/2008 12:48:09’ AND ‘30/09/2008 23:58:53’)
Error Source: .Net SqlClient Data Provider
Error Message: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

Can anyone help please?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 07:54:36

try this and see if it works
set dateformat dmy

DELETE FROM WebProxyLog where (logtime BETWEEN ‘09/09/2008 12:48:09’ AND ‘30/09/2008 23:58:53’

stuarta99
Starting Member

11 Posts

Posted - 2008-10-14 : 08:01:52

right ok, just entered in

DELETE FROM WebproxyLog
WHERE (logtime BETWEEN '09/09/2008 12:48:41' AND '24/09/2008 15:44:26')

but get the same.

The first and second date/times are valid and showing when I open the table

stuarta99
Starting Member

11 Posts

Posted - 2008-10-14 : 08:10:18

actually sorry yes that works.

I forgot the set dateformat dmy

thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 08:54:40

quote:
Originally posted by stuarta99

At the beginning of everymonth, I want to be able to delete all the previous months.

DELETE
FROM	WebproxyLog
WHERE	logTime < DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')

E 12°55'05.63"
N 56°04'39.26"

stuarta99
Starting Member

11 Posts

Posted - 2008-10-16 : 04:53:59

Sorry, just expanding onto this.

How could I do a select query that selects all numbers from my desthost column?

I have a load of IP addresses in there that I want to get rid of. The other option is to delete everything that doesn't equal www.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 05:08:25

quote:
Originally posted by stuarta99

Sorry, just expanding onto this.

How could I do a select query that selects all numbers from my desthost column?

I have a load of IP addresses in there that I want to get rid of. The other option is to delete everything that doesn't equal www.

Thanks

do you mean only records with numeric values in desthost?

stuarta99
Starting Member

11 Posts

Posted - 2008-10-16 : 05:09:55

yep effectively.

That would be a start, but then I might also want to delete more records for anything that doesn't start with www but I'm guessing that could be harder.

stuarta99
Starting Member

11 Posts

Posted - 2008-10-23 : 10:24:43

Sorry just wandering if there was any update on this yet?

Thanks guys

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-23 : 10:26:39

Sample data?
Table layout?
Column names?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 10:32:28

SELECT * FROM WebProxyLog where dbo.Is_Numeric(col) = 1

Is_Numeric can be found here

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

stuarta99
Starting Member

11 Posts

Posted - 2008-10-23 : 10:39:53

ok thanks, just tried this but came back as error

Select * FROM WebProxyLog WHERE (dbo.Is_Numeric(DestHost) =1)

but it came back with an error

I want to remove everything that doesn't start with www from the column DestHost.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 10:42:06

quote:
Originally posted by stuarta99

ok thanks, just tried this but came back as error

Select * FROM WebProxyLog WHERE (dbo.Is_Numeric(DestHost) =1)

but it came back with an error

I want to remove everything that doesn't start with www from the column DestHost.

thanks

for that just use

Select * FROM WebProxyLog WHERE DestHost NOT LIKE 'www%'

stuarta99
Starting Member

11 Posts

Posted - 2008-10-23 : 10:46:39

excellent thank you. turned that into a delete and got rid of 400271 rows

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 10:50:51

quote:
Originally posted by stuarta99

excellent thank you. turned that into a delete and got rid of 400271 rows

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources