asked on

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?

Paul_Harris_Fusion

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

Pratima

For us-English

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

Pratima

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

jensjakobsen

ASKER

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?

jensjakobsen

ASKER

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.

Pratima

try this

SET LANGUAGE danish

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

jensjakobsen

ASKER

@ Paul.

The datestring was the field where I had hoped it would read the values in Danish format.

jensjakobsen

ASKER

At Partima

I got this error:

Msg 155, Level 15, State 1, Line 3
'dmyHotlineDate' is not a recognized datename option.

Pratima

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

Pratima

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

jensjakobsen

ASKER

@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.

Pratima

is this giving correct output ?

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

jensjakobsen

ASKER

Unfortunately not:

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

Pratima

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 )

jensjakobsen

ASKER

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.

jensjakobsen

ASKER

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 ')'.

ASKER CERTIFIED SOLUTION

Paul_Harris_Fusion

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jensjakobsen

ASKER

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

jensjakobsen

ASKER

and now for the last question - I hope this will be easy :)

Pratima

don't you think as I have given the solution of
SET LANGUAGE danish
so need to get some points