Select DISTINCT months and years - preferably in Danish months (if possible)

I have a table called "tblHotlineOpgaver". In there I have a date field called "dmyHotlineDate" where dates are stored as yyyy-mm-dd (no time). The dates are an import from another source.

The date column is sortable as dates.

I need a select distinct where I can extract the MONTH and YEAR - and to make it a little more difficult - I need it in Danish formats.

So - in US English January 2011 would read:
January 2011

In Danish it would read:
Januar 2011

Any suggestions?
LVL 1
jensjakobsenAsked:
Who is Participating?
 
Paul_Harris_FusionConnect With a Mentor Commented:
Try this...

Select dm, dy, datestring
 from
(
select Distinct
   year(dmyHotlineDate) as dy
 , month(dmyHotlineDate) as dm
 , DATENAME(month, dmyHotlineDate) + ' ' + DATENAME(year, dmyHotlineDate) as datestring
from tblHotlineOpgaver
) ilv
order by dy, dm
0
 
Paul_Harris_FusionCommented:
Hi - me again!

Have a look at this link
http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/80035bec-0acd-4f9b-8412-28aa0649eebd

Basically you have DATENAME function
e.g.
Select DATENAME(month,GetDate())
which gives you the names in your currently selected language


Select dmyHotlineDate,f1, DATENAME(month, dmyHotlineDate) + ' ' + DATENAME(year, dmyHotlineDate) as datestring
 from
(
select Distinct
      dmyHotlineDate
 ,  CONVERT(varchar(10), dmyHotlineDate, 101) as f1
from tblHotlineOpgaver
) ilv
order by dmyHotlineDate
0
 
Pratima PharandeCommented:
For us-English

Select distinct ( month(dmyHotlineDate) + ' ' + year(dmyHotlineDate))
from tblHotlineOpgaver
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Pratima PharandeCommented:
To change the month names to danish
you can run
SET LANGUAGE danish

Select distinct ( month(dmyHotlineDate) + ' ' + year(dmyHotlineDate))
from tblHotlineOpgaver

refer
http://msdn.microsoft.com/en-us/library/ms174398.aspx
0
 
jensjakobsenAuthor Commented:
Hi Paul.

Thanks a lot for your input. However - I'm not quite able to understand how to use your solution.

The first thing I tried was to copy/paste your solution but the output was not like expected.

I only want the distinct MONTH and YEAR.

With your example I get distinct DAY, MONTH and YEAR - which means I get all days in March 2011, all days in February 2011 and so on.

How do I get rid of the days?
0
 
jensjakobsenAuthor Commented:
Hi Pratima

Your example gave me the numbers (years) from 2011 to 2022 - not ordered or able to be ordered.

That means:

I need the MONTH and YEAR - and it needs to able to be ordered.
0
 
Pratima PharandeCommented:
try this

SET LANGUAGE danish

Select distinct ( DATENAME(dmyHotlineDate, GETDATE()) + ' ' + year(dmyHotlineDate))
from tblHotlineOpgaver
0
 
jensjakobsenAuthor Commented:
@ Paul.

The datestring was the field where I had hoped it would read the values in Danish format.
0
 
jensjakobsenAuthor Commented:
At Partima

I got this error:

Msg 155, Level 15, State 1, Line 3
'dmyHotlineDate' is not a recognized datename option.
0
 
Pratima PharandeCommented:
for ordering - chanhe ordering as per your requirment

Select distinct ( DATENAME(dmyHotlineDate, GETDATE()) + ' ' + year(dmyHotlineDate))
from tblHotlineOpgaver
order by year(dmyHotlineDate) desc , month(dmyHotlineDate) desc
0
 
Pratima PharandeCommented:
sorry try this

Select distinct ( DATENAME(month ,dmyHotlineDate) + ' ' + year(dmyHotlineDate))
from tblHotlineOpgaver

for ordering - chanhe ordering as per your requirment

Select distinct ( DATENAME(month ,dmyHotlineDate) + ' ' + year(dmyHotlineDate))
from tblHotlineOpgaver
order by year(dmyHotlineDate) desc , month(dmyHotlineDate) desc
0
 
jensjakobsenAuthor Commented:
@Pratima.

Now I get this error:

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
0
 
Pratima PharandeCommented:
is this giving correct output ?

Select distinct ( DATENAME(month ,dmyHotlineDate) + ' ' + year(dmyHotlineDate))
from tblHotlineOpgaver
0
 
jensjakobsenAuthor Commented:
Unfortunately not:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'March ' to data type int.
0
 
Pratima PharandeCommented:

Select distinct ( datename(month,dateadd(month, M - 1, 0)) + ' ' + Y)
From (
select year(dmyHotlineDate) as Y ,month(dmyHotlineDate) as M
from tblHotlineOpgaver
order by year(dmyHotlineDate) desc , month(dmyHotlineDate) desc )
0
 
jensjakobsenAuthor Commented:
Msg 1033, Level 15, State 1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

0
 
jensjakobsenAuthor Commented:
I tried to exclude the ORDER BY

Select distinct ( datename(month,dateadd(month, M - 1, 0)) + ' ' + Y)
From (
select year(dmyHotlineDate) as Y, month(dmyHotlineDate) as M
from tblHotlineOpgaver)

and it gave this error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
0
 
jensjakobsenAuthor Commented:
Thanks a lot

I added: SET LANGUAGE danish

Then the syntax was:

SET LANGUAGE danish
Select dm, dy, datestring
 from
(
select Distinct
   year(dmyHotlineDate) as dy
 , month(dmyHotlineDate) as dm
 , DATENAME(month, dmyHotlineDate) + ' ' + DATENAME(year, dmyHotlineDate) as datestring
from tblHotlineOpgaver
) ilv
order by dy, dm
0
 
jensjakobsenAuthor Commented:
and now for the last question - I hope this will be easy :)
0
 
Pratima PharandeCommented:
don't you think as I have given the solution of
SET LANGUAGE danish
so need to get some points
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.