add leading zero to date column?

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,

MikeMyk wrote:
> 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))

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.

> which I would like to append a leading zero to for those dates that
> start with 9 or lower.

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

> Any ideas?
> Thanks,
> Mike|||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 DateLikeCharColumn = IsNull( convert( char(10) , convert( datetime,
DateLikeCharColumn ) , 110 ) , "" )
from mytable
join inserted on mytable.keycolumn = inserted.keycolumn

(substitute 110 with what ever style you need)

"nib" <individual_news@.nibsworld.com> wrote in message
news:304ot7F2qrbinU1@.uni-berlin.de...
> Myk wrote:
>> 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))
> 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.
>> which I would like to append a leading zero to for those dates that
>> start with 9 or lower.
> 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
>>
>> Any ideas?
>>
>> Thanks,
>>
>> Mike
>