Nice solution. However, it returns NULL in certain situations (less than
a day for example). Besides, you don't need the CASE expressions:
SELECT
CAST(dt.Days AS VARCHAR(5)) + ' Day(s) ' +
CAST(dt.Hours AS VARCHAR(2)) + ' Hour(s) ' +
CAST(dt.Minutes AS VARCHAR(2)) + ' Minute(s)'
FROM
(
SELECT t.Minutes / (24 * 60) AS Days,
(t.Minutes % (24 * 60)) / 60 AS Hours,
(t.Minutes % (24 * 60)) % 60 AS Minutes
FROM
(
SELECT ABS(DATEDIFF(MINUTE, '2006-12-12 18:27:43.173',
GETDATE())) AS Minutes
) AS t
) AS dt
Gert-Jan
[quoted text, click to view] Krishnakumar S wrote:
>
> Try this
>
> SELECT
> CAST(dt.Days AS VARCHAR(5)) + ' Day(s) ' +
> CAST(dt.Hours AS VARCHAR(2)) + ' Hour(s) ' +
> CAST(dt.Minutes AS VARCHAR(2)) + ' Minute(s)'
> FROM
> (
> SELECT
> CASE
> WHEN t.Minutes = 0 THEN 0
> WHEN t.Minutes >= (24 * 60) THEN (t.Minutes / (24 * 60))
> END AS Days,
> CASE
> WHEN t.Minutes = 0 THEN 0
> WHEN t.Minutes % (24 * 60) >= 60 THEN ((t.Minutes % (24 * 60)) / 60)
> END AS Hours,
> (t.Minutes % (24 * 60)) % 60 AS Minutes
> FROM
> (
> SELECT ABS(DATEDIFF(MINUTE, '2006-12-12 18:27:43.173', GETDATE())) AS
> Minutes
> ) AS t
> ) AS dt
>
> --
> Krishnakumar S
>
> "vanitha" wrote:
>
> > i should achive this without writing function that isdirectly in the query
> >
> > "Krishnakumar S" wrote:
> >
> > > There is a good article in
www.aspfaq.com. Go to
> > >
http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-convert-a-datediff-to-days-hours-and-minutes.html > > > --
> > > Krishnakumar S
> > >
> > >
> > > "vanitha" wrote:
> > >
> > > > date difference between 2006-12-14 18:27:43.173 and getdate() in days, hours
> > > > and minutes
> > > >
> > > > thank you
> > > >
> > > > "Krishnakumar S" wrote:
> > > >
> > > > > Refer DATEDIFF in BOL. Or If you need a clear solution please post the DDL
> > > > > and sampe data
> > > > >
> > > > > --
> > > > > Krishnakumar S
> > > > >
> > > > >
> > > > > "vanitha" wrote:
> > > > >
> > > > > > hi,
> > > > > >
> > > > > > i want to find the date difference in days,hours and minutes format.
> > > > > >
> > > > > > please help me to solve this.
> > > > > >
> > > > > > thank you
