• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

How to pull month to date records in SQL

I'm trying to pull month to date data in SQL.
0
sproctor722
Asked:
sproctor722
  • 3
  • 2
  • 2
  • +2
1 Solution
 
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now