x

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

Sias gravatar image

Sias
13 1 1 1

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

2 answers: sort voted first
UPDATE CalLTable SET CallFlag = 1 WHERE CallDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -5) 
more ▼

answered Nov 20, 2009 at 05:41 AM

Peso gravatar image

Peso
1.6k 5 6 8

neat, like it! :)
Nov 20, 2009 at 06:10 AM Matt Whitfield ♦♦
The WHERE clause can be written as WHERE CallDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), '18991227')
Nov 20, 2009 at 08:48 AM Peso
(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
               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

more ▼

answered Nov 20, 2009 at 04:39 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x369

asked: Nov 20, 2009 at 04:23 AM

Seen: 1627 times

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