none
distinct row for all rows of the same month

    Question

  • I have a table like this:

    id, submitDate.

    submitDate field is set to smalldate type.

    I want to retrieve only one record for all the records with the same year/month value.  In other words, I want to get the 2013/10 for all the records that contain that part (2013/10) in the submitDate field.

    Thanks for help.

    Monday, October 28, 2013 8:05 PM

Answers

All replies

  • Oh, I got it.  This works.

    select distinct datepart(mm,submitDate) as Months from myTable.

    Monday, October 28, 2013 8:09 PM
  • select id, submitDate
    FROM yourtableName
    WHERE  submitDate>= DATEADD(month, DATEDIFF(month,0,GETDATE()),0) 
    AND  submitDate<DATEADD(month, DATEDIFF(month,0,GETDATE())+1,0) 

    Monday, October 28, 2013 8:12 PM
    Moderator
  • Oh, I got it.  This works.

    select distinct datepart(mm,submitDate) as Months from myTable.

    You may run into issue if you have multiple years data. Check this query out:

     
    select datepart(mm,getdate()) 
    , month(getdate())
    , Convert(varchar(6),getdate(),112)
    , Convert(varchar(4),getdate(),12)

    Monday, October 28, 2013 8:59 PM
    Moderator