Dateadd, update only from 27th of last month

Question

I am creating a job to update flags in a call record table but only want it to have an effect on calls made after the 26th 23:59:59 of each month until present, Please can someone help me with the Dateadd syntax.

Answer 1

0

UPDATE  CalLTable
SET CallFlag = 1
WHERE   CallDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -5)

answered Nov 20 '09 at 05:41 AM

Peso
1.6k ● 4 ● 6 ● 8

neat, like it! :)

Nov 20 '09 at 06:10 AM Matt Whitfield ♦♦

The WHERE clause can be written as WHERE CallDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), '18991227')

Nov 20 '09 at 08:48 AM Peso

(comments are locked)

Answer 2

0

something like this....

Update CallTable
set CallFlag = case 
                when datepart(d,CallDate) => 27 then 1
                else 0
               end

How are you using DATEADD? Sounds like you are trying to loop around the date values and set the flag accordingly, incrementing by DATEADD - that's a procedural row-by-row way of doing things. Using a setbased approach like I have given WILL be much faster

answered Nov 20 '09 at 04:39 AM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

Script as follows: /* Update CallTable Set CallFlag = '1' Where calltime >='everything after 27th of most recent month' and subscriber is null */

I need to be sure no records before the 27th of the most recent month get affected i.e. 27 October '09

Nov 20 '09 at 05:05 AM Sias

Ahh now I get you! You might be better adding that sample script to your original question.

Nov 20 '09 at 06:01 AM Kev Riley ♦♦

And Peso's answer is perfect!

Nov 20 '09 at 06:05 AM Kev Riley ♦♦

Ill definately add the script next time, thanks for all the help guys.

Nov 20 '09 at 06:10 AM Sias

(comments are locked)

Dateadd, update only from 27th of last month

Follow this question

Related Questions