month to date? [resolved]

[thebloke]

Guys

I need a simple way to get a chunk of 'month to date' data from a sqlserver database?

I'm currently using:

MTA.DateCreated between getdate() - (day(getdate()-1) and getdate()

MTA.DateCreated being a datetime field

That returns everything from the first of the month but at the current time, not 00:00:00.

Any clues?

Peter

[szlamany]

Cast(Convert(varchar(10),GetDate(),101) as DateTime) will get you today's date without the time.

Then this will work in your WHERE clause.

MTA.DateCreated 
   between DateAdd(dd,-(DatePart(dd,GetDate())-1),Cast(Convert(varchar(10),GetDate(),101) as DateTime)) 
     and Cast(Convert(varchar(10),GetDate(),101) as DateTime)

[thebloke]

Nice one mate, works a treat.