Results 1 to 5 of 5

Thread: [RESOLVED] Datediff sql function

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2008
    Posts
    100

    Resolved [RESOLVED] Datediff sql function

    Hello,

    Can someone please help me on this.

    I have a table called timekeeping
    It has 3 columns. Intime, Outtime and difference.

    I can easily insert rows and keep the intime and outtime correctly, but can someone tell me how to show the difference of intime and outtime in minutes in the difference column?

    i use the following function to add time in the table.

    Code:
    sqlstr = "insert into timekeeping (intime,outtime) values ('" & Time & "','" & Time & "')"
    cn.execute(sqlstr)
    and whts wrong with the following code?

    Code:
    sqlStr = "SELECT DATEDIFF(dd,'2014-11-29','2014-11-30')as diffdate"
    cn.Execute (sqlStr)
    Debug.Print sqlStr
    in the debug window it shows
    SELECT DATEDIFF(dd,'2014-11-29','2014-11-30')as diffdate

  2. #2
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    671

    Re: Datediff sql function

    Short answer - don't bother.
    This kind of derivable data should be .. er .. derived .. as and when you need it.

    Do you store people's "Age" (derivable) or "Date of Birth" (fixed)?

    Code:
    create view timekeeping_v as 
    select intime 
    ,      outtime 
    ,      ( outtime - intime ) duration
    from   timekeeping ;
    ... and whts wrong with the following code?
    sqlStr = "SELECT DATEDIFF(dd,'2014-11-29','2014-11-30')as diffdate"
    (1) Does your DBMS support the DateDiff function? Not all of them do.
    (2) The parameter value "dd" probably needs [single] quotes around it, like the other two literals being passed as parameters to the function.
    (3) Good choice of date literal format.

    cn.Execute (sqlStr)
    Beware that stray space before the opening bracket.
    OK, it'll make no difference at all here, but as you move into working with Objects, this syntax will start to trip you up. Briefly, VB evaluates the bracketed expression, which can be the default property of an Object and passes that to the intended function, instead of the Object [reference] itself.
    Choose either the "statement" syntax ("cn.Execute sqlStr") or the "function" one ("call cn.Execute(strSql)") and stick to it. Fervently.

    Debug.Print sqlStr
    Bonus points! Logging/ displaying/ being able to check your SQL (preferably before it's submitted to the database) is a really Good Idea.

    Regards, Phill W.

  3. #3
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,442

    Re: Datediff sql function

    In addition, in this:

    Code:
    sqlStr = "SELECT DATEDIFF(dd,'2014-11-29','2014-11-30')as diffdate"
    ,

    a space is missing after the closing bracket before 'as'

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,953

    Re: Datediff sql function

    Quote Originally Posted by muzamilsumra View Post
    and whts wrong with the following code?

    Code:
    sqlStr = "SELECT DATEDIFF(dd,'2014-11-29','2014-11-30')as diffdate"
    cn.Execute (sqlStr)
    Debug.Print sqlStr
    in the debug window it shows
    SELECT DATEDIFF(dd,'2014-11-29','2014-11-30')as diffdate
    Nothing. that's what I would expect to show in the debug window... the SQL... that's what you told it to show you. However, I suspect that what you expected, was the RESULT of the SQL ... except that you completely ignored it when you executed the SQL.

    Code:
    sqlStr = "SELECT DATEDIFF(dd,'2014-11-29','2014-11-30')as diffdate"
    dim rs as RecordSet 
    Set rs = cn.Execute (sqlStr) 'NOW you're capturing the results
    Debug.Print rs.Fields("diffdate").Value 'NOW you'll see the results of the select.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Feb 2008
    Posts
    100

    Re: Datediff sql function

    Quote Originally Posted by techgnome View Post
    Nothing. that's what I would expect to show in the debug window... the SQL... that's what you told it to show you. However, I suspect that what you expected, was the RESULT of the SQL ... except that you completely ignored it when you executed the SQL.

    Code:
    sqlStr = "SELECT DATEDIFF(dd,'2014-11-29','2014-11-30')as diffdate"
    dim rs as RecordSet 
    Set rs = cn.Execute (sqlStr) 'NOW you're capturing the results
    Debug.Print rs.Fields("diffdate").Value 'NOW you'll see the results of the select.

    -tg
    Thank you all for your support, but techgnomes answer was the best one as that is what i exactly wanted. Cheers...

    One more question, is there any better solution than using datagrid control to display and edit records?

Posting Permissions

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



Click Here to Expand Forum to Full Width