user1815210 user1815210 - 1 month ago 11
SQL Question

PostgreSQL refusing to only display just month and year

This is the original table

"facultyid","courseid","datequalified"
2143,"ISM 3112","9/1988"
2143,"ISM 3113","9/1988"
3467,"ISM 4212","9/1995"
3467,"ISM 4930","9/1996"
4756,"ISM 3112","9/1991"
4756,"ISM 3113","9/1991"

CREATE TABLE qualified (
facultyid integer NOT NULL,
courseid character varying(16) NOT NULL,
datequalified character varying(16),
dq2 date
);


ALTER TABLE qualified OWNER TO postgres;

--
-- TOC entry 2136 (class 0 OID 16441)
-- Dependencies: 190
-- Data for Name: qualified; Type: TABLE DATA; Schema: class_scheduling_01; Owner: postgres
--

INSERT INTO qualified (facultyid, courseid, datequalified, dq2) VALUES (3467, 'ISM 4212', '9/1995', '1995-09-01');
INSERT INTO qualified (facultyid, courseid, datequalified, dq2) VALUES (4756, 'ISM 3112', '9/1991', '1991-09-01');
INSERT INTO qualified (facultyid, courseid, datequalified, dq2) VALUES (2143, 'ISM 3113', '9/1988', '1988-09-01');
INSERT INTO qualified (facultyid, courseid, datequalified, dq2) VALUES (4756, 'ISM 3113', '9/1991', '1991-09-01');
INSERT INTO qualified (facultyid, courseid, datequalified, dq2) VALUES (2143, 'ISM 3112', '9/1988', '1988-09-01');
INSERT INTO qualified (facultyid, courseid, datequalified, dq2) VALUES (3467, 'ISM 4930', '9/1996', '1996-09-01');


I didn't know how to store the value, with just month and year, so I decided to use varchar type. From there I decided if I wanted to perform date query commands, I would have to convert it from a string to date. One query I'd like to perform is finding out which faculty ID is associated with dates after 1991.

I used the following to_date function to convert the string to date:
UPDATE qualified SET dq2 = to_date(datequalified, 'MM\YYYY')

the dq2 column was output as the following:

dq2
1995-09-01
1991-09-01
1988-09-01
1991-09-01
1988-09-01
1996-09-01


Is it possible to get dq2 output as just month and year, and still permit, date related queries?

Answer
SELECT to_char(your_date, 'YYYY-MM') AS month_format
FROM YourTable
Comments