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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
Paul_Harris_FusionCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.