• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

SQL syntax for toChar year


I need to add some fields to this sql to break the date field into separate fields for month, day and year. I want hem to be character. I tried  to_char("ESHPOSHD"."PHST_CHG_DATE",'YYYY') as chg_yr It does not like that. I tried to find the correct syntax for this but so far no luck.

SELECT DISTINCT
 "ESYPOSTR"."TITLE",
 "ESYINFOR"."DESCRIPTION",
 "ESYINFOR"."INFO_TYPE",
 "ESHPOSHD"."PHST_CHG_DATE",
 "ESHSHSTD"."SHST_OLD_SALARY",
 "ESHSHSTD"."SHST_OLD_HRLY_RATE",
 "ESHPOSHD"."PHST_DESCRIPTION",
 "ESHPOSHD"."PHST_ID",
 "ESYAPPTR"."CYCLE_CODE",
 "ESYEMPLR"."EMP_NO",
 "ESYAPPTR"."STATUS",
 "ESYEMPLR"."EMPLOYMENT_STATUS",
 "ESYEMPLR"."FNAME",
 "ESYEMPLR"."cur_hire_date",
 "ESYEMPLR"."HIRE_DATE",
 "ESYEMPLR"."TERM_DATE",
 "ESYEMPLR"."HOME_DEPT",
 "ESYEMPLR"."LNAME",
 "ESYEMPLR"."SSN",
 "ESYAPPTR"."SALARY",
 "ESYAPPTR"."HOURLY_RATE",
 "ESYEMPLR"."mname",
 "ESHSHSTD"."SHST_ID",
 "ESHPOSHD"."PHST_OLD_POS_NO",
 to_char("ESHPOSHD"."PHST_CHG_DATE",'YYYY') as chg_yr
 
 
 FROM   (((("EDENLive"."dbo"."ESYEMPLR" "ESYEMPLR"
 LEFT OUTER JOIN "EDENLive"."dbo"."ESYAPPTR" "ESYAPPTR"
 ON "ESYEMPLR"."EMP_ID"="ESYAPPTR"."EMP_ID")
 LEFT OUTER JOIN "EDENLive"."dbo"."ESYINFOR" "ESYINFOR"
 ON "ESYEMPLR"."EMPLOYMENT_STATUS"="ESYINFOR"."INFO_KEY")
 LEFT OUTER JOIN "EDENLive"."dbo"."ESHPOSHD" "ESHPOSHD"
 ON "ESYAPPTR"."EMP_ID"="ESHPOSHD"."PHST_EMP_ID")
 LEFT OUTER JOIN "EDENLive"."dbo"."ESYPOSTR" "ESYPOSTR"
 ON "ESYAPPTR"."POS_ID"="ESYPOSTR"."POS_ID")
 LEFT OUTER JOIN "EDENLive"."dbo"."ESHSHSTD" "ESHSHSTD"
 ON "ESHPOSHD"."PHST_EMP_ID"="ESHSHSTD"."SHST_EMP_ID"
 
 
 WHERE  "ESYAPPTR"."CYCLE_CODE"='b'
 AND "ESYEMPLR"."cur_hire_date"<{ts '2010-09-30 00:00:01'}
 AND "ESYEMPLR"."EMP_NO" LIKE 'E%'
 AND "ESYINFOR"."INFO_TYPE"='es'
0
qbjgqbjg
Asked:
qbjgqbjg
  • 3
  • 3
1 Solution
 
Lee SavidgeCommented:
cast(ESHPOSHD.PHST_CHG_DATE as nvarchar(4))

Lee
0
 
qbjgqbjgConsultantAuthor Commented:
Will that give me the year? I need to name it. How about month and day?
0
 
bchoorCommented:
YEAR: CAST(DATEPART(yy, ESHPOSHD.PHST_CHG_DATE) AS varchar(4))
MONTH: CAST(DATEPART(m, ESHPOSHD.PHST_CHG_DATE) AS varchar(2))
DAY: CAST(DATEPART(d, ESHPOSHD.PHST_CHG_DATE) AS varchar(2))

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
qbjgqbjgConsultantAuthor Commented:
I need the number '2010' as character format, but I need it to be 2010, not letters. I tried your suggestion it gave me Nov, Dec, etc. For a date such as 01/20/2010, I need 3 fields, with 01 20 and 2010.
0
 
bchoorCommented:
did u try the datepart function? without the cast - you'll just get the numbers. Unless I'm not understanding what you're looking for.
0
 
bchoorCommented:
when I do this:
PRINT CAST(DATEPART(yy, GETDATE()) AS varchar(4))
PRINT CAST(DATEPART(m, GETDATE()) AS varchar(2))
PRINT CAST(DATEPART(d, GETDATE()) AS varchar(2))
PRINT ''
PRINT DATEPART(yy, GETDATE())
PRINT DATEPART(m, GETDATE())
PRINT DATEPART(d, GETDATE())

Result:
2010
4
23
 
2010
4
23


the first set of results is varchars, the second set of results is integers
0
 
qbjgqbjgConsultantAuthor Commented:
Thanks so much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now