Help with MS SQL DateTime Query

[wxman]

Firstly, thanks for all that answered my pleas for help on this SQL statement – all is working really well now.
Weltchy also asked why I was not formatting the date in application code?
I am actually using the SQL code in a stored procedure and then displaying the results in a net2 grid view. Figured that if I could get the stored proc to out put in the correct format that I wanted – then I could reference the proc in other area of my web page.

I did mention that I am not a programmer – so most likely this sis a more efficient way of doing this but right now the following works well for me.

SELECT TOP (5)
NickName AS Chaser,
Speed,
Direction,
Altitude,
CONVERT(char(5), (getutcdate()- gpsTime), 108) AS [Time ago],
CASE TrackerID
WHEN 'Tracker 1' THEN '1'
WHEN 'Tracker 2' THEN '2'
WHEN 'Tracker 3' THEN '3'
WHEN 'Tracker 4' THEN '4'
WHEN 'Tracker 5' THEN '5'
WHEN 'Tracker 6' THEN '6'
WHEN 'Tracker 7' THEN '7'
WHEN 'Tracker 8' THEN '8'
WHEN 'Tracker 9' THEN '9'
WHEN 'Tracker 10' THEN '10'
END AS [GPS]
FROM TrackerData
WHERE DATEDIFF(hour, gpsTime, GETUTCDATE()) < 12
ORDER BY gpsTime DESC

[expat]

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.

You put up a question about SQL Server, mate. I don't do that, I only do SQL (on real databases - Oracle and DB2). And I'm really pissed off with "SQL Server" polluting Google, and indeed Jobserve, for those of us who are looking for "SQL".

[Weltchy]

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.

Its Christmas. You know, the time to be jolly, give and receive presents, and of course provide dodgy, half cut solutions to questions about all and sundry!!!!

Seriously tho, wtf am I doing working at 7:15am just 3 days before xmas. If ever there was a working condition that says I'm not a bloody employee, this is 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...

[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.

[Joe Black]

[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!!!

[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

[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?]

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?

[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?]

have you had a look at the datediff function?

[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 !!!