none
How to round off time datetime to just the date

    Question

  • HI,

    I have a table which has a column named createddate and this has the values of datetime ( ex 2013-10-02 12:07:32.400).

    Now i want to query this table to get all the records for 2013-10-02 (just for yesterday)
    If i do something like 

    Select * from Table where createddate = '2013-10-02 00:00:00.000'

    I get no rows back.Can someone please help me with this?

    Thanks

    Thursday, October 03, 2013 1:11 PM

Answers

  • try this :

    Select * from Table where dateadd(dd, 0, datediff(dd, 0, createddate)) = '2013-10-02 00:00:00.000'


    Please vote if you find this posting was helpful or Mark it as answered.


    • Edited by Paresh Patel Thursday, October 03, 2013 1:15 PM
    • Marked as answer by SqlDev12 Thursday, October 03, 2013 1:17 PM
    Thursday, October 03, 2013 1:14 PM

All replies

  • try this :

    Select * from Table where dateadd(dd, 0, datediff(dd, 0, createddate)) = '2013-10-02 00:00:00.000'


    Please vote if you find this posting was helpful or Mark it as answered.


    • Edited by Paresh Patel Thursday, October 03, 2013 1:15 PM
    • Marked as answer by SqlDev12 Thursday, October 03, 2013 1:17 PM
    Thursday, October 03, 2013 1:14 PM
  • You can also try,

    Select * from Table where convert(char(10),createddate,101) = '10/03/2013'

    Regards, RSingh


    • Edited by RSingh() Thursday, October 03, 2013 1:19 PM
    Thursday, October 03, 2013 1:17 PM
  • Hi Paresh,

    It worked fine.

    Thanks

    Thursday, October 03, 2013 1:17 PM
  • PFB code

    declare @getdate datetime select @getdate= convert(varchar(10),getdate(),121) Select * from Table where convert(varchar(10),createddate,121) = convert(varchar(10),@getdate,121) or Select * from Table where createddate <= convert(varchar(10),@getdate,121)+ ' 00:00:00.000' and createddate >= convert(varchar(10),@getdate,121)+ ' 23:59:59.000'

    Thanks

    Saravana Kumar C

    Thursday, October 03, 2013 1:17 PM
  • …WHERE createddate >=DATEADD(day, DATEDIFF(day,0,GETDATE())-1,0) 
    AND createddate < DATEADD(day, DATEDIFF(day,0,GETDATE()),0) 

    Thursday, October 03, 2013 1:42 PM
  • Hi,

    On SQL 2008 it'd be

    SELECT * FROM MyTable WHERE CAST(MyDateTimeField AS DATE) = '2013-10-02'


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Thursday, October 03, 2013 1:49 PM
  • Ok, almost all the suggestions wrap the table column in an expression making it non-sargable.

    That's not OK, as it will always require a scan of all rows to evaluate.

    SELECT * FROM Table 
    where createddate >= cast(@somedate AS DATE)
      and createdate < dateadd(day,1,cast(@somedate AS DATE))
    David


    David http://blogs.msdn.com/b/dbrowne/


    Thursday, October 03, 2013 2:10 PM
  • CAST (foo_timestamp AS DATE ) AS foo_date

    The DATE data type is three byte and has built-in math. Why don't  your know that rows are not records? This is fundamental RDBMS. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, October 03, 2013 5:55 PM