Re: help with to_date and to_char

There might be a better way, but this should do what you want. And I
think that you can safely replace '05' with when_month.

select to_char( to_date( '05' || '/' || to_char( current_date,
'DD/YYYY' ), 'MM/DD/YYYY' ), 'MON' );

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Oct 12, 2004, at 7:20 PM, Lori wrote:

> Im trying to do something very simple
> I have a field called when_month (integer )
> so I want to get the month name for the integer
>
> this comes close to what I want
> update mytable set myfield=to_char(current_timestamp,'MON');
>
> the result is myfield is set to OCT
> which is close to what I want but when I try to use a variable
> update mytable set myfield=to_char(when_month,'MON');
> or a char with value of '05' or '5'
> update mytable set myfield=to_char('05','MON');
> get error:
> update mytable set myfield=to_date(when_month,'MON');
> update mytable set myfield=to_char('05','MON');
> as well as
> update mytable set full_month= to_date('01/'05'/2004','DDMONYYYY');
> all get errors
>
> Please can anyone help?
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: help with to_date and to_char

Thomas F.O'Connell wrote:
> There might be a better way, but this should do what you want. And I
> think that you can safely replace '05' with when_month.
>
> select to_char( to_date( '05' || '/' || to_char( current_date, 'DD/YYYY'
> ), 'MM/DD/YYYY' ), 'MON' );
>

Perhaps
select to_char(to_date('02', 'MM'), 'MON');

is better. When current_date is, say Aug 31 then

select to_char( to_date('05' || '/' || to_char(current_date, 'DD/YYYY'),
'MM/DD/YYYY' ), 'MON' );

returns 'MAR', which is probably not what you want.

This works with 7.3.2, 7.4.5 and 8.0beta2.


> -tfo
>
> --
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-260-0005
>
> On Oct 12, 2004, at 7:20 PM, Lori wrote:
>
>> Im trying to do something very simple
>> I have a field called when_month (integer )
>> so I want to get the month name for the integer
>>
>> this comes close to what I want
>> update mytable set myfield=to_char(current_timestamp,'MON');
>>
>> the result is myfield is set to OCT
>> which is close to what I want but when I try to use a variable
>> update mytable set myfield=to_char(when_month,'MON');
>> or a char with value of '05' or '5'
>> update mytable set myfield=to_char('05','MON');
>> get error:
>> update mytable set myfield=to_date(when_month,'MON');
>> update mytable set myfield=to_char('05','MON');
>> as well as
>> update mytable set full_month= to_date('01/'05'/2004','DDMONYYYY');
>> all get errors
>>
>> Please can anyone help?
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

--
Edmund Bacon

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: help with to_date and to_char

Edmund Bacon wrote:
> When current_date is, say Aug 31 then
>
> select to_char( to_date('05' || '/' || to_char(current_date, 'DD/YYYY'),
> 'MM/DD/YYYY' ), 'MON' );
>

NUTS! that should have been
select ... to_date('02' || ...

{ cut, paste, ?edit? }

> returns 'MAR', which is probably not what you want.
>
> This works with 7.3.2, 7.4.5 and 8.0beta2.
>
--
Edmund Bacon

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: help with to_date and to_char

Thanks Thomas it worked great ,even when I put in the var - when_month
:)
Lori

Thomas F.O'Connell wrote:

> There might be a better way, but this should do what you want. And I
> think that you can safely replace '05' with when_month.
>
> select to_char( to_date( '05' || '/' || to_char( current_date,
> 'DD/YYYY' ), 'MM/DD/YYYY' ), 'MON' );
>
> -tfo
>
> --
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-260-0005
>
> On Oct 12, 2004, at 7:20 PM, Lori wrote:
>
>> Im trying to do something very simple
>> I have a field called when_month (integer )
>> so I want to get the month name for the integer
>>
>> this comes close to what I want
>> update mytable set myfield=to_char(current_timestamp,'MON');
>>
>> the result is myfield is set to OCT
>> which is close to what I want but when I try to use a variable
>> update mytable set myfield=to_char(when_month,'MON');
>> or a char with value of '05' or '5'
>> update mytable set myfield=to_char('05','MON');
>> get error:
>> update mytable set myfield=to_date(when_month,'MON');
>> update mytable set myfield=to_char('05','MON');
>> as well as
>> update mytable set full_month= to_date('01/'05'/2004','DDMONYYYY');
>> all get errors
>>
>> Please can anyone help?
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Which type of functions are best?

Hi,

I'm just starting out and am looking to speed up queries using either
SQL functions or PLPGSQL functions. I have googled around and have not
found a great answer saying that this is the way to go. I would like to
use PREPARE/EXECUTE... but of course they only last for each connection,
I would like something more permanent.

Thanks for your input,
J

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: help with to_date and to_char

Yup, even better. For some reason I gave up trying to_date( '02', 'MON'
), which clearly wasn't working.

Thanks for the improvement!

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Oct 19, 2004, at 6:00 PM, Edmund Bacon wrote:

> Thomas F.O'Connell wrote:
>> There might be a better way, but this should do what you want. And I
>> think that you can safely replace '05' with when_month.
>> select to_char( to_date( '05' || '/' || to_char( current_date,
>> 'DD/YYYY' ), 'MM/DD/YYYY' ), 'MON' );
>
> Perhaps
> select to_char(to_date('02', 'MM'), 'MON');
>
> is better. When current_date is, say Aug 31 then
>
> select to_char( to_date('05' || '/' || to_char(current_date,
> 'DD/YYYY'),
> 'MM/DD/YYYY' ), 'MON' );
>
> returns 'MAR', which is probably not what you want.
>
> This works with 7.3.2, 7.4.5 and 8.0beta2.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org