Question about query with multiple unions

Databases
1

Hello:

I have a table with dates, like this:

--------------
|    date    |
--------------
| 2010-01-03 |
| 2010-01-05 |
| 2010-03-12 |
| 2010-04-17 |
| 2010-07-01 |
| 2010-09-02 |
| 2010-12-25 |
--------------

I want to have a query that returns the months that exist on the table. For the example above, I’d like to have the result

'January'
'March'
'April'
'July'
'September'
'December'

Or eventually, the numbers of the months: 01, 03, 04, 07, 09, 12.

I can do it like this:

select top 1 'January' as month
From TestTable
where month(date) = 01

union all 

select top 1 'February' as month
From TestTable
where month(date) = 02

union all 

select top 1 'March' as month
From TestTable
where month(date) = 03

(...)

But this doesn’t look like a good solution. Is there any way around this?

Thanks in advance!

2

Try this for size…


SELECT CASE MONTH(date)
       WHEN 1 THEN 'January'
       WHEN 2 THEN 'February'
       WHEN 3 THEN 'March'
       ...
       WHEN 12 THEN 'December' END AS month
  FROM TestTable;
3

Thanks so much, I didn’t remember using the CASE statement :slight_smile:

4 

SELECT DISTINCT MONTH(date)
  FROM TestTable

:slight_smile: