get the number of days it has been since a record was inserted

Hi

when inserting records into a table one of the fields is a date field. I am using the GETDATE() function to insert the date as the record is being inserted.

when i retrieve an entire record from the table i want to be able to select this date, but also to get the number of days it has been since that record was inserted.
eg: 3 days

if the record was inserted less than one day ago (<24 hrs ago) i would like it to return the number of hours.
e.g. 22 hrs

i dont want hours to be displayed if the days is >= 1.

please can anyone guide me with this?

thanks!

use the query like this

Declare @.MyVarasDateTime

Set @.Myvar='22/05/2007'

Select'satya', MyTime=

CASE

WHENDATEDIFF(hh,@.Myvar,GetDate())> 23THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate()))+' days'

ELSE

Convert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate()))+' hours'

END

Use the appropriate fields according to your database and tables

|||

Thanks Satya, this was really useful. Can you help me modify this so that it returns 1 day and 1 hour instead of 1 days and 1 hours

Appreciate the help!

|||

Sure change the code where its + "days" or + "hours"

1Declare @.MyVaras DateTime23Set @.Myvar='22/05/2007'45Select'satya', MyTime=67CASE8WHENDATEDIFF(hh,@.Myvar,GetDate()) > 23THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate())) +' day'910ELSE1112Convert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hour'1314END1516

|||

Sorry, i dont think i explained what i meant properly...

I need it to say 'days' and 'hours' all the time but the only exceptions are when days = 1 and when hours = 1...in them cases it should say 1 day and 1hour.

so as an example it could out the following :

11 days

21 days

1 day

...and

22 hours

6 hours

1 hour.

Thanks again!

|||

Declare @.MyVaras DateTime Set @.Myvar='05/22/2007'Select'satya', MyTime=CASEWHENDATEDIFF(hh,@.Myvar,GetDate()) < 2THENConvert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hour'WHENDATEDIFF(hh,@.Myvar,GetDate()) < 23THENConvert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hours'WHENDATEDIFF(d,@.Myvar,GetDate()) < 2THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate())) +' day'WHENDATEDIFF(d,@.Myvar,GetDate()) > 1THENConvert(varchar(10),DATEDIFF(dd,@.Myvar,GetDate())) +' days'END

|||

Great, worked perfectly :)

Thanks.

|||

You are welcome... Answer it if solved