MonthToDate function?

Question

Post reply

MonthToDate function?

joshd 1807

Default port

Points: 1499
More actions
March 20, 2012 at 9:18 am

#258926

I'm creating a report through Crystal and I'm needing to get creative. I need to create a data structure to "structurize" a report I'm building.
What I need to do is create a view that has MTD quantities (which will need to be summed up) but I don't have much knowledge of the date functions within TSQL.
Is there a formula I need to use or is there a built in MTD funcion?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

1. http://msdn.microsoft.com/en-us/library/ms186724.aspx

2. http://www.sqlservercentral.com/articles/Best+Practices/61537/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

drew.allen SSC Guru Points: 76998 More actions · Answer 2

It's easy to calculate the beginning of the month using the following function.

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

You can then use that in your CASE expression or WHERE clause as appropriate.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

drew.allen (3/20/2012)
It's easy to calculate the beginning of the month using the following function.
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
You can then use that in your CASE expression or WHERE clause as appropriate.
Drew

Using the above combined with this will get all records during the month:

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

Used like this:

OrderDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) and

OrderDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

This can be used in a CASE or WHERE clause.

joshd 1807 Default port Points: 1499 More actions · Answer 4

Lynn and Drew,

This is exactly what I was looking for. I appreciate it!