snippetsqlMinor

Need to convert 21:45 to 21.75

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

convertneedstackoverflow

Problem

I have a datetime column with a time portion of 21:45; I need to convert it to 21.75. I tried:

CONVERT(VARCHAR(5), DATE,108)

This converts it to 21:45, now the client wants this be 21.75.

Solution

DECLARE @x TABLE(col DATETIME);

INSERT @x SELECT '20150901 21:45';

SELECT col, 
  d = CONVERT(CHAR(10), col, 101), -- AMBIGUOUS, REGIONAL, NOT RECOMMENDED!
  t = CONVERT(DECIMAL(4,2), DATEDIFF(MINUTE, CONVERT(DATE,col), col)/60.0) 
FROM @x;

Results:

col                        d             t
-----------------------    ----------    -----
2015-09-01 21:45:00.000    09/01/2015    21.75

Code Snippets

DECLARE @x TABLE(col DATETIME);

INSERT @x SELECT '20150901 21:45';

SELECT col, 
  d = CONVERT(CHAR(10), col, 101), -- AMBIGUOUS, REGIONAL, NOT RECOMMENDED!
  t = CONVERT(DECIMAL(4,2), DATEDIFF(MINUTE, CONVERT(DATE,col), col)/60.0) 
FROM @x;

col                        d             t
-----------------------    ----------    -----
2015-09-01 21:45:00.000    09/01/2015    21.75

Context

StackExchange Database Administrators Q#114532, answer score: 3

Revisions (0)

No revisions yet.