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

Question

Post reply

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

Val Akkapeddi

SSC Enthusiast

Points: 122
More actions
August 18, 2004 at 1:16 pm

#86325

Hi,
I need to retrieve the time part of a Date/Time field in SQL Server 2000 as a 9-byte string. This is what it needs to exactly look like: hhmmssmss.
This is the statement I am writing in order to retrieve this:
CAST((DATEPART(hh,DATETIMEVAL) as char(2)) +
CAST((DATEPART(mi,DATETIMEVAL) as char(2)) +
CAST((DATEPART(ss,DATETIMEVAL) as char(2)) +
CAST((DATEPART(mi,DATETIMEVAL) as char(3))
That gives me something like what I want, but when the return values are in single digits ( for example, 06 hrs, 000 ms), it is only giving me 6 for hours and 0 for ms, and leaving the other chars blank. For example, when I want 162205003, I am getting something like 1622 5 3.
Bottom line:
How do I either pad the chars with zeros when the values are smaller?

Viewing 8 posts - 1 through 7 (of 7 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: 246716 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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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: 246716 More actions · Answer 4

Anne's nearly got it.

Try this:

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

Regards

Phil

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Bill Geake Ten Centuries Points: 1292 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