Day of the week

How to find the saturday in the current week using sql server 2000

SELECTDATEADD(wk,DATEDIFF(wk,0,getdate()),5)

This assumes the DATEFIRST is set to 7 on the sql server. 7 meaning the first day of the week is Sunday.

|||

How to get the 11:59 pm of this saturday. I get the output of running the query as
2007-05-26 00:00:00.000
but I want some thing like below:
2007-05-26 11:59:00.000

|||

There are many methods to do this, but I might use something like this:

SELECTDATEADD( minute , -1 ,DATEADD( wk ,DATEDIFF( wk, 0,getdate() ) , 6))

|||

You are better off getting Sunday and checking to see if the value is less than that, rather than less than or equal to 11:59pm of Saturday. Unless of course you can never ever have 11:59:02pm on Saturday.

|||

Good point Motley. I do try to stress the date predicate style that is not like

WHERE dateColBETWEEN @.dt1AND @.dt2

which is an all-inclusive between hard dates but rather an inclusive lower, exclusive upper style

WHERE dateCol >= @.dt1AND dateCol < @.dt2

Which allows for variations in date precision. Not exactly sure where the original poster was going with that, but nonetheless...