add leading zero to date column??

[Myk]

Hello All,

None of the solutions I have found in the archives seem to solve my
problem. I have a date column in my tables (stored as a char(10))
which I would like to append a leading zero to for those dates that
start with 9 or lower.

Any ideas?

Thanks,

Mike

[nib]

Re: add leading zero to date column??

Myk wrote:[color=blue]
> Hello All,
>
> None of the solutions I have found in the archives seem to solve my
> problem. I have a date column in my tables (stored as a char(10))[/color]

No, you have a char() column in your table that you store a string in
that is supposed to represent a date. It may or may not.
[color=blue]
> which I would like to append a leading zero to for those dates that
> start with 9 or lower.[/color]

I'd recommend you actually make the column a date data type since that's
what it is for, but you can append like this:

CASE WHEN LEFT(YourColumn , 1) BETWEEN '1' AND '9' THEN '0' + YourColumn END

Now, there are tons of things wrong with what I just wrote in that it
assumes the first column will always be 0-9, which, given your specs
above, isn't garunteed. It assumes that there are no spaces in the first
character position. It assumes that if there is a 1 through 9 that the
length of that string plus the new '0' is still within 10 chars. All in
all, it is a crappy solution.

Make your data column type match your data and this problem goes away.

Zach
[color=blue]
>
> Any ideas?
>
> Thanks,
>
> Mike
>[/color]

[David Rawheiser]

If you must ...

Database Modeling Sin # 58 - Confusing Data Presentation with Data
Representation.

If you can't convert the column to its proper datatype (highly , greatly,
strongly, and "i mean it dude!" recommended),
add the following to a trigger on the table to insure that the format of the
string is consistent.

update mytable
set DateLikeCharCol umn = IsNull( convert( char(10) , convert( datetime,
DateLikeCharCol umn ) , 110 ) , "" )
from mytable
join inserted on mytable.keycolu mn = inserted.keycol umn

(substitute 110 with what ever style you need)



"nib" <individual_new s@nibsworld.com > wrote in message
news:304ot7F2qr binU1@uni-berlin.de...[color=blue]
> Myk wrote:[color=green]
>> Hello All,
>>
>> None of the solutions I have found in the archives seem to solve my
>> problem. I have a date column in my tables (stored as a char(10))[/color]
>
> No, you have a char() column in your table that you store a string in that
> is supposed to represent a date. It may or may not.
>[color=green]
>> which I would like to append a leading zero to for those dates that
>> start with 9 or lower.[/color]
>
> I'd recommend you actually make the column a date data type since that's
> what it is for, but you can append like this:
>
> CASE WHEN LEFT(YourColumn , 1) BETWEEN '1' AND '9' THEN '0' + YourColumn
> END
>
> Now, there are tons of things wrong with what I just wrote in that it
> assumes the first column will always be 0-9, which, given your specs
> above, isn't garunteed. It assumes that there are no spaces in the first
> character position. It assumes that if there is a 1 through 9 that the
> length of that string plus the new '0' is still within 10 chars. All in
> all, it is a crappy solution.
>
> Make your data column type match your data and this problem goes away.
>
> Zach
>[color=green]
>>
>> Any ideas?
>>
>> Thanks,
>>
>> Mike
>>[/color][/color]