Thread: mapping date value (SQL question)

mapping date value (SQL question)

From

"Johnson, Shaunn"

Date:

21 February 2002, 11:52:02

Howdy:

I'm running Postgres 7.1.3 on RedHat Linux 7.2, kernel version
2.4.7 rel 10.

I've created a table which includes the date. I have been instructed
to map the date into something that reflects the quarter of the year
on the fly (somewhere in the script). For example:

(currently) date:               (needed) quarter:
2001-02-26                      200101
1998-05-12                      199802
803-11-11                       80304

I was thinking I'd have to use something like a case statement, but
I don't know enough about SQL to do something like that. Somehow, I
had the notion that if it were possible to write 'if-then-else' loops
I could probably do it.

Is there such a mechanism to do what is being asked of me?

Thanks!

-X

Re: mapping date value (SQL question)

From

Darren Ferguson

Date:

21 February 2002, 12:30:50

You have in plpgsql the ability to use IF THEN ELSIF ELSE

Also in pure SQL you can use the CASE WHEN THEN END

Example: Case Statement

CASE WHEN status = 1 THEN Draft
     WHEN status = 2 THEN Open
     WHEN status = 3 THEN Complete
     WHEN status = 4 THEN Closed
ELSE undefined
End AS status

Example: PLPGSQL

    IF subject = ''Location'' THEN
       /* Code goes here */
    ELSIF subject = ''Device'' THEN
       /* Code goes here */
    ELSE
       /* Code goes here */
    END IF;

Hope this helps

Darren Ferguson

On Thu, 21 Feb 2002, Johnson, Shaunn wrote:

> Howdy:
>
> I'm running Postgres 7.1.3 on RedHat Linux 7.2, kernel version
> 2.4.7 rel 10.
>
> I've created a table which includes the date.  I have been instructed
> to map the date into something that reflects the quarter of the year
> on the fly (somewhere in the script).  For example:
>
> (currently) date:        (needed) quarter:
> 2001-02-26            200101
> 1998-05-12            199802
> 803-11-11            80304
>
> I was thinking I'd have to use something like a case statement, but
> I don't know enough about SQL to do something like that.  Somehow, I
> had the notion that if it were possible to write 'if-then-else' loops
> I could probably do it.
>
> Is there such a mechanism to do what is being asked of me?
>
> Thanks!
>
> -X
>

Re: mapping date value (SQL question)

From

"Johnson, Shaunn"

Date:

21 February 2002, 12:32:00

--that's close. but i need the '0' in the field between the

year and the quarter.

--all of this because a company want's fixed length columns.

--i was reading about something like this:

[example]

select to_char(to_date('05/21/1998'),'Q') from dual

[/example]

--trying to modify it somehow ...

--thanks!

-X

-----Original Message-----
From: Mancz, James

How about:

SELECT date_part('y', datefield) || date_part('qtr', datefield)
FROM tablename;
-----Original Message-----
From: Johnson, Shaunn

Howdy:
I'm running Postgres 7.1.3 on RedHat Linux 7.2, kernel version
2.4.7 rel 10.
I've created a table which includes the date. I have been instructed
to map the date into something that reflects the quarter of the year
on the fly (somewhere in the script). For example:
(currently) date:               (needed) quarter:
2001-02-26                      200101
1998-05-12                      199802
803-11-11                       80304
I was thinking I'd have to use something like a case statement, but
I don't know enough about SQL to do something like that. Somehow, I
had the notion that if it were possible to write 'if-then-else' loops
I could probably do it.
Is there such a mechanism to do what is being asked of me?
Thanks!
-X

Re: mapping date value (SQL question)

From

"Mancz, James"

Date:

21 February 2002, 12:38:00

If you want the leading zero, modify it to be like this;

SELECT date_part('y', datefield) || '0' || date_part('qtr', datefield)
FROM tablename;

-----Original Message-----
From: Johnson, Shaunn [mailto:SJohnson6@bcbsm.com]
Sent: 21 February 2002 14:32
To: 'Mancz, James'; 'pgsql-general@postgresql.org'
Subject: RE: [GENERAL] mapping date value (SQL question)


--that's close.  but i need the '0' in the field between the
year and the quarter.

--all of this because a company want's fixed length columns.

--i was reading about something like this:

[example]

select to_char(to_date('05/21/1998'),'Q') from dual

[/example]

--trying to modify it somehow ...

--thanks!

-X

-----Original Message-----
From: Mancz, James


How about:

SELECT date_part('y', datefield) || date_part('qtr', datefield)
FROM tablename;
-----Original Message-----
From: Johnson, Shaunn


Howdy:
I'm running Postgres 7.1.3 on RedHat Linux 7.2, kernel version
2.4.7 rel 10.
I've created a table which includes the date.  I have been instructed
to map the date into something that reflects the quarter of the year
on the fly (somewhere in the script).  For example:
(currently) date:               (needed) quarter:
2001-02-26                      200101
1998-05-12                      199802
803-11-11                       80304
I was thinking I'd have to use something like a case statement, but
I don't know enough about SQL to do something like that.  Somehow, I
had the notion that if it were possible to write 'if-then-else' loops
I could probably do it.
Is there such a mechanism to do what is being asked of me?
Thanks!
-X

Re: mapping date value (SQL question)

From

Jeff Self

Date:

21 February 2002, 12:39:22

On Thu, 2002-02-21 at 09:31, Johnson, Shaunn wrote:
> --that's close.  but i need the '0' in the field between the
> year and the quarter.
>
> --all of this because a company want's fixed length columns.
>
> --i was reading about something like this:
>
> [example]
>
> select to_char(to_date('05/21/1998'),'Q') from dual
>
> [/example]
>
> --trying to modify it somehow ...
>
> --thanks!
>
> -X

Are you talking about showing the quarter as 01 or 02 instead of 1 or 2?
If so, just convert the quarter to a character using casting I suppose
and insert the 0 in front of it. What are you using to provide the
reports? It would probably be a lot easier to let those tools do the
work for you. Perl or PHP could easily make the quarter look like 01 or
02.

--
Jeff Self
Information Technology Analyst
Department of Personnel
City of Newport News
2400 Washington Ave.
Newport News, VA 23607
757-926-6930

Re: mapping date value (SQL question)

From

Thomas Lockhart

Date:

21 February 2002, 13:19:07

> "Johnson, Shaunn" wrote:
> I've created a table which includes the date.  I have been instructed
> to map the date into something that reflects the quarter of the year
> on the fly (somewhere in the script).  For example:
> (currently) date:               (needed) quarter:
> 2001-02-26                      200101
> 1998-05-12                      199802
> 803-11-11                       80304
> Is there such a mechanism to do what is being asked of me?

select date_part('quarter', date 'now');

gets you the current quarter, and

select date_part('year', date 'now') || date_part('quarter', date
'now');

gets you both year and quarter.

However, you may want to use the to_char() functionality to get more
control over the formatting:

select to_char(date 'now', 'YYYY0Q');

Good luck!

                   - Thomas