Automating a time-sensitive query

My company uses MS Retail Management System, which is SQL-based. Without
going into a lot of detail about RMS, here's what I need to do: schedule a
query that will insert a new row into an existing table. Part of the data
that needs to be inserted is date/time. For example, here's the query I
need to run:
INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title, FromDate)
VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
What I don't know how to do:
Generate the date fields "Today 6:30PM" and "NOW"
Schedule the query.
I've got SQL 2000 (as part of SBS Premium), and I am certain that I have the
capability to do this. I think I have figured out the scheduling part, but
I'm hesitant to try it out on a live database. In fact, I think I'll try a
simpler query on a sample database before I ever go live with it.
Just to be perfectly clear, I'm a whole lot closer to being an SQL newbie
than an SQL expert.
Thanks for any advice you might offer,
Tom
--
Stop Fishing For e-MailThe function getdate() returns the current date/time, and jobs are the
standard method of scheduling a query, stored procedure, or package to
execute at a specific time or at intervals.
"Terrible Tom" <tomg@.gofish.robysfurniture.com> wrote in message
news:eHO$g55QGHA.1772@.TK2MSFTNGP14.phx.gbl...
> My company uses MS Retail Management System, which is SQL-based. Without
> going into a lot of detail about RMS, here's what I need to do: schedule
> a query that will insert a new row into an existing table. Part of the
> data that needs to be inserted is date/time. For example, here's the
> query I need to run:
> INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title,
> FromDate)
> VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
> What I don't know how to do:
> Generate the date fields "Today 6:30PM" and "NOW"
> Schedule the query.
> I've got SQL 2000 (as part of SBS Premium), and I am certain that I have
> the capability to do this. I think I have figured out the scheduling
> part, but I'm hesitant to try it out on a live database. In fact, I think
> I'll try a simpler query on a sample database before I ever go live with
> it.
> Just to be perfectly clear, I'm a whole lot closer to being an SQL newbie
> than an SQL expert.
> Thanks for any advice you might offer,
> Tom
> --
> Stop Fishing For e-Mail
>|||> INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title,
> FromDate)
> VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
> What I don't know how to do:
> Generate the date fields "Today 6:30PM" and "NOW"
> Schedule the query.
DECLARE @.eff SMALLDATETIME, @.from SMALLDATETIME;
SET @.from = CURRENT_TIMESTAMP;
SET @.eff = DATEADD(MINUTE, 30, DATEADD(HOUR, 18, DATEADD(DAY, 0,
DATEDIFF(DAY, 0, @.from))));
INSERT WorkSheet
(
Style,
EffectiveDate,
Status,
Notes,
Title,
FromDate
)
SELECT
250,
@.eff,
2,
'Auto 250',
'Automated Daily Item Update',
@.from;

> I'm hesitant to try it out on a live database. In fact, I think I'll try
> a simpler query on a sample database before I ever go live with it.
Never a bad idea. We test our code on three non-essential environments
before live clients ever hear about it.
A|||Use the ANSI/ISO Standard CURRENT_TIMESTAMP instead of NOW or the old
proprietary getdate().
.|||Why is it called a time "stamp" ?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141927581.261444.223900@.v46g2000cwv.googlegroups.com...
> Use the ANSI/ISO Standard CURRENT_TIMESTAMP instead of NOW or the old
> proprietary getdate().
> .
>