Date Calculation help

[JazzMaan]

i would like to select date from a table where the transaction happened in last week (sunday through saturday)

What can i use to day to find out last week Sunday and saturday given running the query today.

thanks

 

[Rofeu]

check out the datepart,dateadd, and getdate functions. You should be able to figure it out.

Cheers,

Rofeu

 

[simian336]

As Rofeu said start with

select datepart(dw,getdate())

That will give you the current day of the week. Then calculate the starting datea and ending date from there.

(The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7)

Simi

 

[Rofeu]

That reminds me, you can use @@DATEFIRST to determine which day your instance uses as the starting day of the week, or use SET DATEFIRST to modify it as you wish. (take care that this changes the setting for the sqlserver, not just for your query!)

Cheers,

Rofeu

 

[gmmastros]

That reminds me, you can use @@DATEFIRST to determine which day your instance uses as the starting day of the week,

I'm sorry, but this isn't right.

@@DATEFIRST will return the first day of the week for the current session. More specifically, @@DATEFIRST is based on the language setting of the user that is currently logged in to SQL Server. So, different users (that have different languages) may return a different value for @@DATEFIRST.

or use SET DATEFIRST to modify it as you wish. (take care that this changes the setting for the sqlserver, not just for your query!)

SET DATEFIRST will override the language setting for first day of week. This is true. However, this setting will only be used until you disconnect from the database. The next time you log in, it will be reset to the language setting that is set for the log in.

Rofeu, I apologize for correcting you. I'm just worried that there may be some misunderstanding regarding the DATEFIRST settings.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JazzMaan]

so i came up with below that works but thats a lot of code - is this efficent or any better way?


SELECT
StartDate =
CASE (DATEPART(dw, GETDATE()) + @@DATEFIRST) %7
WHEN 1 THEN Convert(date,getdate()-7)
WHEN 2 THEN Convert(date,getdate()-8)
WHEN 3 THEN Convert(date,getdate()-9)
WHEN 4 THEN Convert(date,getdate()-10)
WHEN 5 THEN Convert(date,getdate()-11)
WHEN 6 THEN Convert(date,getdate()-12)
WHEN 7 THEN Convert(date,getdate()-13)
END,
ENDDATE = CONVERT(date,GETDATE()-DATEPART(dw, GETDATE()))

 

[Rofeu]

No apologies necessary, George. My understanding of the lifetime of these settings was flawed.

(doesn't take away that the OP can use them for his problem)

Cheers,

Rofeu

 

[gmmastros]

JazzMaan,

Seems like the start date will always be 6 days earlier than the end date, right?

SELECT STARTDATE = CONVERT(date,GETDATE()-DATEPART(dw, GETDATE())-6),
       ENDDATE = CONVERT(date,GETDATE()-DATEPART(dw, GETDATE()))

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Rofeu]

No need for testing the value. You can calculate it directly.

select	dateadd(d,-(datepart(dw,getdate()-(7-@@datefirst))+6),getdate()) [StartDate]
	,	dateadd(d,-(datepart(dw,getdate()-(7-@@datefirst))  ),getdate()) [EndDate]

Cheers,

Rofeu

 

[JazzMaan]

Startdate = starting day of week - Sunday
Enddate = end of week so Saturday

still testing/checking i think i need -
WHEN 0 THEN Convert(date,getdate()-6)