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]

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

[David Rawheiser]

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" <in************ *@nibsworld.com > wrote in message
news:30******** *****@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