CODEWITHSUNDEEP
sql-serversql-server-2008
user3141052
user3141052

Reputation: 31

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

Upvotes: 3

Views: 43952

Answers (3)

CRAFTY DBA
CRAFTY DBA

Reputation: 14925

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

Upvotes: 1

Lamak
Lamak

Reputation: 70668

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'

Upvotes: 6

RBarryYoung
RBarryYoung

Reputation: 56755

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)

Upvotes: 2

Related Questions