Thread: help with to_date and to_char

help with to_date and to_char

From

Lori

Date:

20 October 2004, 01:03:34

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?

Re: help with to_date and to_char

From

Thomas F.O'Connell

Date:

20 October 2004, 01:28:59

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

Re: help with to_date and to_char

From

Edmund Bacon

Date:

20 October 2004, 03:00:26

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 <ebacon@onesystem.com>

Re: help with to_date and to_char

From

lorid

Date:

20 October 2004, 03:01:20

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

Re: help with to_date and to_char

From

Edmund Bacon

Date:

20 October 2004, 03:05:37

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 <ebacon@onesystem.com>

Which type of functions are best?

From

Postgres User

Date:

20 October 2004, 06:40:50

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

Re: help with to_date and to_char

From

Thomas F.O'Connell

Date:

20 October 2004, 07:29:55

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.