sql agent

[tleper]

hi.I have a sql2000 database.I have orders in a table with orderdate and ordertime.I want to force database to delete any order if person didnt pay for it after 3 days.I think I can check that with sqlAgent.but I dont know how.I checked most sites but couldnt find any sources.any help would be appreciated.

[RathE]

Well, the first step is finding the difference in days, so assuming orderdate is the date the order was placed and ordertime is the payment due day,

datediff(dd,orderdate,ordertime) > 3

Anything greater than 3 will be overdue. The next step is to create a job(in sql agent as you said) which either runs hourly or daily on close of business hours,depending on how strict you plan to be with the timing (daily gives the customer a few hours more than 3 days in general)
the script for this would look something like

delete
from orders
where datediff(dd,orderdate,ordertime) > 3

hope that helps

[tleper]

thanks RathE for help.in my database orderdate's data type is char like 09.09.2004 which is dd/mm/yyyy.I tried this and error is "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." is there anyway to handle that?

[RathE]

Thats not a problem, are both fields char or varchar?

if they are both of a char type then use

datediff(dd,convert(datetime,orderdate,104),
convert(datetime,ordertime,104)) > 3

If you select convert and hit [Shift] + [F1] it will bring up the different datetime conversions possible

Edit: formatting looks horrid, just tried to tidy it

[tleper]

yes.both two colums are char.I tried this but I get same error.I also tried cast(orderdate as datetime) and didnt work.this f..k.n columns are char(20).could this be the problem?or Im gonna delete whole table.

[RathE]

ahh, if you can alter the table to varchar instead if char it will help you endlessly (easiest way to do this is in Enterprize manager). Bcause it is set to char(20), it is trying convert the trailing spaces as well(char claims all 20 characters while varchar only fills the space needed). If you cant change the table, then a messy way around it is to convert the date to varchar first.

datediff(dd,convert(datetime,convert
(varchar,orderdate,104),104),
convert(datetime,convert
(varchar,ordertime,104),104)) > 3

Edit: cleaning again

[tleper]

nope.didnt work.same error.but I tried this: print
datediff(dd,'08.09.2004','08.10.2004')
and it gave:1.
so I tried to give my orderdate in mm.dd.yyyy format by using this:
select * from Orders where
datediff(dd,substring(OrderDate,4,2)+'.'+substring (OrderDate,1,2)+'.'+substring(OrderDate,7,4),'09.0 9.2004')>3
and this time it says
406 rows affected but then I get this error:
Incorrect syntax near ')'.
I think the output is something like this which causes error:
select * from Orders where
datediff(dd,09.04.2004,'09.09.2004')>3.
I tried to convert 09.04.2004 to char but didnt work.how can I put 09.04.2004 between those things '09.04.2004 ' ?

[tleper]

oops.my mistake.this works well.problem was some wrong entries in database.thanks for help.

no edit:

[RathE]

cool, a quick way to test wherether something is a date or not is to use

isdate(field) = 0

to find the problem rows

[tleper]

Im not experienced so hope I dont bother you but I have another question.how can I check if date is valid in select query.I mean I dont know the syntax.is there an if control or any other thing.
select * from Orders where
datediff(#the code#)
where should I put the control?a sample would be very helpful.thanks

[rmiao]

You can find details of datediff in sql books online.

[RathE]

Thats what the "isdate" function does, although you may need to do the convert to varchar first before it is accurate.
so you would be looking for:

select *
from orders
where datediff(blah)
and isdate(blah) = 1