Getting all records from the beginning of the current month

Databases
1

I have a table where I’d like to just pull records that are new that month. I’m not too familiar with SQL functions but I’m assuming there’s a way to grab the current month and year within SQL.

So instead of editing the query every month…

SELECT * FROM myTable WHERE DateCreated => '3/1/2014'

I’d like to do something like (making up the SQL functions)…

SELECT * FROM myTable WHERE DateCreated => MAKEDATE(MONTH(NOW()), 1, YEAR(NOW()))

Anything available along these lines? Thanks!

2

What flavour SQL? MS? MySQL? Oracle?

3

Microsoft SQL.

4

Hmm… well… it’s been a while since I worked MS SQL. I did manage to do this in Oracle, though.


SELECT *
FROM TABLE
WHERE TRUNC(Table_Date) >= TO_DATE(EXTRACT(YEAR from sysdate) || '-' || EXTRACT(MONTH from sysdate) || '-01','YYYY-MM-DD')

HTH

:slight_smile:

5 
SELECT *
FROM TABLE
WHERE Table_Date >= DatePart('yyyy',getDate()) + '-' + DatePart('mm',getDate()) + '-01'

… just guessing…

6

… if the above doesn’t work, then you might need to put everything after the “>=” into a CAST().

7 
SELECT something
     , anything
     , just_not_the_dreaded_evil_select_star
  FROM mytable
 WHERE datecreated >= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

:slight_smile: