Help with select

17 May 2005 4:56 PM

ajmister

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

17 May 2005 5:13 PM

Hari Prasad

Hi,

Try the below statement

select year, right(('0'+ltrim(rtrim(mon))),2) from year_mon

Thanks
Hari
SQL Server MVP

Show quote

"ajmister" <ajmis***@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
>

17 May 2005 5:52 PM

Alejandro Mesa

Try,

update year_mon
set mon = '0' + ltrim(cast(month as int))
where len(month) = 1;

AMB

Show quote

"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
>
>
>

17 May 2005 6:13 PM

Alejandro Mesa

Sorry.

select [year], right('0' + ltrim(cast(month as int)), 2) as [month]
from year_mon

AMB

Show quote

"Alejandro Mesa" wrote:

> 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
> >
> >
> >

17 May 2005 7:06 PM

ajmister

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

Show quote

"Alejandro Mesa" <AlejandroM***@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:
>
>> 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
>> >
>> >
>> >

17 May 2005 5:54 PM

John Bell

Hi

There are several ways, this is one

select year, right('0'+rtrim(MON),2) from year_mon
go

John

Show quote

"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
>
>
>

17 May 2005 7:33 PM

--CELKO--

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));

17 May 2005 7:53 PM

Thomas Coleman

> 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.

Thomas