Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select records from current month Expand / Collapse
Author
Message
Posted Monday, January 7, 2013 7:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 5:13 AM
Points: 61, Visits: 721
Hi,

I use a very basic select query

SELECT * FROM Client_Trades.DBO.Trades
WHERE MONTH(Trade_Date) = '01'

To extract records from a particular month. Is there any way of me not having to change the value e.g. '01' every month and just having it so it selects from the current month?

Thanks for any help.

EDIT: Trade_Date is a Date datatype.
Post #1403620
Posted Monday, January 7, 2013 8:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
Try this:

DECLARE @Date DATE = GETDATE();

SELECT *
FROM Client_Trades.DBO.Trades
WHERE Trade_Date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)
AND Trade_Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date)+1, 0);

What that does is calculate the first day of the current month, and the first day of the next month. Then it makes sure the Trade_Date >= first day of current, and < first day of next month.

Does that do what you need?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1403633
Posted Monday, January 7, 2013 8:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 5:13 AM
Points: 61, Visits: 721
This works perfectly. Thank you very much :)
Post #1403644
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse