Syntax error converting datetime from character st

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

Syntax error converting datetime from character st

Author

Topic

mdeligny
Starting Member

10 Posts

Posted - 2007-06-04 : 04:05:51

Probably one of the easiest queries some have seen around, but good ol' MS and their dates. I can't get around this error on this simple query. It will be a job that will run probably several times a day. I haven't even gotten passed to the second part of it....I've also seen this error is a VERY popular error, even with advanced programmers it seems. WOW. Basically, I want to keep the row count to 2 days. Well, if you have any suggestions, I'd take 'em. Thanks!

Delete from tblCallIndexes where inum in
(select inum from tblCalls where StartedAt < 'DATEADD(d,-2,GETDATE())')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 04:07:58

Delete i
from tblCallIndexes as i
inner join tblCalls as t on t.inum = i.inum
where t.StartedAt < DATEADD(d,-2,GETDATE())

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-04 : 06:17:54

or
Remove single quote

Delete from tblCallIndexes where inum in
(select inum from tblCalls where StartedAt < DATEADD(d,-2,GETDATE()))

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-04 : 06:21:23

Also note that GETDATE() includes time also

See which one you need

select columns from tblCallIndexes where inum in
(select inum from tblCalls where StartedAt < DATEADD(d,-2,GETDATE()))

select columns from tblCallIndexes where inum in
(select inum from tblCalls where StartedAt < DATEADD(day,datediff(day,0,GETDATE()),-2))

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 06:25:23

Madhi, the job will be run several times a day according to OP, so I don't think the time part will be an issue.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-04 : 08:24:15

quote:
Originally posted by Peso

Madhi, the job will be run several times a day according to OP, so I don't think the time part will be an issue.

Peter Larsson
Helsingborg, Sweden

Then the time portion doesnt matter.
Thanks

Madhivanan

Failing to plan is Planning to fail

mdeligny
Starting Member

10 Posts

Posted - 2007-06-06 : 02:14:37

quote:
Originally posted by madhivanan

quote:
Originally posted by Peso

Madhi, the job will be run several times a day according to OP, so I don't think the time part will be an issue.

Peter Larsson
Helsingborg, Sweden

Then the time portion doesnt matter.
Thanks

Madhivanan

Failing to plan is Planning to fail

Thanks people. I'll give these few suggestions a try and see what happens. I have a backup to mess around with. Thanks, I'll let ya know how it goes.

mdeligny
Starting Member

10 Posts

Posted - 2007-06-06 : 04:10:12

quote:
Originally posted by Peso

Delete i
from tblCallIndexes as i
inner join tblCalls as t on t.inum = i.inum
where t.StartedAt < DATEADD(d,-2,GETDATE())

Peter Larsson
Helsingborg, Sweden

I just tried this so far and it deleted all the records in TblCalls. There was still suppose to be 2 days (48 hours) available. These crazy Date functions could make you go nuts. According to my friend he said that "-2" parameter is what should make it work amd keep the 2 days worth of data.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-06-06 : 08:09:30

instead of running the delete statement, you should change it to a simple select first, to see just what will be deleted.

Hind sight is 20/20

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-06 : 09:46:50

quote:
Originally posted by DonAtWork

instead of running the delete statement, you should change it to a simple select first, to see just what will be deleted.
Hind sight is 20/20

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Yes. Thats why I showed him/her the select statement

Madhivanan

Failing to plan is Planning to fail

mdeligny
Starting Member

10 Posts

Posted - 2007-06-08 : 08:56:32

quote:
Originally posted by madhivanan

Also note that GETDATE() includes time also

See which one you need

select columns from tblCallIndexes where inum in
(select inum from tblCalls where StartedAt < DATEADD(d,-2,GETDATE()))

select columns from tblCallIndexes where inum in
(select inum from tblCalls where StartedAt < DATEADD(day,datediff(day,0,GETDATE()),-2))

Madhivanan

Failing to plan is Planning to fail

It wouldn't cause any issues if the time isn't thrown in, would it? Just using the date is alright?

This is the query I've been running manually for a while, just changing the dates...

Delete from tblCallIndexes where inum in (select inum from tblCalls where StartedAt < '2007-06-6 9:00')

then I would do this real simple one.....

Delete from tblCalls where StartedAt < '2007-06-6 9:00'

So, that's the deal. It selects the date range of where the inum in one table to another, and then just simply delete the records in the second table. It works...because it leaves exactly 2 days (48 hours) of data...but I have to keep running it manually.

I appreciate the examples, and I want to try them out over the weekend. I have used the date AND time in the manual queries. The TblIndexes table isn't really used, but it's hard coded into the software and we just need to keep trimming it down, that's all.
Thanks!

mdeligny
Starting Member

10 Posts

Posted - 2007-06-08 : 09:01:24

quote:
Originally posted by DonAtWork

instead of running the delete statement, you should change it to a simple select first, to see just what will be deleted.
Hind sight is 20/20

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

I haven't been just messing up a production database, but using a VM with all that I need to make it right. There is no doubt I'm trying to skip over alot of the basics I would need to know, but given that my work wants me to try and get this done asap, I'm just putting it on the fast track now, then go back and actually teach myself what it does....and how it does it.

Thank you...

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-06-08 : 10:17:25

Doing it on VM first is a VERY good idea. Now just marry that with selecting instead of deleting, and you have a winner

Trying to skip over the basics because it needs to be done ASAP is a contradiction. Fast != right. Not right != fast.

Best of luck to you.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

mdeligny
Starting Member

10 Posts

Posted - 2007-06-10 : 14:53:54

quote:
Originally posted by DonAtWork

Doing it on VM first is a VERY good idea. Now just marry that with selecting instead of deleting, and you have a winner

Trying to skip over the basics because it needs to be done ASAP is a contradiction. Fast != right. Not right != fast.

Best of luck to you.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Hello,

Ultimately I did get the small two little queries working. Runs twice a day keeping the DB always only at 48 hours of data only. Something to do with our legal department. Anything older then 48 days, has to go. Not archived, no saved, not backed up...but deleted. Not %100 sure this was the right place, but I put them in an area called "JOBS"....that kind of brought be through the steps then the scheduling at the end. These DB just hold pointers to real data that's some where else. Just rows and rows and rows of information about particular pieces of data stored on other machines. One of our main DB files is over 2.5gb. Is that common for a DB file to be that size or is that normal these days? Well, thanks for the help and I'm learning little by little when I have the time. I'm afraid to upgrade from SQL 2000 Server to a newer one. Not sure what that involves....but my friend said many companies and people still use SQL 2000 Server A LOT. Whatever....no big deal to me.

Thanks again,

Michael

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-06-11 : 07:48:37

2.5 Gigs is a tiny database. SQL SERVER can handle a much, MUCH larger one. Glad everything has worked out for you.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources