Visio User


How to get distinct year from the data below

Sample data is .....

stdate

2007-09-02 08:30:00.000

2007-09-02 08:40:00.000

2007-09-02 08:20:00.000

2007-09-02 08:34:00.000

Result i am looking is.......

2007

select distinct stdate

from tbl_employee

select year(distinct stdate)

from tbl_employee




Re: What is best way get result?

Bushan


Code Block

select distinct year(stdate) from tbl_employee







Re: What is best way get result?

Manivannan.D.Sekaran

Here it is,

select distinct year(stdate) from tbl_employee

See, Books online for Distinct.

NOTE: Only Aggregate functions (SUM,COUNT,AVG..etc.) allows Distinct keyword inside the function.







Re: What is best way get result?

clevesteve

If I understand you correctly, you want to get as a result:

2007

I think what you are trying to do is

SELECT DISTINCT year(stdate)

FROM tbl_employee

If that doesn't work, try

SELECT year(stdate)

FROM tbl_employee

GROUP BY year(stdate)

ORDER BY year(stdate)

I recommend the group by method because then you can get statistics if you need them like MAX, MIN, and AVG, and COUNT.






Re: What is best way get result?

Visio User

thanks a bunch....worked perfect.





Re: What is best way get result?

Visio User

Why this won't work .

select distinct year(stdate)

from tbl_employee

order by year(stdate) desc

this works fine...

select distinct year(stdate)

from tbl_employee

order by year(stdate)





Re: What is best way get result?

Visio User

i think some space problem is happening...

how to trim stdate

we don't have trim in sql server ....need to use ltrim and rtrim .

select distinct year(stdate)

from tbl_employee

order by year(stdate) desc





Re: What is best way get result?

DaleJ

Yes, TRIM == LTRIM(RTRIM(field))






Re: What is best way get result?

Visio User

This way ....not working.

select distinct year(LTRIM(RTRIM(stdate) ))

from tbl_employee

order by year(stdate) desc





Re: What is best way get result?

Chris Howarth

Visio User wrote:

Why this won't work .

select distinct year(stdate)

from tbl_employee

order by year(stdate) desc

Could you provide a little more info, i.e. what error messages are you receiving

Chris