Please help me see what is happening in datetime string manipulation

Question

Post reply

Please help me see what is happening in datetime string manipulation

Sue-245992

SSC Enthusiast

Points: 100
More actions
November 28, 2008 at 7:57 am

#124697

When I pull the dateparts for minutes and seconds and concatenate, everything is fine. However, when I concatenate hours with minutes there is a space in between them. Can someone PLEASE explain?
DECLARE
@RECORDCOUNT varchar(7),
@CHEKSUMRCDS int,
@CHEKSUMIRNS int,
@RECORDSTATUS Char(1),
@SORTTYPE Char(2),
@RECORDFORMATINDICATOR char(1),
@FISCALYEAR Char(4),
@Now datetime,
@REPORTINGPERIOD Char(1),
@DISTRICTIRN Char(6)
,@strIRNCkSm varchar(10)
SELECT
@Now = getdate()
SELECT
-- two digit hour
replicate('0', 2 - len(convert(char(2),datepart(hh,@Now)))) + convert(char(2),datepart(hh,@Now))
-- two digit minute
, replicate('0', 2 - len(convert(char(2),datepart(mi,@Now)))) + convert(char(2),datepart(mi,@Now))
-- length of two digit hour
, len(replicate('0', 2 - len(convert(char(2),datepart(hh,@Now)))) + convert(char(2),datepart(hh,@Now)))
-- length of two digit minute
, len( replicate('0', 2 - len(convert(char(2),datepart(mi,@Now)))) + convert(char(2),datepart(mi,@Now)) )
-- concatenate hour and minute
, replicate('0', 2 - len(convert(char(2),datepart(hh,@Now)))) + convert(char(2),datepart(hh,@Now))
+ replicate('0', 2 - len(convert(char(2),datepart(mi,@Now)))) + convert(char(2),datepart(mi,@Now))
-- length of contcatenated hour and minute
, len(replicate('0', 2 - len(convert(char(2),datepart(hh,@Now)))) + convert(char(2),datepart(hh,@Now))
+ replicate('0', 2 - len(convert(char(2),datepart(mi,@Now)))) + convert(char(2),datepart(mi,@Now)) )
TIA :hehe:

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

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Not sure what your question is. When I run your strings, I get this:

10 142210144

What are you getting?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Sue-245992 SSC Enthusiast Points: 100 More actions · Answer 2

Sue-245992

SSC Enthusiast

Points: 100

November 28, 2008 at 8:20 am

#904823

09 45 2 2 09 45 5

is what I am getting.

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Interesting. Not sure what local settings would cause something like that. You could probably get rid of it with a REPLACE(x,' ',''), but that seems a bit wasteful.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Sue-245992 SSC Enthusiast Points: 100 More actions · Answer 4

I have replaced the whole concatenation thing with the following very simple code:

replace(convert(char(8), @Now, 108), ':', '') AS FileTime

BUT... the anamoly is driving me nuts none the less. I was hoping I had overlooked something and someone could point it out to me.

🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Sue (11/28/2008)
09 45 2 2 09 45 5
is what I am getting.

It falls over with single-digit times. Try casting the hours portion to VARCHAR instead:

SELECT

@Now = '2008-11-28 09:45:57.783' --getdate()

SELECT

-- two digit hour

replicate('0', 2 - len(convert(char(2),datepart(hh,@Now)))) + convert(char(2),datepart(hh,@Now))

-- two digit minute

, replicate('0', 2 - len(convert(char(2),datepart(mi,@Now)))) + convert(char(2),datepart(mi,@Now))

-- length of two digit hour

, len(replicate('0', 2 - len(convert(char(2),datepart(hh,@Now)))) + convert(char(2),datepart(hh,@Now)))

-- length of two digit minute

, len( replicate('0', 2 - len(convert(char(2),datepart(mi,@Now)))) + convert(char(2),datepart(mi,@Now)) )

-- concatenate hour and minute

, replicate('0', 2 - len(convert(VARchar(2),datepart(hh,@Now)))) + convert(VARchar(2),datepart(hh,@Now))

+ replicate('0', 2 - len(convert(char(2),datepart(mi,@Now)))) + convert(char(2),datepart(mi,@Now))

-- length of contcatenated hour and minute

, len(replicate('0', 2 - len(convert(VARchar(2),datepart(hh,@Now)))) + convert(VARchar(2),datepart(hh,@Now))

+ replicate('0', 2 - len(convert(char(2),datepart(mi,@Now)))) + convert(char(2),datepart(mi,@Now)) )

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Garadin One Orange Chip Points: 29613 More actions · Answer 6

Heh, so the "local settings" were... a different time. :blush:

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Garadin (11/28/2008)
Heh, so the "local settings" were... a different time. :blush:

It's those pesky CHARs innit!

Sue, try this instead:

SELECT RIGHT('0' + CAST(datepart(hh,@Now) AS VARCHAR(2)),2)

+ RIGHT('0' + CAST(datepart(mi,@Now) AS VARCHAR(2)),2)

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden