pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp with to_date and to_char
6+ messages / 4 participants
[nested] [flat]
* help with to_date and to_char
@ 2004-10-13 00:20 Lori <lorid@dri.edu>
0 siblings, 1 reply; 6+ messages in thread
From: Lori @ 2004-10-13 00:20 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: help with to_date and to_char
@ 2004-10-19 21:27 Thomas F.O'Connell <tfo@sitening.com>
parent: Lori <lorid@dri.edu>
0 siblings, 2 replies; 6+ messages in thread
From: Thomas F.O'Connell @ 2004-10-19 21:27 UTC (permalink / raw)
To: Lori <lorid@dri.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: help with to_date and to_char
@ 2004-10-19 23:00 Edmund Bacon <ebacon@onesystem.com>
parent: Thomas F.O'Connell <tfo@sitening.com>
1 sibling, 2 replies; 6+ messages in thread
From: Edmund Bacon @ 2004-10-19 23:00 UTC (permalink / raw)
To: pgsql-sql
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>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: help with to_date and to_char
@ 2004-10-19 23:05 Edmund Bacon <ebacon@onesystem.com>
parent: Edmund Bacon <ebacon@onesystem.com>
1 sibling, 0 replies; 6+ messages in thread
From: Edmund Bacon @ 2004-10-19 23:05 UTC (permalink / raw)
To: pgsql-sql
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>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: help with to_date and to_char
@ 2004-10-19 23:08 lorid <lorid@dri.edu>
parent: Thomas F.O'Connell <tfo@sitening.com>
1 sibling, 0 replies; 6+ messages in thread
From: lorid @ 2004-10-19 23:08 UTC (permalink / raw)
To: Thomas F.O'Connell <tfo@sitening.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: help with to_date and to_char
@ 2004-10-20 03:29 Thomas F.O'Connell <tfo@sitening.com>
parent: Edmund Bacon <ebacon@onesystem.com>
1 sibling, 0 replies; 6+ messages in thread
From: Thomas F.O'Connell @ 2004-10-20 03:29 UTC (permalink / raw)
To: Edmund Bacon <ebacon@onesystem.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-10-20 03:29 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-13 00:20 help with to_date and to_char Lori <lorid@dri.edu>
2004-10-19 21:27 ` Thomas F.O'Connell <tfo@sitening.com>
2004-10-19 23:00 ` Edmund Bacon <ebacon@onesystem.com>
2004-10-19 23:05 ` Edmund Bacon <ebacon@onesystem.com>
2004-10-20 03:29 ` Thomas F.O'Connell <tfo@sitening.com>
2004-10-19 23:08 ` lorid <lorid@dri.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox