Casting Date/Time as string, padding empty chars with 0.

Question

Casting Date/Time as string, padding empty chars with 0.

Viewing 7 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 1

Try

Select Stuff(Stuff(Replace(Replace(Convert(Char(23),GetDate(),126),'-',''),':',''),9,1,''),15,1,'')

Select Stuff(Stuff(Replace(Replace(Convert(Char(23),Cast('1 jan 1990 13:14:15.123' as datetime),126),'-',''),':',''),9,1,''),15,1,'')

Phil Parkin SSC Guru Points: 247183 More actions · Answer 2

Hi Val

It seems that your original statement is nearly giving you what you want, except that you have spaces where you want zeroes to appear. So can you just use

replace([expression], ' ', '0')

to fix it up?

Regards

Phil

anne-135297 SSC Rookie Points: 26 More actions · Answer 3

anne-135297

SSC Rookie

Points: 26

August 18, 2004 at 9:16 pm

#519685

Try select convert(varchar(9),getdate(),114)

Phil Parkin SSC Guru Points: 247183 More actions · Answer 4

Anne's nearly got it.

Try this:

select replace(convert(varchar(12),getdate(),114),':','')

Regards

Phil

Bill Geake Ten Centuries Points: 1352 More actions · Answer 5

Doesn't this just show up a weakness of SQL Server. Compared to Oracle's ToDate or VB's Format, this is all a bit complex for a pretty common task. I know, I know, use a function.

Bill

NB I can put more than 2 heresies in a single post: Select * is cool, primary keys are for retentives only and comments are for losers

AKM SSCommitted Points: 1538 More actions · Answer 6

Tell you what Bill, you write a T-SQL UDF that implements VB's Format function, and I'm sure you will get a special prize

Leah Kats SSC Veteran Points: 271 More actions · Answer 7

You can use Replicate function. Look into BOL

Leah Kats