none
Today's date in where clause

    Question

  • I have a table called tblDays that has three fields:  trxDay (datetime), trxMonth (datetime) and FiscalYear (nchar(10). The data in this table is between 7/1/2007 and 6/30/2012.  I want to run a query that will get today's date and then return the current month and fiscal year.  I wrote a query which is listed below.  I was expecting it to return one record with 1/30/2012 as the trxDay, 1/1/2012 as the trxMonth and 2012 as the FiscalYear.  This particular record is in the table but the query returns no results.  Does anyone know what I am doing wrong here?

    SELECT

    TrxDay, TrxMonth,FiscalYear

    FROM tblDays

    WHERE

    (TrxDay = GETDATE

    ())

     


    Chuck W
    Monday, January 30, 2012 3:23 PM

Answers

  • try :

    SELECT
    TrxDay, TrxMonth,FiscalYear FROM tblDays
    WHERE TrxDay>= Convert(char(8),GETDATE (),112) and TrxDay< Convert(char(8),Dateadd(day,1,GETDATE()),112)
    

     

     

    Best regards
    • Marked as answer by ChuWil Monday, January 30, 2012 3:29 PM
    Monday, January 30, 2012 3:26 PM
  • Try in SQL Server 2008:

    SELECT
    
    TrxDay, TrxMonth,FiscalYear
    
    FROM tblDays
    
    WHERE TrxDay = CAST(GETDATE()) AS DATE)
    
    -- in SQL 2005 and less
    
    
    SELECT
    
    TrxDay, TrxMonth,FiscalYear
    
    FROM tblDays
    
    WHERE TrxDay >= dateadd(day, datediff(day,'19000101',CURRENT_TIMESTAMP),'19000101')
    AND TrxDay < dateadd(day, datediff(day,'19000101',CURRENT_TIMESTAMP)+1,'19000101')
    
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Badii Gharbi Monday, January 30, 2012 3:32 PM
    • Marked as answer by ChuWil Monday, January 30, 2012 3:39 PM
    Monday, January 30, 2012 3:28 PM
    Moderator
  • Chuck,

    Getdate () function returns the date and time so you need to convert it to date

    Try

    SELECT TrxDay, TrxMonth,FiscalYear FROM tblDays WHERE 
    (convert(date,TrxDay) = convert(date,GETDATE()))
    



    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    • Marked as answer by ChuWil Monday, January 30, 2012 3:34 PM
    Monday, January 30, 2012 3:30 PM

All replies

  • try :

    SELECT
    TrxDay, TrxMonth,FiscalYear FROM tblDays
    WHERE TrxDay>= Convert(char(8),GETDATE (),112) and TrxDay< Convert(char(8),Dateadd(day,1,GETDATE()),112)
    

     

     

    Best regards
    • Marked as answer by ChuWil Monday, January 30, 2012 3:29 PM
    Monday, January 30, 2012 3:26 PM
  • Try in SQL Server 2008:

    SELECT
    
    TrxDay, TrxMonth,FiscalYear
    
    FROM tblDays
    
    WHERE TrxDay = CAST(GETDATE()) AS DATE)
    
    -- in SQL 2005 and less
    
    
    SELECT
    
    TrxDay, TrxMonth,FiscalYear
    
    FROM tblDays
    
    WHERE TrxDay >= dateadd(day, datediff(day,'19000101',CURRENT_TIMESTAMP),'19000101')
    AND TrxDay < dateadd(day, datediff(day,'19000101',CURRENT_TIMESTAMP)+1,'19000101')
    
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Badii Gharbi Monday, January 30, 2012 3:32 PM
    • Marked as answer by ChuWil Monday, January 30, 2012 3:39 PM
    Monday, January 30, 2012 3:28 PM
    Moderator
  • Chuck,

    Getdate () function returns the date and time so you need to convert it to date

    Try

    SELECT TrxDay, TrxMonth,FiscalYear FROM tblDays WHERE 
    (convert(date,TrxDay) = convert(date,GETDATE()))
    



    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    • Marked as answer by ChuWil Monday, January 30, 2012 3:34 PM
    Monday, January 30, 2012 3:30 PM
  • Thanks Babii.  That worked.  What does the ,112 do to the query?  I think the char(8) gets the mm/dd/yyyy data and drops the time information correct?  Is the reason why WHERE (TrxDay = GETDATE()) did not work because it is datetime field and not simply a date field?

    Chuck


    Chuck W
    Monday, January 30, 2012 3:32 PM
  • Got it.  Thanks.  The query worked.  Chuck
    Chuck W
    Monday, January 30, 2012 3:34 PM