Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    21

    Unanswered: compare DateTime values

    guys this is kind of a biggie for me.
    i've only used sql server to compare the Date Portion of date(s), not the whole string (mm/dd/yyyy hh:mi).
    i have a table called Time_Check which has two fields with timestamps,
    "TimeLastRun & TimeNextRun". i was trying to compare the Current TimeStamp that i pass from a vb.net form to a stored procedure and compare that DateTime to the TimeNextRun. I'd like to then Update the two Time Columns if rows are found. Should i maybe be using the DateDif Func if i know ahead of time how much time should be elapsed(say five minutes)?
    Any Code help is appreciated

    Create Procedure dbo.My_Time_Check
    @myDate smalldatetime
    as
    select alertnumber,alertname,TimeLastRun,TimeNextRun
    from time_check
    where timeNextRun >= @myDate
    --would like to update these two columns if rows are found

    thanks again
    this forum is the best
    rik

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How much time has elapsed? What does that have to do with anything?

    Explain why this would not work for you:
    Code:
    Create Procedure dbo.My_Time_Check
    	@myDate smalldatetime
    as
    update	time_check
    set	TimeLastRun = [foo],
    	TimeNextRun = [bar]
    where	timeNextRun >= @myDate
    You need to explain your requirments more clearly.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2006
    Posts
    21

    More Detail

    i appologize - the stress is killing me.

    the procedure is called from a service which fires every 60 seconds and passes the current timestamp.
    it then goes to a table. what i'm trying to accomplish is keep looking for when five minutes have passed. i dont know what the best way to do that is.
    if the time matches, then i want to return the first Column (myAlert) to the Original Calling Procedure and overwrite the TimeStamp(s) Columns.
    Mind you, i may only need one TimeStamp column to achieve this. i just dont know at this point.
    there'll eventually be several rows - some will need to pass the myAlert Field back after five minutes, some an hour, some a day etc.

    myAlert,LastTimeRun,NextTimeRun

    i do appreciate the help on this and appologize for not being more clear
    thanks again
    rik

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rikb54
    ...what i'm trying to accomplish is keep looking for when five minutes have passed....
    Five minutes since what? Really, you need to slow down and explain your problem clearly. The good news is that there are several simple solutions that may apply to your case, but at this point I haven't a clue which one is appropriate.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2006
    Posts
    21
    sorry about that.
    Five minutes since the dateTime that's in the table column "LastTimeRun" and the current_timestamp when the procedure is called.

    so if "LastTimeRun" column holds a current Value "2/2/2006 9:30:00"
    and i have a Service that fires this procedure every sixty Seconds.
    When the Procedure is called it takes the current_timestamp and compares
    the datediff between "LastTimeRun" column and the current_timestamp.
    if the difference is 5 minutes, then return the First Column and update the "LastTimeRun" column to the current_timestamp that it was just compared to.

    ......OR...the column "NextTimeRun" column of that table would hold the actual value -9:35:00 - and i'd just need to compare the current_timestamp
    with 9:35 and if they match return the First Column and update the "NextTimeRun" column to 9:40.

    currently my table is basically three columns:::

    AlertNumber,LastTimeRun,NextTimeRun

    hopefully i've done a better job this time of explaining what i'm trying to accomplish. please let me know if you need more. truly, i do appreciate the help.
    thanks
    rik

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So, why not something along this line:
    Code:
    Create Procedure dbo.My_Time_Check
    as
    update	time_check
    set	TimeLastRun = [foo],
    	TimeNextRun = [bar]
    where	timeLastRun <= dateadd(minute, -5, getdate())
    ...though it would seem you would actually want to DO something other than just update the timestamps to make the code actually functional.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •