SQL Enterprise not updating table

[Joell]

Hi Guys,

I have the following code in the Enterprise Job Scheduler and it runs successfully but the table is not updated. I first put it into a stored procedure but when they did not work, I put just the code to insert the records directly into the scheduler. Its supposed to update nightly at 5am, but when I run my crystal report the next day, nothing in the table was updated. Please help!

This is SQL script. I do not know any other coding practices. - JO

insert into NewCustomers
select cust.c_id_alpha Location_ID, cust.c_name Location_name,
substring (CUST.c_name, CHARINDEX('#', CUST.c_name)+1, ((len(CUST.c_name) - (len(CUST.c_Name) - CHARINDEX('#', CUST.c_name))))) Store_no,
C_ADDRNUM1, C_ADDR1, C_ADDR2,
C_CITY, C_STATE, C_ZIP, C_PHO, C_FAX Cust_Fax_No, c_pcont Cust_Contact, null, cmpy.company
FROM cust inner join udef_active_cstat ud on cust.c_cstat = ud.unique_id
left join dbo.ups_keepers k on cust.c_id = k.c_id
inner join cmpy on cust.b_bill_co = cmpy.cmpy_id
where right(cust.c_id_alpha, 3) <> '001'
and b_bill_co not in (30,2,26,29,4)
and c_quote >= getdate()-1

update NewCustomers
set Standard_Instructions = rxrf.comment
FROM cust inner join udef_active_cstat ud on cust.c_cstat = ud.unique_id
left join dbo.ups_keepers k on cust.c_id = k.c_id
inner join rxrf on cust.c_id = rxrf.c_id
inner join cmpy on cust.b_bill_co = cmpy.cmpy_id
inner join NewCustomers L on L.Location_id = cust.c_id_alpha
where right(cust.c_id_alpha, 3) <> '001'
and b_bill_co not in (30,2,26,29,4)
and c_quote >= getdate()-1

update NewCustomers set c_pho = replace(c_pho, '-', '')
update NewCustomers set c_pho = replace(c_pho, ')', '')
update NewCustomers set c_pho = replace(c_pho, '(', '')
update NewCustomers set c_pho = replace(c_pho, ' ', '')
update NewCustomers set c_zip = replace(c_zip, '-', '')
update NewCustomers set cust_FAX_no = replace(cust_fax_no, '-', '')

[davef]

If you're getting no error, one can assume that your update query affects no rows because no matches are found. Check whether your select query returns any rows first.

[Joell]

ok its the getdate() function. I am trying to pull at 5am the previous day's new customers and add them to this table. When I put in yesterdays date, I get them when I put in getdate() -1 I dont? Am I not using the correct date function?

getdate() - 1 <--- is this not correct?

[davef]

ok its the getdate() function. I am trying to pull at 5am the previous day's new customers and add them to this table. When I put in yesterdays date, I get them when I put in getdate() -1 I dont? Am I not using the correct date function?

getdate() - 1 <--- is this not correct?

The time on the server might be set to GMT hence the offset. Make sure you accomodate for it.

[Joell]

That would only 1 hour. We dont have people entering data until after 8am in the morning. Actually we only have one person adding in new customers. Could it be something else?

[davef]

That would only 1 hour. We dont have people entering data until after 8am in the morning. Actually we only have one person adding in new customers. Could it be something else?

Print getdate()-1 on the server and place it in the query. See if it fetches the dataset.

[Joell]

the clock on that server is the exact time. It is correct. But, my query is still not pulling early the morning the day before data. I dont understand it. If I pull it now with an actual date it works fine.

[Joell]

The is what I mean:
Select c_id_alpha, c_name, c_quote, getdate() from cust where c_quote >= '09/14/2007'

returns 9 rows

Select c_id_alpha, c_name, c_quote, getdate() from cust where c_quote >= getdate()

returns 0 rows

how can this be?

[ck9663]

The is what I mean:
Select c_id_alpha, c_name, c_quote, getdate() from cust where c_quote >= '09/14/2007'

returns 9 rows

Select c_id_alpha, c_name, c_quote, getdate() from cust where c_quote >= getdate()

returns 0 rows

how can this be?

if your c_quote is string, and it's not in proper/expected date format, the explicit conversation could be failing, hence the false condition on your where. if it's a string, use a convert or a cast function, then compare

[Joell]

c_quote is a datetime data type in my database. What else can I try?

[ck9663]

c_quote is a datetime data type in my database. What else can I try?

how many rows will this query returns:

Select c_id_alpha, c_name, c_quote, getdate() from cust where c_quote >= cast('09/14/2007' as datetime)

[Joell]

That query with the cast returns the same rows as just using the date field. I need to use the getdate() though. I just dont understand why it will not bring back the same results.

[ck9663]

That query with the cast returns the same rows as just using the date field. I need to use the getdate() though. I just dont understand why it will not bring back the same results.

would you mind posting some of the data/rows?

[Joell]

would you mind posting some of the data/rows?

Select c_id_alpha Acct_no, c_quote, getdate() Getdate_column from cust where c_quote >= '09/14/2007'

406765 2007-09-14 00:00:00.000 2007-09-17 15:43:45.613
406789 2007-09-14 00:00:00.000 2007-09-17 15:43:45.613
406784 2007-09-14 00:00:00.000 2007-09-17 15:43:45.613
406786 2007-09-14 00:00:00.000 2007-09-17 15:43:45.613
406787 2007-09-14 00:00:00.000 2007-09-17 15:43:45.613
406788 2007-09-14 00:00:00.000 2007-09-17 15:43:45.613
406790 2007-09-17 00:00:00.000 2007-09-17 15:43:45.613

It works fine this way.... but when I use the getdate() function, I get 0 rows.

[ck9663]

Select c_id_alpha Acct_no, c_quote, getdate() Getdate_column from cust where c_quote >= '09/14/2007'

406765 2007-09-14 00:00:00.000 2007-09-17 15:43:45.613
406789 2007-09-14 00:00:00.000 2007-09-17 15:43:45.613
406784 2007-09-14 00:00:00.000 2007-09-17 15:43:45.613
406786 2007-09-14 00:00:00.000 2007-09-17 15:43:45.613
406787 2007-09-14 00:00:00.000 2007-09-17 15:43:45.613
406788 2007-09-14 00:00:00.000 2007-09-17 15:43:45.613
406790 2007-09-17 00:00:00.000 2007-09-17 15:43:45.613

It works fine this way.... but when I use the getdate() function, I get 0 rows.

because your c_quote are all 2007-09-14 dates and getdate() is 2007-09-17. getdate(), in this example, is greater than 2007-09-17, hence no result set are returned.

[Joell]

because your c_quote are all 2007-09-14 dates and getdate() is 2007-09-17. getdate(), in this example, is greater than 2007-09-17, hence no result set are returned.

I was using c_quote >= getdate() -1


The query is supposed to run at 5am so I do not know if it will work correctly until tomorrow but I think this might work:

c_quote >= convert(datetime,convert(varchar,getdate(),101)) -1

Thanks for your efforts!

[Dosth]

try this

convert(varchar(10),c_quote,101) >= convert(varchar(10),getdate()-1,101)

Thanks
Dosth