Link to home
Start Free TrialLog in
Avatar of qbjgqbjg
qbjgqbjgFlag for United States of America

asked on

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'
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

cast(ESHPOSHD.PHST_CHG_DATE as nvarchar(4))

Lee
Avatar of qbjgqbjg

ASKER

Will that give me the year? I need to name it. How about month and day?
ASKER CERTIFIED SOLUTION
Avatar of bchoor
bchoor
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
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
Thanks so much