Show all the records from the current month

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

SQL Server 2000 Forums

Transact-SQL (2000)

Show all the records from the current month

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-19 : 05:37:24

Dragos writes "I want to show the records from the current month only. What function should I use to get this problem solved?"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 05:42:41

Select columns from yourTable where DateDiff(month,Datecolumn,GetDate())=0

Madhivanan

Failing to plan is Planning to fail

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-10-19 : 12:22:59

Another option is to have a function that will return the first day of the month and a function that will return the last day of the month. You can get hold of these functions in the following links:

http://www.sql-server-helper.com/functions/get-first-day-of-month.aspx
http://www.sql-server-helper.com/functions/get-last-day-of-month.aspx

If your date column doesn't have any time part:

SELECT * FROM YourTable
WHERE YourDateColumn BETWEEN [dbo].[ufn_GetFirstDayOfMonth] (GETDATE()) AND
[dbo].[ufn_GetLastDayOfMonth] (GETDATE())

If your date column has a time part:

SELECT * FROM YourTable
WHERE YourDateColumn >= [dbo].[ufn_GetFirstDayOfMonth] (GETDATE()) AND
YourDateColumn < DATEADD(MM, 1, [dbo].[ufn_GetFirstDayOfMonth] (GETDATE()))

Hope this helps.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-19 : 19:55:19

Those functions are not really necessary.

You can get the first day of this month like this:
dateadd(mm,datediff(mm,0,getdate()),0)

You can get the first day of next month like this:
dateadd(mm,datediff(mm,0,getdate())+1,0)


select
	*
from
	MyTable
where
	-- Select greater than or equal to first of current month
	DateCol >= dateadd(mm,datediff(mm,0,getdate()),0) and
	-- Select less than first day of next month
	DateCol < dateadd(mm,datediff(mm,0,getdate())+1,0)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-20 : 02:23:35

>>http://www.sql-server-helper.com/functions/get-first-day-of-month.aspx

I think there is no need for using Cast or Convert functions as DateAdd and DateDiff are more simpler than those

This is to get first day of year
select dateadd(year,datediff(year,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources