SQL to return the name of a month

Question

Hi There,

I have a table with a DATETIME column in it. It stores all of our printing information, input from HP's web jet admin.

Is there any way that I can get a list of the months of data available in there? For example, I want to dynamically create a HTML dropdown box using PHP.

To make this a little clearer - if I had the following entries in my table:

DATETIMECOL

01/01/2010 00:00:00
01/01/2010 00:00:00
01/02/2010 00:00:00
01/02/2010 00:00:00
01/04/2010 00:00:00

The SQL statement would need to return the following values:

January 2010
February 2010
April 2010

Hope I have made myself clear?

Thanks
Matt

Answer 1

You could add following column in your query:

SELECT DISTINCT MONTH(DATETIMECOL) + CONVERT(VARCHAR(4),YEAR(DATETIMECOL)) AS [MON_YYYY]

from TABLE_NAME

---

Manoj Pandey

 

Agree with Atif, Correction:

SELECT DISTINCT DATENAME(MM,DATETIMECOL) + CONVERT(VARCHAR(4),YEAR(DATETIMECOL)) AS [MON_YYYY]

from TABLE_NAME

Answer 2

DECLARE @vTable TABLE (dt Datetime)

INSERT INTO @vTable
	SELECT '2010-01-01 00:02:00' UNION ALL
	SELECT '2010-01-02 00:02:00' UNION ALL
	SELECT '2010-01-03 00:02:00' UNION ALL
	SELECT '2010-02-01 00:02:00' UNION ALL
	SELECT '2010-02-01 00:02:00' 
	

SELECT DISTINCT DateName(mm,dt) + ' ' + CAST(YEAR(dt) AS VARCHAR(10))
FROM @vTable

---

Please visit my Blog for some easy and often used t-sql scripts

Answer 3

Cast(year(dt) as char(4))  will be enough

but I agree with Atif

Answer 4

if you are looking for ordered month + Year list, check this out;

DECLARE @vTable TABLE (dt Datetime)

INSERT INTO @vTable
	SELECT '2010-01-01 00:02:00' UNION ALL
	SELECT '2010-01-02 00:03:00' UNION ALL
	SELECT '2010-01-03 00:04:00' UNION ALL
	SELECT '2010-02-04 00:05:00' UNION ALL
	SELECT '2010-02-05 00:06:00' UNION ALL
	SELECT '2010-04-06 00:07:00' UNION ALL
	SELECT '2010-04-07 00:08:00' UNION ALL
	SELECT '2009-04-08 00:09:00' UNION ALL
	SELECT '2007-04-08 00:09:00' 
	
SELECT ndt FROM (
SELECT *,ROW_NUMBER() OVER (PARTITION BY ndt ORDER BY rno) rno2
FROM (
SELECT DateName(mm,dt) + ' ' + CAST(YEAR(dt) AS VARCHAR(10)) AS ndt
,ROW_NUMBER() OVER (ORDER BY YEAR(dt),MONTH(dt)) rno
FROM @vTable
) Main
) Main2 WHERE rno2 = 1
ORDER BY rno

---

Please visit my Blog for some easy and often used t-sql scripts

Answer 5

Assuming DATETIMECOL may include days other than the first of the month, you can return a distinct list of the first date of each month with:

SELECT DISTINCT
	DATEADD(MONTH, DATEDIFF(MONTH, '19000101', DATETIMECOL), '19000101') AS FirstDayOfMonth
FROM dbo.MyTable
ORDER BY FirstDayOfMonth;

The best practice is to format results in application code rather than in T-SQL.  See http://php.net/manual/en/function.date.php.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

Hi Atif,

I have just tried your example which looks the most approipiate - however I am not getting dates in the correct order. I am getting the following:

June 2007
November 2009
December 2009
March 2010
April 2010
May 2010
June 2010
July 2010
September 2010
August 2010
October 2010
January 2010
February 2010

Any ideas why it might put Jan and Feb at the end?

Thanks
Matt

Answer 7

Because you dates are now converted to varchar datatype, hence ordered alphabetically.

Try Atif's solution.

---

Manoj Pandey

Answer 8

I have checkd it with random dates. its not giving issue to me.

---

Please visit my Blog for some easy and often used t-sql scripts