Dateadd, update only from 27th of last month

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.

more ▼

asked Nov 20, 2009 at 04:23 AM in Default

avatar image

13 1 1 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

SET CallFlag = 1
more ▼

answered Nov 20, 2009 at 05:41 AM

avatar image

1.6k 5 6 9

(comments are locked)
10|1200 characters needed characters left

something like this....

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

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

more ▼

answered Nov 20, 2009 at 04:39 AM

avatar image

Kev Riley ♦♦
64.1k 48 61 81

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, 2009 at 05:05 AM Sias

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

Nov 20, 2009 at 06:01 AM Kev Riley ♦♦

And Peso's answer is perfect!

Nov 20, 2009 at 06:05 AM Kev Riley ♦♦

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

Nov 20, 2009 at 06:10 AM Sias
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 20, 2009 at 04:23 AM

Seen: 2877 times

Last Updated: Nov 20, 2009 at 06:06 AM

Copyright 2016 Redgate Software. Privacy Policy