Using the DATEADD function with a Date Function for the Date

jtflex
jtflex used Ask the Experts™
on
I am trying to run a script in SQL Anywhere 11 to update records based on activity that occured 3 days ago with the DATEADD function.

I am using it as follows:

DATEADD(day, -3, NOW())

Although it runs the script without any reported errors, it doesn't update the records.

If I use the following:

DATEADD(day, -3, '2012-05-23')

It updates the record correctly.

I've tried "GETDATE" with no luck either.

What can I use to replace an actual date for this script?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012

Commented:
Can you show us the actual UPDATE statement? You can change table and column names but it's helpful to see what's actually being run.

Author

Commented:
UPDATE "DBA"."staffing_tbl" SET staffing_benign_si = 'N', wstat_no_in = 162 WHERE wSTAT_NO_IN = 86 and staffing_benign_si = 'Y' AND staffing_calendar_da = DATEADD(day, -3, NOW())
Commented:
I guess the difference is NOW() or getdate functions includes time as well. When you use a date without time default time is 12:00 am. Most probably dates in your table have that time too. If you use '=' to qualify rows, a date without time is working for that purpose. But you can't use any other time by using now() or getdate if it is the case.

You can try to convert getdate to a string without time like below and use in where clause:

convert(char(10), getdate(), 101)

Author

Commented:
Thanks, I got it working with your help.  It through an error but I found it was related to the style.  It need 120 not 101

convert(char(10), getdate(), 120)

Joe

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial