SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Evangelist NokX's Avatar
    Join Date
    Feb 2003
    Location
    Knoxville, TN
    Posts
    524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Getting all records from the beginning of the current month

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

    Code:
    SELECT * FROM myTable WHERE DateCreated => '3/1/2014'
    I'd like to do something like (making up the SQL functions)...

    Code:
    SELECT * FROM myTable WHERE DateCreated => MAKEDATE(MONTH(NOW()), 1, YEAR(NOW()))
    Anything available along these lines? Thanks!

  2. #2
    SitePoint Addict bronze trophy WolfShade's Avatar
    Join Date
    Mar 2014
    Location
    St. Louis, MO, USA
    Posts
    323
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)
    What flavour SQL? MS? MySQL? Oracle?

  3. #3
    SitePoint Evangelist NokX's Avatar
    Join Date
    Feb 2003
    Location
    Knoxville, TN
    Posts
    524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by WolfShade View Post
    What flavour SQL? MS? MySQL? Oracle?
    Microsoft SQL.

  4. #4
    SitePoint Addict bronze trophy WolfShade's Avatar
    Join Date
    Mar 2014
    Location
    St. Louis, MO, USA
    Posts
    323
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)
    Hmm.. well.. it's been a while since I worked MS SQL. I did manage to do this in Oracle, though.

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

    ^_^

  5. #5
    SitePoint Addict bronze trophy WolfShade's Avatar
    Join Date
    Mar 2014
    Location
    St. Louis, MO, USA
    Posts
    323
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT *
    FROM TABLE
    WHERE Table_Date >= DatePart('yyyy',getDate()) + '-' + DatePart('mm',getDate()) + '-01'
    .. just guessing..

  6. #6
    SitePoint Addict bronze trophy WolfShade's Avatar
    Join Date
    Mar 2014
    Location
    St. Louis, MO, USA
    Posts
    323
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)
    .. if the above doesn't work, then you might need to put everything after the ">=" into a CAST().

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star
      FROM mytable
     WHERE datecreated >= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •