Convert a DATEDIFF result to a string, to concatenate another string to it

Question

I have a DATEDIFF result (in minutes) that i have divided by 1440 to count as days, I wish to add 'Hours' to the end of it but cant figure out how to turn the result into a string first.

DATEDIFF(mi,z.entryDate, GETDATE()) / 1440

this returns 11, but i want it to read 11 Hours.

Ive tried to CAST AS VARCHAR but think my syntax is wrong. Please Help :)

did u try - `cast((DATEDIFF(mi,z.entryDate, GETDATE()) / 1440) + ' Hours' as nvarchar)`? — KrazzyNefarious, Dec 30 '13 at 22:48
Yes it still says 'Conversion failed when converting the varchar value ' Hours' to data type int.' — Matt, Dec 30 '13 at 22:52

score 5 · Accepted Answer · answered Dec 30 '13 at 22:57

5

For days:

CONVERT(varchar(11), DATEDIFF(minute, z.entryDate, GETDATE()) / 1440) + ' Days'

For hours:

CONVERT(varchar(11), DATEDIFF(minute, z.entryDate, GETDATE()) / 60) + ' Hours'

answered Dec 30 '13 at 22:57

swandog

741
8
22

Added length to varchar conversion as @aaron-bertrand said – swandog Dec 30 '13 at 23:02

score 1 · Answer 2 · edited Jul 29 '20 at 18:41

To get hours (rounded, of course), you would divide by 60, not 1440:

SELECT CONVERT(VARCHAR(11), (DATEDIFF(MINUTE, z.entryDate, GETDATE()) / 60)) 
  + ' hours.' FROM ...;

If you want to divide by 1440, then the result is in days, not hours:

SELECT CONVERT(VARCHAR(11), (DATEDIFF(MINUTE, z.entryDate, GETDATE()) / 1440)) 
  + ' days.' FROM ...;

And please stop using things like mi shorthand (blog post for background) and converting to varchar without length (blog post for background).

score 0 · Answer 3 · answered Dec 30 '13 at 22:55

0

I am sorry about my previous post. this should work :- cast((DATEDIFF(mi,z.entryDate, GETDATE()) / 1440) as nvarchar) + ' Hours'

answered Dec 30 '13 at 22:55

KrazzyNefarious

3,202
3
20
32

Uh, why **n**varchar? And [why cast without length](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx)? – Aaron Bertrand Dec 30 '13 at 22:59
umm, okay I am a naive here. what will happen if I have a million records? – KrazzyNefarious Dec 30 '13 at 23:04
Sorry, no idea at all what you're asking. – Aaron Bertrand Dec 31 '13 at 00:15

Convert a DATEDIFF result to a string, to concatenate another string to it

3 Answers3