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';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7934 MILLER CLERK 7782 23-JAN-82 1300 10
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