• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Help with MS SQL DateTime Query

wxman

More time posting than coding
Joined
Jul 18, 2005
Messages
384
Likes received
0
Thanks received
0
Location
Leicestershire
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 !!!
 
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...
 
wxman said:
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?
 
Last edited: 19:40
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
 
JB is right:

select CONVERT(char(5), (getutcdate()- gpsTime), 108)
from TrackerData
WHERE gpsTime > (GETUTCDATE() -0.5)
ORDER BY gpsTime DESC
 
Last edited: 19:48
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!!!
 
Last edited: 21:11
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. :mad:
 
MarillionFan said:
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. :mad:
Well, I did try...
 

Advertisers

Back
Top