sort distinct date column

Question

Hi Experts,

I need distinct year and month from date column which would be sorted by same column.

I have date coulmn with values like (YYYY/MM/DD)

2007/11/7
2007/1/8
2007/11/4
2007/12/3
2008/10/4
2009/11/5
2008/5/16

after having query, it should be

2007/1/1
2007/11/1
2007/12/1
2008/5/1
2008/10/1
2009/11/1

This doesn't seems to be working

SELECT distinct (cast(year(datecol) as nvarchar(20) ) +
       '/'+ cast(month(datecol) as nvarchar(20) ) + '/1') as dt1 
FROM Table 
ORDER BY dt1

Thanks in advance. Anil

Answer 1

When dealing with dates in SqlServer avoid using cast like this - the resulting format will change depending on server config.

Instead use convert and choose a format (for instance 112) that adds leading zeros to the month.

Answer 2

Soemthing like this would work on MS SQL Server:

select 
    distinct 
    dateadd(day, -1 * DAY(datefield) + 1, datefield)
From
    datetable
order by
    dateadd(day, -1 * DAY(datefield) + 1, datefield)

The DATEADD function call basically subtracts (day-1) DAYS from the current date --> you always get the first of whatever month that date is in.

Sort by it and you're done! :-)

ADditionally, you could also add this functionality to your table as a "computed column" and then use that for easy acccess:

alter table yourTable 
  add FirstOfMonth As DATEADD(day, -1 * DAY(datefield) + 1, datefield) persisted

Then your query would be even simpler:

SELECT DISTINCT FirstOfMonth
FROM YourTable
ORDER BY FirstOfMonth

Marc

Answer 3

Anil, Do you also have time part in the dates ? What dataType are you using for the column ? Are you using DateTime dataType or Char ?

This works for me

SELECT DISTINCT (DateField) AS Date FROM DateTable ORDER BY 1