Date Function - 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

Date Function

Author

Topic

diggydee
Starting Member

5 Posts

Posted - 2009-10-26 : 13:49:26

Hi Guys,

I have code running to get 1 month data, I used this date function

recvd_dt >= dateadd(m,datediff(m,0,getdate()-1),0) and
recvd_dt < dateadd(m,datediff(m,0,getdate()-1)+1,0)

Now i want to run every week to get week data, can anyone please help me how to write date function to get week data, I will gratefull for you.

Thanks
Diggy

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-10-26 : 14:26:03

change dateadd and datediff(m...

to dateadd(week...
or
dateadd(wk...

diggydee
Starting Member

5 Posts

Posted - 2009-10-26 : 14:40:46

Russel, Thanks for reply. I tried that but the job is schedule every Monday and 1st of every month. if its ran 1st monday of novermber it include october data too right....in this case how should right my condition.......please help me in this i am begineer in SQL....Can anyone help me.....

Thanks
Diggy

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-10-26 : 15:18:01

so you want last week's data, but only if it's in the current month?

take a little time to understand the dateadd and datediff functions. for example:


select dateadd(month, datediff(month, 0, getdate()), 0) -- first day of current month
select dateadd(week, datediff(week, 0, getdate())-1, 0) -- first day of last week
select dateadd(week, datediff(week, 0, getdate()), 0)-1 -- last day of last week

so your where clause will be similar to this:


WHERE	recvd_dt BETWEEN
	dateadd(week, datediff(week, 0, getdate())-1, 0)
	and
	dateadd(week, datediff(week, 0, getdate()), 0)-1
AND	recvd_dt >= dateadd(month, datediff(month, 0, getdate()), 0)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-10-26 : 15:19:55

this assumes that the @@DateFirst setting matches your weeks...if it doesn't, you'll need to adjust for that

Michael Valentine Jones wrote a nice function for that stuff somewhere here you can search on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources