CODEWITHSUNDEEP
t-sql
user996277
user996277

Reputation:

Distinct Time from Table SQL

I have table Proccesses that consist of Date Time Field.

Proccesses ( Id as int , RunDate as DateTime) I need to run Distinct by RunDate as Time Without seconds

For Example

ID        RunDate 
1         2011-12-13 12:36:26.483
2         2011-12-12 12:37:22.421
3         2011-12-11 12:36:44.421

I need to receive in output

Output
12:01
12:03

In order to retrieve it I using following SQL and it's working

SELECT DISTINCT DATENAME(hour, RunDateTime) + ':' + 
DATENAME(mi, RunDateTime) AS  d
from Proccesses

The problem that if minutes is less than 10 for example 8 I receiving single digit "8" , and I want to receive two digit 08

For example I receive 12:8 , and I need to receive 12:08

Upvotes: 1

Views: 1045

Answers (3)

Yahia
Yahia

Reputation: 70379

try

SELECT DISTINCT 
RIGHT ('00' + CAST(DATENAME(hour, RunDateTime) AS VARCHAR(2)), 2) + ':' +
RIGHT ('00' + CAST(DATENAME(mi, RunDateTime) AS VARCHAR(2)), 2)
from Proccesses

Alternatively you can use CONVERT as suggested by @JoeStefanelli and handle the string result accordingly.

Upvotes: 1

syneptody
syneptody

Reputation: 1280

SELECT RIGHT(CONVERT(VARCHAR,RunDateTime),7)

You could strip of the AM/PM if you want to.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

CONVERT style 108 will return hh:mm:ss. Using CHAR(5) for the data type will return just the hh:mm portion.

SELECT DISTINCT CONVERT(CHAR(5), RunDateTime, 108) AS d
    FROM Processes

Upvotes: 0

Related Questions