datepart(wk, getdate()) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

datepart(wk, getdate())

Author

Topic

mukhan85
Starting Member

46 Posts

Posted - 2008-05-27 : 19:09:56

Hi, everyone! Could anyone help me with understanding of the datepart() function. I want to retrive data from a database that was added in this week, starting from Sunday. Suppose today is Tuesday, so I want to get all the records that were added on Sunday, Monday, and Tuesday. Can I use
...
where datepart(wk, Date_Reported)=datepart(wk, getdate()) ?
Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 00:16:08

Have a had a look at DATEPART explanation in books online?

http://msdn.microsoft.com/en-us/library/ms174420.aspx

Now, to get all records added during week you can do this

DECLARE @CurrentDate
SET @CurrentDate = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

SELECT * FROM YourTable WHERE DateColumn > DATEADD(dd,-1 * (DATEPART(dw,@CurrentDate)-1),@CurrentDate)

Also make sure SELECT @@DATEFIRST returns 7 which indicates you have set Sunday as first day of week (default option)

Bsically the above query returns the day of week for current day and goes back 1 less than number of days to reach the first day (Sunday) of week and takes all records from it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources