date related function in sql - 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 related function in sql

Author

Topic

ratheeshsql
Starting Member

17 Posts

Posted - 2006-07-03 : 06:41:04

hi,

Can someone give me the sql query to find out data that has been added in the current month.(table contains a field 'date' of type datetime.)

rthsh.wst

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-03 : 06:46:27

Here's an example (I like examples

)...

--data
declare @t table (id int identity(1, 1), date datetime)
insert @t
          select getdate()
union all select dateadd(wk, -1, getdate())
union all select dateadd(wk, -2, getdate())
union all select dateadd(wk, -3, getdate())
union all select dateadd(wk, -4, getdate())
union all select dateadd(wk, 1, getdate())
union all select dateadd(wk, 2, getdate())
union all select dateadd(wk, 3, getdate())
union all select dateadd(wk, 4, getdate())
union all select dateadd(wk, 5, getdate())

--calculation
select * from @t
where date >= dateadd(m, datediff(m, 0, getdate()), 0)
  and date < dateadd(m, datediff(m, 0, getdate())+1, 0)

/*results
id          date                                                   
----------- ------------------------------------------------------ 
1           2006-07-03 11:44:35.170
6           2006-07-10 11:44:35.170
7           2006-07-17 11:44:35.170
8           2006-07-24 11:44:35.170
9           2006-07-31 11:44:35.170
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

ratheeshsql
Starting Member

17 Posts

Posted - 2006-07-03 : 06:51:12

i got one function like "month(getdate())"- is it right?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-03 : 07:08:39

Yeah...there is a simple solution:

select * from sometable where month(dt) = month(getdate())

although I guess since in the above query there is a function on the left hand side, it's not a best solution performance-wise(Indexes will not be used, if any). Better, go for the RyanRandall's solution.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-03 : 08:41:29

quote:
Originally posted by ratheeshsql

i got one function like "month(getdate())"- is it right?

Yes, this is how you get the month. But beware that this approach does not notice the difference between June 19, 2005 and June 18, 2006.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-03 : 09:11:39

Ryan's method is effcient if there exists index on the date column
Also , just for clarity, I prefer using month in place of m

select * from @t
where date >= dateadd(month, datediff(month, 0, getdate()), 0)
and date < dateadd(month, datediff(month, 0, getdate())+1, 0)

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-03 : 09:23:41

quote:
Also, just for clarity, I prefer using month in place of m

Yes, this is a good idea.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

ratheeshsql
Starting Member

17 Posts

Posted - 2006-07-04 : 01:18:40

thanks all..

rthsh wst

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources