How to pull month to date records in SQL

I'm trying to pull month to date data in SQL.
sproctor722Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tommy11bCommented:
Select Month(colDate) From Table  -- Will return the number month
DATENAME(mm, getdate())    -- Will return the month spelled out
0
sproctor722Author Commented:
I'm looking to pull records from a table for month to date.
0
dan_masonCommented:
This function will bring back the start of a month based on a date you supply. So the first of the current month would be obtained by:

SELECT dbo.FirstDayMonth(GETDATE())

And MTD would just be anything >= that value.


ALTER FUNCTION [dbo].[MonthDay99](@date datetime)
  Returns datetime
  AS
  BEGIN
  RETURN (
   SELECT CAST(DATEADD(dd,-(DAY(CAST(FLOOR(CONVERT(float,@date)) AS datetime))-1),CAST(FLOOR(CONVERT(float,@date)) AS datetime)) AS datetime)
  )
  END

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

dan_masonCommented:
Sorry, meant to paste this:
CREATE FUNCTION [dbo].[FirstDayMonth](@date datetime)
  Returns datetime
  AS
  BEGIN
  RETURN (
   SELECT CAST(DATEADD(dd,-(DAY(CAST(FLOOR(CONVERT(float,@date)) AS datetime))-1),CAST(FLOOR(CONVERT(float,@date)) AS datetime)) AS datetime)
  )
  END

Open in new window

0
Ken ButtersCommented:
select * from table where datefield >= CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
                CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME)

1) construct the first day of the month:
          first day of the month = current year / currenty month /  01

Then selecting all records where the date field you are comparing agsinst in your records is greater than or equal to first day of the month.
0
sproctor722Author Commented:
This is the sql I am using.
select *
from ds_final_transformation
WHERE invoice_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
AND invoice_date < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 31)
0
Anthony PerkinsCommented:
Perhaps I am missing something, but it should be as simple as:

SELECT  *
FROM    ds_final_transformation
WHERE   invoice_date BETWEEN DATEADD(day, 1 - DAY(GETDATE()), DATEDIFF(day, 0, GETDATE())) AND GETDATE()

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
Incidentally this:
select *
from ds_final_transformation
WHERE invoice_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
AND invoice_date < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 31)

Gives data from the whole month.  Mine will "pull month to date data".  So which is it?
0
sproctor722Author Commented:
Excellent!  You are a genius.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.