How to use TO_CHAR function functionality in SQL Server 2008

Question

I have a table EMP. I know how to use TO_CHAR function in TOAD but how to get same output if I use in SQL Server 2008. Is there any conversion function in SQL Server for TO_CHAR?

Please help me in writing a query in SQL Server.

Thanks in advance...

 select * 
 from emp 
 where to_char(hiredate, 'yy') like '82';

Output

     EMPNO  ENAME    JOB      MGR    HIREDATE       SAL   COMM   DEPTNO
     7934   MILLER   CLERK    7782   23-JAN-82     1300           10

Answer 1

Assuming that HIREDATE is either a DATE or DATETIME datatype, then you can do:

SELECT *
FROM emp
WHERE YEAR(HIREDATE) = 1982 -- I think that this is the year you want

Now, that code won't be able to use an index on HIREDATE if there exists one, so you could do:

SELECT *
FROM emp
WHERE HIREDATE >= '19820101' 
AND HIREDATE < '19830101'

On the other hand, if you actually want to use the LIKE '82' condition, meaning that you want results for 1882, 1982, 2082, .... then you can use:

SELECT *
FROM emp
WHERE DATENAME(YEAR,HIREDATE) LIKE '%82'