How to select records using just the date and not the time in a datetime field

  • How to select records using just the date and not the time; I want to show all records with a date of '07/16/2004'

    I have a table with a field cTime attribute datetime; the values are similar to the following: 7/16/2004 8:50:54 AM.

     

     

  • Two ways, the second may prevent the use of indexes, so be warned.

    DECLARE @dt DATETIME

    SET @dt = '2004/07/16'

    SELECT * FROM tbl

    WHERE cTime BETWEEN @dt AND DATEADD(ss,-1,DATEADD(dd,1,@dt))

    (all records where cTime between 2004/07/16 00:00:00 and 2004/07/16 23:59:59)

    OR

    SELECT * FROM tbl

    WHERE CAST(FLOOR(CAST cTime AS FLOAT)) AS DATETIME) = @dt

    (All records where the date portion of cTime = 2004/07/16)

    HTH

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • There are 2 methods I have used to do this....

    1.  Convert the field to a varchar and then do the comparision. 

    Select * From Table Where Convert(VarChar(10), MyField, 101) = '08/04/2004'

    When using this method, you will need to be very careful about the formatting of the "constant" part of the expression.

    2.  When using large tables, I opted to add another field to the table that stores the date only (the time part it 00:00:00).  I know this violates database normalization practices, but it really sped up my queries.


    George Mastros
    Orbit Software, Inc.

  • If you are looking for a single date, you can also use

    datediff(d,'08/04/2004',myfield) = 0.

    This also allows you to select current  (todays) records by

       datediff(d,myfield,getdate()) = 0

    or even those in the last five minutes

      datediff(mi,myfiled,getdate()) <= 5

     

    Jonathan

     

     

  • I always use the cast function for this.

    select cast(left(order_date,11) as datetime)

     

    This eliminates the time.

  • Thanks for all the information!

    I was trying to use a statement that I can use on an AS400:

    select * from cptipdta/seams where date(ctime)='2004-05-17' 

    but that does not work on a SQL Server and was looking for something not similar that was not complicated.

    The "(CAST(LEFT(cTime, 11) AS datetime) = '2004-05-17')" works just fine for me...Thanks again everyone!

  • I have these function also which get date only

    http://www.sqlservercentral.com/scripts/contributions/733.asp

    Date without Time (2 functions)

    Some time you need to know only Date without Time.

    This is simple function that allows you truncate datetime field to date only.

    This function can help you to construct dynamic T-SQL for select date period.

    P.S. Thanks for ispaleny!

    Conversion only for real not enough! It is necessary convert to real that correct errors near the last instant of the day.

    --------------------------------------------

    CREATE FUNCTION DateOnly_sdt

      (@DateAndTime AS smalldatetime)

    RETURNS smalldatetime

    AS

    /*****************************************

     * return date without time smalldatetime

     *****************************************/

    BEGIN

      RETURN CAST(ROUND(CAST(@DateAndTime AS float),0,1) AS smalldatetime)

    END

    CREATE FUNCTION DateOnly_dt

      (@DateAndTime AS datetime)

    RETURNS datetime

    AS

    /*****************************************

     * return date without time for datetime

     *****************************************/

    BEGIN

      RETURN CAST(ROUND(CAST(@DateAndTime AS float),0,1) AS datetime)

    END

    another Function

    ----------------------------------------------

    CREATE  function getdatePortion(@inDate datetime)

    returns datetime

    As

    begin

     return convert(datetime,convert(varchar(11),@inDate))

    end

     

    I hope this help u


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

Viewing 7 posts - 1 through 6 (of 6 total)

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