Help with MS SQL DateTime Query

[wxman]

Firstly I am not an SQL guy (or even pretending to be!) as my background is Active Directory so I apologise in advance if this is a simple answer – 2 hours on Goggle have left me clue less…

I am writing a home application that basically logs GPS movements. I store the movements in a MS 2005 database. I am trying to work out how many HH:MM ago the last GPS signal was received. This value is in UTC time and is stored as follows

gpsTime(datetime) 2006-12-21 18:16:27.000

By using (GETUTCDATE()) – gpsTime I get then how long ago the last report was recived. 1900-01-01 00:01:24.970

But what I really want is just HH:MM 00:01 displayed – i.e. 1 minute ago…


This is what I have so far..

SELECT TOP (5)
(GETUTCDATE()) - gpsTime
As [Time Ago]
FROM TrackerData
WHERE gpsTime > (GETUTCDATE() -0.5) ‘ ignore records over 12 hours old
ORDER BY gpsTime DESC

Thanks in advance for anyone who can help me – this is driving me nuts !!!

[To BI or not to BI?]

have you had a look at the datediff function?

[wxman]

Hey BI! - thanks for the quick reply - I struggled with datediff.. so I tried convert! but I can not seem to drop off the seconds I only want (hh:mm)

so...

select CONVERT(varchar(8), (getutcdate()- gpsTime), 108)
from TrackerData
WHERE gpsTime > (GETUTCDATE() -0.5)
ORDER BY gpsTime DESC

gives..

[01:09:05] (1 hour, 9 mins, 5 seconds)

I wonder if there is a format code (I am currently using ,108) that just gives hh:mm ?

more google for me I guess...

[To BI or not to BI?]

Hey BI! - thanks for the quick reply - I struggled with datediff.. so I tried convert! but I can not seem to drop off the seconds I only want (hh:mm)

so...

select CONVERT(varchar(8), (getutcdate()- gpsTime), 108)
from TrackerData
WHERE gpsTime > (GETUTCDATE() -0.5)
ORDER BY gpsTime DESC

gives..

[01:09:05] (1 hour, 9 mins, 5 seconds)

I wonder if there is a format code (I am currently using ,108) that just gives hh:mm ?

more google for me I guess...

EDIT: why don't you wrap the whole thing in a substring function?

[Joe Black]

Using DATEDIFF is better than the "gpsTime > (GETUTCDATE() -0.5)" expression, e.g. DATEDIFF(hour, gpsTime, GETUTCDATE()) < 12.

As for the just wanting hours and minutes part then you can also use smalldatetime which is accurate only to a minute, or look at casting (CONVERT) and select a output format which suits.

Edit: deleted this thinking you'd found the answer. try char(5) instead

[To BI or not to BI?]

JB is right:

select CONVERT(char(5), (getutcdate()- gpsTime), 108)
from TrackerData
WHERE gpsTime > (GETUTCDATE() -0.5)
ORDER BY gpsTime DESC

[Weltchy]

You could use datepart as well and extract the hours and minutes. But, the real question is, why are you trying to format date/time at the database level? What application are you using to display the information. You should really be looking to use that API to provide the correct time format!!!

[Joe Black]

[MarillionFan]

You bunch of bloody SQL amateurs. I put a question up about SQL no-one apart of Socky can answer it, someone puts a simple question up you all smarm over who can answer it.

Winging it, you're all bloody winging it.

[To BI or not to BI?]

You bunch of bloody SQL amateurs. I put a question up about SQL no-one apart of Socky can answer it, someone puts a simple question up you all smarm over who can answer it.

Winging it, you're all bloody winging it.

Well, I did try...