• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • 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

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    Help with MS SQL DateTime Query

    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 !!!
    www.stormtrack.co.uk - My Stormchasing website.

    #2
    have you had a look at the datediff function?
    Carpe Pactum

    (does fuzzy logic tickle?)

    Comment


      #3
      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...
      www.stormtrack.co.uk - My Stormchasing website.

      Comment


        #4
        Originally posted by 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...
        EDIT: why don't you wrap the whole thing in a substring function?
        Last edited by To BI or not to BI?; 21 December 2006, 19:43.
        Carpe Pactum

        (does fuzzy logic tickle?)

        Comment


          #5
          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

          Comment


            #6
            JB is right:

            select CONVERT(char(5), (getutcdate()- gpsTime), 108)
            from TrackerData
            WHERE gpsTime > (GETUTCDATE() -0.5)
            ORDER BY gpsTime DESC
            Last edited by To BI or not to BI?; 21 December 2006, 19:58.
            Carpe Pactum

            (does fuzzy logic tickle?)

            Comment


              #7
              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 by Weltchy; 21 December 2006, 21:16.

              Comment


                #8

                Comment


                  #9
                  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.
                  What happens in General, stays in General.
                  You know what they say about assumptions!

                  Comment


                    #10
                    Originally posted by 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.
                    Well, I did try...
                    Carpe Pactum

                    (does fuzzy logic tickle?)

                    Comment

                    Working...
                    X