How to use TO_CHAR function functionality in SQL Server 2008

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

Answers


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'

In SQL Server 2008 all explicit conversions are done with CAST and CONVERT. See here for the doc.

For your explicit case, I would do it like this:

SELECT * FROM emp WHERE RIGHT(CONVERT(varchar(4), hiredate, 121),2) like '82';

(well, actually, I would probably use the YEAR function and keep it numeric instead, but if I really needed it to be as a string, then like this)


Many different ways. Here are two from me.

-- If a string, just select right two
declare @temp1 varchar(9) = '23-JAN-82'
select right(@temp1, 2) as yy;


-- If a date, just use japan format
declare @temp2 datetime = '23-JAN-82'
select convert(char(2), @temp2, 11) as yy

Need Your Help

Has any one out there in the field used Dispatcher.Yield?

wpf multithreading

In "Lucian Wischik - Async Part 2 -- deep dive into the new language feature of VB/C#" for NDC 2012, the recommended use of Dispatcher.Yield is introduced to me. Does anyone out there have examples...

Convert HTML to PDF in ASP.NET MVC

asp.net-mvc pdf-conversion

Im working in a project which requires current html page to convert in pdf and that pdf will automatically save on button click on server and its reference will be save in database.I can convert...