Help with select

Hi
I have a table with year and date values
create table year_mon
(year char (6),
mon char(2)
)
insert into year_mon values ('2003',12)
insert into year_mon values ('2004',12)
insert into year_mon values ('2005',12)
insert into year_mon values ('2003',3)
insert into year_mon values ('2003',6)
insert into year_mon values ('2003',9)
insert into year_mon values ('2004',3)
insert into year_mon values ('2004',6)
How can I insert a 0 value before the mon where mon is 3,6 or 9
select year, mon from year_mon
go
gives me
2003 12
2004 12
2005 12
2003 3 --> would like the values to be displayed as 2003 03
2003 6 --> would like the values to be displayed as 2003 06
2003 9 --> would like the values to be displayed as 2003 09
etc ...
AHi,
Try the below statement
select year, right(('0'+ltrim(rtrim(mon))),2) from year_mon
Thanks
Hari
SQL Server MVP
"ajmister" <ajmister@.optonline.net> wrote in message
news:%23hNQxFwWFHA.2700@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have a table with year and date values
> create table year_mon
> (year char (6),
> mon char(2)
> )
> insert into year_mon values ('2003',12)
> insert into year_mon values ('2004',12)
> insert into year_mon values ('2005',12)
> insert into year_mon values ('2003',3)
> insert into year_mon values ('2003',6)
> insert into year_mon values ('2003',9)
> insert into year_mon values ('2004',3)
> insert into year_mon values ('2004',6)
> How can I insert a 0 value before the mon where mon is 3,6 or 9
> select year, mon from year_mon
> go
> gives me
> 2003 12
> 2004 12
> 2005 12
> 2003 3 --> would like the values to be displayed as 2003 03
> 2003 6 --> would like the values to be displayed as 2003 06
> 2003 9 --> would like the values to be displayed as 2003 09
> etc ...
> A
>|||Try,
update year_mon
set mon = '0' + ltrim(cast(month as int))
where len(month) = 1;
AMB
"ajmister" wrote:

> Hi
> I have a table with year and date values
> create table year_mon
> (year char (6),
> mon char(2)
> )
> insert into year_mon values ('2003',12)
> insert into year_mon values ('2004',12)
> insert into year_mon values ('2005',12)
> insert into year_mon values ('2003',3)
> insert into year_mon values ('2003',6)
> insert into year_mon values ('2003',9)
> insert into year_mon values ('2004',3)
> insert into year_mon values ('2004',6)
> How can I insert a 0 value before the mon where mon is 3,6 or 9
> select year, mon from year_mon
> go
> gives me
> 2003 12
> 2004 12
> 2005 12
> 2003 3 --> would like the values to be displayed as 2003 03
> 2003 6 --> would like the values to be displayed as 2003 06
> 2003 9 --> would like the values to be displayed as 2003 09
> etc ...
> A
>
>|||Hi
There are several ways, this is one
select year, right('0'+rtrim(MON),2) from year_mon
go
John
"ajmister" wrote:

> Hi
> I have a table with year and date values
> create table year_mon
> (year char (6),
> mon char(2)
> )
> insert into year_mon values ('2003',12)
> insert into year_mon values ('2004',12)
> insert into year_mon values ('2005',12)
> insert into year_mon values ('2003',3)
> insert into year_mon values ('2003',6)
> insert into year_mon values ('2003',9)
> insert into year_mon values ('2004',3)
> insert into year_mon values ('2004',6)
> How can I insert a 0 value before the mon where mon is 3,6 or 9
> select year, mon from year_mon
> go
> gives me
> 2003 12
> 2004 12
> 2005 12
> 2003 3 --> would like the values to be displayed as 2003 03
> 2003 6 --> would like the values to be displayed as 2003 06
> 2003 9 --> would like the values to be displayed as 2003 09
> etc ...
> A
>
>|||Sorry.
select [year], right('0' + ltrim(cast(month as int)), 2) as [month]
from year_mon
AMB
"Alejandro Mesa" wrote:
> Try,
> update year_mon
> set mon = '0' + ltrim(cast(month as int))
> where len(month) = 1;
>
> AMB
> "ajmister" wrote:
>|||Thank you all. I was able to get the output using
select year,
right("0" + convert(varchar(2),mon) as mon.
from year_mon
Aj
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BAA3A38D-E203-4D5D-B39E-C97330F83F84@.microsoft.com...
> Sorry.
> select [year], right('0' + ltrim(cast(month as int)), 2) as [month]
> from year_mon
>
> AMB
> "Alejandro Mesa" wrote:
>|||You have missed the point of SQL.
The language has temporal data types, so you use them for temporal
data. Look up the concept of proper domains for data. This is not
COBOL any more; we do not use strings and numerics for this. The
second fundamental thing that you missed is that time is always modeled
as durations. The third thing is that in a tiered architecture display
and formatting are never done in the database, but belongs in the front
end.
CREATE TABLE MonthlyCalendar
(year_month CHAR (7) NOT NULL PRIMARY KEY,
month_start_date DATETIME NOT NULL,
month_end_date DATETIME NOT NULL
CHECK (month_start_date < month_end_date));|||> The
> second fundamental thing that you missed is that time is always modeled
> as durations.
I disagree. According to Snodgrass (Developing Time-Oriented Database
Applications in SQL), time data types include Instance, Interval and Period.
His
storage could be commensurate with an Interval (e.g. March 2005, May 2004).
Granted, even intervals of this nature can be stored using standard DateTime
data types.

> The third thing is that in a tiered architecture display
> and formatting are never done in the database, but belongs in the front
> end.
Agreed. The reporting engine should be doing the formatting.
Thomassql