Time Comparison In MS SQL

  • Hi,

    I want to compare a time passed from VB with SQL Server's Current Time. How can i do that?

    Thanx in Advance.

    Sundar.

  • How do you want to compare? Need an example of what it is you want to see.

  • Hello Sundar,

    quote:


    I want to compare a time passed from VB with SQL Server's Current Time. How can i do that?


    do you mean a comparison with GETDATE() or CURRENT_TIMESTAMP?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Hi,

    Thanks for your quick Reply.

    I'm getting time like '06:00:00 PM' from Visual Basic, I want to compare this time with SQL Server's Current Time (Not Date). Like, whether passed time is Greater than or Less than Server's Current Time.

    What needs to be done for this?

    Thanx.

    Regards,

    Sundar.

  • Hi Sundar,

    quote:


    Hi,

    Thanks for your quick Reply.

    I'm getting time like '06:00:00 PM' from Visual Basic, I want to compare this time with SQL Server's Current Time (Not Date). Like, whether passed time is Greater than or Less than Server's Current Time.


    unfortunately there is no explicit function for only returning time.

    Maybe this works

    declare @a datetime

    set @a = '17.07.2003 19:00:00'

    if (@a<getdate() )

    print 'j'

    else

    print 'n'

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Hi Frank,

    I Just replied to that. Could you plz help me in this regard.

    Thank You.

    Sundar.

    quote:


    Hello Sundar,

    quote:


    I want to compare a time passed from VB with SQL Server's Current Time. How can i do that?


    do you mean a comparison with GETDATE() or CURRENT_TIMESTAMP?

    Cheers,

    Frank


  • quote:


    Hi Frank,

    I Just replied to that. Could you plz help me in this regard.

    Thank You.

    Sundar.

    quote:


    Hello Sundar,

    quote:


    I want to compare a time passed from VB with SQL Server's Current Time. How can i do that?


    do you mean a comparison with GETDATE() or CURRENT_TIMESTAMP?

    Cheers,

    Frank



    ???

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • here's one way to do elapsed time, if that's what you need....

    Dim conn As New ADODB.Connection

    Dim cmd As New ADODB.Command

    Dim rs As New ADODB.Recordset

    Dim StartTime As Date

    Dim EndTime As Date

    Dim ElapsedTime As Date

    conn.Open "DSN=pubs;uid=;pwd=;database=pubs"

    Set cmd.ActiveConnection = conn

    cmd.CommandText = "SELECT getdate()as date"

    rs.CursorLocation = adUseClient

    rs.Open cmd, , adOpenStatic, adLockBatchOptimistic

    rs.MoveFirst

    StartTime = rs.Fields("date")

    rs.Close

    rs.Open cmd, , adOpenStatic, adLockBatchOptimistic

    rs.MoveFirst

    EndTime = rs.Fields("date")

    rs.Close

    ElapsedTime = EndTime - StartTime

    MsgBox ElapsedTime '(in hh/mm/ss format)

  • IF @vbtime<cast(convert(varchar(8),getdate(),108) as datetime) print 'less'

  • sorry, should read:

    cast(@vbtime as datetime)<cast(convert(varchar(8),getdate(),108) as datetime)

  • It's woefully complex, but here's the easiest way I could get a time out of SQL in the format '06:00:00 PM'.

    select

    Case

    When cast(cast(convert(varchar, getdate(), 108) as char(2)) as int) > 12 then cast(cast(cast(convert(varchar, getdate(), 108) as char(2)) as int) - 12 as varchar)

    Else cast(cast(cast(convert(varchar, getdate(), 108) as char(2)) as int) as varchar)

    END + substring(convert(varchar, getdate(), 108), 3, 7) +

    Case

    When cast(cast(convert(varchar, getdate(), 108) as char(2)) as int) > 12 then ' PM'

    Else ' AM'

    END

    Signature is NULL

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply