469,936 Members | 2,444 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,936 developers. It's quick & easy.

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

Jul 20 '05 #1
2 4395
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

Jul 20 '05 #2
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" <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

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by david | last post: by
5 posts views Thread by GarryJones | last post: by
6 posts views Thread by Marko | last post: by
8 posts views Thread by Andrew Poulos | last post: by
6 posts views Thread by JimmyKoolPantz | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.