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: 152
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: 246986 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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

Anne's nearly got it.

Try this:

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

Regards

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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