CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Zeno
Zeno

Reputation: 1829

How to get leading zeroes with DatePart?

I have this piece in my SELECT portion of my TSQL query:

CAST(DATEPART(HOUR,s.Arr) AS VARCHAR) + CAST(DATEPART(MINUTE,s.Arr) AS VARCHAR),

The original s.Arr field looks like:

2016-01-04 04:43:00.000

The goal is to make it look like:

0443

But right now my SQL makes it look like:

443

Because it isn't grabbing the leading zeroes. How can I grab the leading zeroes? Is there a way to do this without hackish methods like padding zeroes onto it?

Server: 2008 R2

Upvotes: 3

Views: 5032

Answers (3)

Saketh
Saketh

Reputation: 11

Padding with 00 and take right two

RIGHT('00' + CAST(DATEPART(HOUR,s.Arr) AS VARCHAR),2) + RIGHT('00' + CAST(DATEPART(MINUTE,s.Arr) AS VARCHAR),2)

Upvotes: 1

The Hill Boy
The Hill Boy

Reputation: 162

Try this one: I am assuming date format will not change.

declare @dt varchar(max) = '2016-01-05 09:42:18.717'

select SUBSTRING(convert(varchar(255),@dt),12,len(@dt)-(len(@dt)-2))+SUBSTRING(convert(varchar(255),@dt),15,len(@dt)-(len(@dt)-2))

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

With SQL Server 2012+ you can use FORMAT:

DECLARE @d DATETIME = '2016-01-04 04:43:00.000';
SELECT FORMAT(@d, 'HHmm');
-- 0443

LiveDemo

SQL Server 2008

Without padding with 0:

DECLARE @d DATETIME = '2016-01-04 04:43:00.000';
SELECT REPLACE(LEFT(CONVERT(VARCHAR(100), @d, 14),5), ':', '');
-- 0443

LiveDemo2

Style: 14 hh:mi:ss:mmm(24h)

Upvotes: 5

Related Questions