Remove, but don't remove zeros.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Remove, but don't remove zeros.

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-03-30 : 16:46:27

Is there a slick one-liner that will remove leading zeros in this date:
08/03/05

But not the zeros in this date?

10/10/05

There must be a supreme SQL guru out there in this forum... who is it?

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-30 : 16:50:46

Well, Sam, you know formatting should always be done at the presentation layer, right?

but if you HAVE to do it in T-SQL, you could use:

declare @Value varchar(20);

set @Value = '04/10/2003'

select substring(replace('/' + @Value,'/0','/'),2,20)

I strongly recommend against it, of course. Return actual DATA with your SQL, not a bunch of pre-formatted varchar strings.

EDIT: scratch that, it won't work for 04/10/03 .... all the more reason to format at the presentation layer!
- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2005-03-30 : 16:54:59

This isn't presentation layer. On keyboard entry, users should not be entering the leading zeros. If they do, I've gotta remove 'em.

Yes, JavaScript or ASP could do the job, but that's another story.

The problem with the solution above is it removes the leading zero on the year '04/09/05'.

I like the addition / removal of the leading / though.

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2005-03-30 : 16:56:41

Not likely that many will have leading zero's in the year. It's a birthdate. They'd have to be 4 years old or younger, either that, or a century old...

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-30 : 16:57:38

try this:

select substring(replace('/' + Left(@Value,5),'/0','/'),2,20) + right(@Value,3)

The presentation layer IS the part of the app that all user interfacing goes through.
Why does this need to be done in T-SQL? why are you storing/parsing strings into dates and formatting them in the database layer? Have the web page accept the value, make sure it is a valid date, and then pass it to a stored procedure that accepts a datetime datatype. No formatting involved for the database at any point.

- Jeff

Kristen
Test

22859 Posts

Posted - 2005-04-08 : 13:35:36

declare @Value varchar(20)
set @Value = '004/10/2003'
SELECT REPLACE(REPLACE(REPLACE('x' + @Value, 'x00', ''), 'x0', ''), 'x', '')
set @Value = '04/10/2003'
SELECT REPLACE(REPLACE(REPLACE('x' + @Value, 'x00', ''), 'x0', ''), 'x', '')
set @Value = '4/10/2003'
SELECT REPLACE(REPLACE(REPLACE('x' + @Value, 'x00', ''), 'x0', ''), 'x', '')

Make sure that whatever you use for 'x' can't be in the data stream - use multiple X's if that helps

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources