Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2008
    Posts
    6

    Unanswered: How to search date colomb by todays date

    Hi All,

    I have one table which inserts date as current_date. I have to query the data whcih belongs to today.

    Table 1
    *******************************
    Name ID Date_Reg

    DDD 0012e todays date(2009-01-27 19:11:56.937)
    dd1 023ed 2009-01-27 20:11:56.937

    *******************************

    Now i have to serch records which belogs to today. Then how to do this .

    Select * from table1 where date_reg=?

  2. #2
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    This will return today's date, at midnight. You can compare >= this.
    Code:
    SELECT CONVERT(datetime,(CONVERT(int,GETDATE())))
    

    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    use like this
    where dateadd(dd,datediff(d,0,date),0) = dateadd(dd,datediff(dd,0,getdate()),0)
    r indexes
    WHERE Date>=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
    AND Date<DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Just check if DATEDIFF(dd, 0, GETDATE()) = DATEDIFF(dd, 0, Date_Reg) should do the trick.
    Code:
    create table wim(
    id integer not null,
    ts datetime not null,
    constraint pk_wim primary key (id)
    );
    
    insert into wim(id, ts) values (1, '2009-01-14 00:12:45');
    insert into wim(id, ts) values (2, '2009-01-28 00:12:45');
    insert into wim(id, ts) values (3, '2009-01-29 00:12:46');
    insert into wim(id, ts) values (4, '2009-01-29 00:12:45');
    insert into wim(id, ts) values (5, '2010-01-29 00:12:45');
    insert into wim(id, ts) values (6, '2009-02-20 00:12:45');
    
    select * , DATEDIFF(dd, 0, GETDATE()), DATEDIFF(dd, 0, ts), 
    	CASE WHEN DATEDIFF(dd, 0, GETDATE()) = DATEDIFF(dd, 0, ts) 
    		THEN '=' 
    		ELSE '<>' 
    	END as same_day,
    	GETDATE() as now
    from wim;
    
    drop table wim;
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    any time you apply a function to a column, the optimizer cannot use an index on that column

    so far, bklr has the best answer:

    Code:
    WHERE Date_Reg >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) -- midnight last night
      AND Date_Reg  < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1) -- midnight tonight
    put the column to be searched on the left side of the operator, and do all your calculations on the right

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2007
    Posts
    183
    Quote Originally Posted by bklr
    use like this
    where dateadd(dd,datediff(d,0,date),0) = dateadd(dd,datediff(dd,0,getdate()),0)
    Wouldn't this be simpler

    WHERE DATEDIFF(DAY, colDate, getdate()) = 0

    but still the wrong approach?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that's simpler, and yes, that would bring the correct results... but maybe by next tuesday

    any time you apply a function to a column, the optimizer cannot use an index on that column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2007
    Posts
    183
    You know that and I know that.
    I quoted bklr to make sure he knows it too.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you quoted the lines just above the lines where he clearly shows that he knows it too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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