How can this be said in English language?

Question

How can this be said in English language?

VoldemarG

Hall of Fame

Points: 3883
More actions
March 8, 2020 at 6:02 pm

#3732318

how can the meaning of this Where clause be explained in a human language?
this query <does what?> ...does it really make sence the way it is?
delete from FROM dbo.huge_queue_table
WHERE date_created < DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)
Thank you.
Likes to play Chess

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16174 More actions · Answer 1

looks clear enough for some one with your level of experience.

so what seems to be your question after you have run "select DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)" to see its result?

VoldemarG Hall of Fame Points: 3883 More actions · Answer 2

this part confuses me:

DATEDIFF(wk,0,GETDATE()),

and this was a one time run query in prod, not scheduled. Deleting old rows... i actually need to make an SP out of it with params and schedule. So this part would be a good candidate to parameter but why DATEDIFF(wk,0,GETDATE()) ?

this is the number of weeks since the year 2000.

Likes to play Chess

frederico_fonseca SSCoach Points: 16174 More actions · Answer 3

are you sure? did you run it? and does it really look like the number of weeks that 20 years would have?

VoldemarG Hall of Fame Points: 3883 More actions · Answer 4

yes, you right. I just assumed that. it is not what it means. What does parameter value = 0 mean in Datediff function in such case?

Likes to play Chess

frederico_fonseca SSCoach Points: 16174 More actions · Answer 5

"0" (zero) on these cases means the default value for datetime - which you can determine by a simple change to that code (or by reading the manuals)

VoldemarG Hall of Fame Points: 3883 More actions · Answer 6

it appears that select DATEDIFF(wk,0,GETDATE())

returns 6271 , which . divided by 52 - 120 years.

so it is weeks since the year 1900.

Likes to play Chess

VoldemarG Hall of Fame Points: 3883 More actions · Answer 7

Thank you! I cannot believe I either forgot or did not know about default value for 'zer0' or for ' ' for datetime datatype...

I ran this to confirm.

create table wdi(datum datetime null)

insert into wdi(datum) values ('')

insert into wdi(datum) values (0)

insert into wdi(datum) values (NULL)

select * from wdi

drop table wdi

Likes to play Chess

VoldemarG Hall of Fame Points: 3883 More actions · Answer 8

but then why to juggle it ti like that

This whole thing returns date of midnight following the today's date.

Like what are we saying? Delete all records that are dated earlier than midnight tomorow?

then why go to such complexity as this?

select DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

Likes to play Chess

frederico_fonseca SSCoach Points: 16174 More actions · Answer 9

its doing more than that - it just happens that today (Sunday) happens to be a week boundary.

try it with multiple dates (consecutives, spawning 2 -3 weeks) instead of getdate to see its variation

Phil Parkin SSC Guru Points: 246993 More actions · Answer 10

frederico_fonseca wrote:

... spawning 2 -3 weeks) instead of getdate to see its variation

I think you mean 'spanning'.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

frederico_fonseca SSCoach Points: 16174 More actions · Answer 11

Phil Parkin wrote:

frederico_fonseca wrote:
... spawning 2 -3 weeks) instead of getdate to see its variation
I think you mean 'spanning'.

yeah..

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Check this old blog: https://www.sqlservercentral.com/blogs/some-common-date-routines

VoldemarG Hall of Fame Points: 3883 More actions · Answer 13

ok. now i fully see how it is working. But since we are going to run this job on Sundays only, so we are fine with the code then. I don't know why such a simple piece of code made me so confused. Thanks for your help !

Likes to play Chess