qbjgqbjg
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_C
SELECT DISTINCT
"ESYPOSTR"."TITLE",
"ESYINFOR"."DESCRIPTION",
"ESYINFOR"."INFO_TYPE",
"ESHPOSHD"."PHST_CHG_DATE"
"ESHSHSTD"."SHST_OLD_SALAR
"ESHSHSTD"."SHST_OLD_HRLY_
"ESHPOSHD"."PHST_DESCRIPTI
"ESHPOSHD"."PHST_ID",
"ESYAPPTR"."CYCLE_CODE",
"ESYEMPLR"."EMP_NO",
"ESYAPPTR"."STATUS",
"ESYEMPLR"."EMPLOYMENT_STA
"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_N
to_char("ESHPOSHD"."PHST_C
FROM (((("EDENLive"."dbo"."ESYE
LEFT OUTER JOIN "EDENLive"."dbo"."ESYAPPTR
ON "ESYEMPLR"."EMP_ID"="ESYAP
LEFT OUTER JOIN "EDENLive"."dbo"."ESYINFOR
ON "ESYEMPLR"."EMPLOYMENT_STA
LEFT OUTER JOIN "EDENLive"."dbo"."ESHPOSHD
ON "ESYAPPTR"."EMP_ID"="ESHPO
LEFT OUTER JOIN "EDENLive"."dbo"."ESYPOSTR
ON "ESYAPPTR"."POS_ID"="ESYPO
LEFT OUTER JOIN "EDENLive"."dbo"."ESHSHSTD
ON "ESHPOSHD"."PHST_EMP_ID"="
WHERE "ESYAPPTR"."CYCLE_CODE"='b
AND "ESYEMPLR"."cur_hire_date"
AND "ESYEMPLR"."EMP_NO" LIKE 'E%'
AND "ESYINFOR"."INFO_TYPE"='es
ASKER
Will that give me the year? I need to name it. How about month and day?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Thanks so much
Lee