Alpha Video Training
Results 1 to 9 of 9

Thread: SQL Query records for yesterdays date

  1. #1
    Member
    Real Name
    Jeff Broomall
    Join Date
    Feb 2008
    Posts
    18

    Default SQL Query records for yesterdays date

    Would anyone have a way to query SQL server data for yesterday's date? I am trying to query a datetime field and use this in a grid component but having trouble with datetime field.

  2. #2
    Member
    Real Name
    Robert Earl Allin
    Join Date
    Apr 2009
    Location
    Toronto, Ontario
    Posts
    241

    Default Re: SQL Query records for yesterdays date

    For SQL, there's a dateadd() function you can use.

    so your query could be:

    Select * from tablename where dateadd(day,-1,datecolumn) = curdate()


    Here's a link to the function:
    http://www.w3schools.com/sql/func_dateadd.asp

    Thanks,

    Robert

  3. #3
    Member
    Real Name
    Jeff Broomall
    Join Date
    Feb 2008
    Posts
    18

    Default Re: SQL Query records for yesterdays date

    I probably didn't explain correctly. I need to find records where the datetime field contains only yesterdays date.

  4. #4
    Member
    Real Name
    Robert Earl Allin
    Join Date
    Apr 2009
    Location
    Toronto, Ontario
    Posts
    241

    Default Re: SQL Query records for yesterdays date

    Ok, try:

    Select * from tablename where dateadd(day,-1,curdate()) = datecolumn

    Thanks,

    Robert

  5. #5
    Member
    Real Name
    Robert Earl Allin
    Join Date
    Apr 2009
    Location
    Toronto, Ontario
    Posts
    241

    Default Re: SQL Query records for yesterdays date

    Sorry - that was wrong I didn't realize you had a 'datetime' field.. I read date.

    Try this:

    Select datecolumn, DATEADD(D, 0, DATEDIFF(D, 0, datecolumn)) as dateonly_datecolumn, DATEADD(D, 0, DATEDIFF(D, 1233, GETDATE())) as yesterday from tablename
    WHERE DATEADD(D, 0, DATEDIFF(D, 0, creation_date)) = DATEADD(D, 0, DATEDIFF(D, 1, GETDATE()))

    Try it without the WHERE first, and you'll see what it does.

    Thanks,

    Robert
    Last edited by workaholic06; 08-04-2010 at 05:59 PM.

  6. #6
    "Certified" Alphaholic chadbrown's Avatar
    Real Name
    Chad Brown
    Join Date
    Aug 2007
    Location
    Aurora, Ontario, Canada
    Posts
    1,408

    Default Re: SQL Query records for yesterdays date

    this is a where statement I use

    now() - interval 2 day
    Chad Brown

  7. #7
    "Certified" Alphaholic glenschild's Avatar
    Real Name
    Glen Schild
    Join Date
    Apr 2000
    Location
    Frome, Somerset, UK
    Posts
    1,521

    Default Re: SQL Query records for yesterdays date

    getdate() returns a datetime value so how about

    SELECT * FROM table WHERE CAST(datecolumn AS DATE) = DATEADD(dd,-1,CAST(getdate() as DATE))
    Last edited by glenschild; 08-17-2018 at 03:17 PM. Reason: oops less haste more speed...
    Glen Schild



    My Blog


  8. #8
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,393

    Default Re: SQL Query records for yesterdays date

    Hi Jeff,
    Try the code below.
    You will need to replace <TABLE> with the name of your table, and <COLUMN> with the name of your datetime column.

    More graceful solutions may exist, but this should do what you want.

    Code:
    select * from <TABLE>
    where left(convert(varchar,dateadd(d,-0,<COLUMN>),120),10) =left(convert(varchar,dateadd(d,-1,getdate()),120),10)

  9. #9
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,393

    Default Re: SQL Query records for yesterdays date

    Hi Jeff,
    Try the code below.
    You will need to replace <TABLE> with the name of your table, and <COLUMN> with the name of your datetime column.

    More graceful solutions may exist, but this should do what you want.

    Code:
    select * from <TABLE>
    where left(convert(varchar,dateadd(d,-0,<COLUMN>),120),10) =left(convert(varchar,dateadd(d,-1,getdate()),120),10)

Similar Threads

  1. Query multible records the query against sql
    By steve745 in forum Alpha Five Version 9 - Desktop Applications
    Replies: 1
    Last Post: 05-10-2010, 07:42 AM
  2. Move from SQL Express 2008 to SQL Standard, Can't INSERT new records now
    By Scholin in forum Application Server Version 10 - Web/Browser Applications
    Replies: 6
    Last Post: 02-13-2010, 05:26 PM
  3. Use variables in Xbasic for deleting duplicate records on query.order, query.filter,
    By leufj in forum Alpha Five Version 9 - Desktop Applications
    Replies: 2
    Last Post: 11-23-2009, 10:36 AM
  4. Sql date to Alpha and back to SQL
    By Pat Bremkamp in forum Application Server Version 10 - Web/Browser Applications
    Replies: 3
    Last Post: 10-14-2009, 04:46 AM
  5. sql query
    By jeffemery in forum Alpha Five Version 4
    Replies: 11
    Last Post: 05-11-2001, 09:25 AM

Bookmarks

Posting Permissions

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