none
extract just the date from a datetime field using T-SQL

    Question

  • I am using a calendar control to pass a date to a stored procedure.  The field in the table is a datetime field.  Is it possible to extract just the date from the datetime field, or do I have to use multiple Datepart?

    WHERE  (datepart(mm,sampletimestamp) = month(@selcteddate) and
     datepart(dd,sampletimestamp) = day(@selcteddate) and
     datepart(yyyy,sampletimestamp) = year(@selcteddate)
     )

    This works, but I thought there must be an easier way.

     

    Wednesday, January 10, 2007 7:14 PM

Answers

  • Something like this:

    select DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())),
           DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))

    ----------------------- -----------------------
    2007-01-10 00:00:00.000 2007-01-11 00:00:00.000

    And best to use a form like this for your where:

    WHERE sampletimestamp >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, @selcteddate))
      and sampletimestamp < DATEADD(DAY, 1, DATEDIFF(DAY, 0, @selcteddate))

    So you can increase the likelihood of using an index for the search, since you don't have to execute a function on the column (which makes it unusable as a search argument for an index lookup.)

    Wednesday, January 10, 2007 7:25 PM
    Moderator

All replies

  • There are many ways. Easiest is to do below:
     
    convert(varchar, sampletimestamp, 112) = @selcteddate
    Wednesday, January 10, 2007 7:24 PM
  • Something like this:

    select DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())),
           DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))

    ----------------------- -----------------------
    2007-01-10 00:00:00.000 2007-01-11 00:00:00.000

    And best to use a form like this for your where:

    WHERE sampletimestamp >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, @selcteddate))
      and sampletimestamp < DATEADD(DAY, 1, DATEDIFF(DAY, 0, @selcteddate))

    So you can increase the likelihood of using an index for the search, since you don't have to execute a function on the column (which makes it unusable as a search argument for an index lookup.)

    Wednesday, January 10, 2007 7:25 PM
    Moderator