none
TSQL Query help: Where Time equals today's date

    Question

  • I'm using follwoing code:

    TimeStamp Column:

    2013-08-04 19:18:27.200
    2013-08-04 19:18:27.417
    2013-08-04 19:18:34.600
    2013-08-04 19:18:38.100
    2013-08-04 19:18:50.453
    2013-08-04 19:18:53.040
    2013-08-04 19:27:55.417

    Where Clause:

    WHERE     (TimeStamp > CONVERT(date, GETDATE()))

    to get data filtered by today's date and it's working fine

    Is there a better way to do this?

    Wednesday, August 21, 2013 3:09 PM

Answers

  • where cast(TimeStamp as date) = cast(CURRENT_TIMESTAMP AS Date)

    Or alternatively

    WHERE TimeStamp >= CAST(current_timestamp AS DATE) and TimeStamp < CAST(dateadd(day, 1, CURRENT_TIMESTAMP) AS Date)


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


    My blog


    My TechNet articles

    • Marked as answer by SSISQ Friday, August 23, 2013 3:41 PM
    Wednesday, August 21, 2013 3:23 PM
    Moderator
  • Yes. CONVERT and getdate() are the old 1970's dialect from Sybase. We use the ANSI/ISO Standard CAST() and CURRENT_TIMESTAMP now.   

     CAST (foobar_timestamp AS DATE) =  CAST (CURRENT_TIMESTAMP AS DATE) = 


    --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

    • Marked as answer by SSISQ Friday, August 23, 2013 3:41 PM
    Wednesday, August 21, 2013 6:50 PM

All replies

  • where cast(TimeStamp as date) = cast(CURRENT_TIMESTAMP AS Date)

    Or alternatively

    WHERE TimeStamp >= CAST(current_timestamp AS DATE) and TimeStamp < CAST(dateadd(day, 1, CURRENT_TIMESTAMP) AS Date)


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


    My blog


    My TechNet articles

    • Marked as answer by SSISQ Friday, August 23, 2013 3:41 PM
    Wednesday, August 21, 2013 3:23 PM
    Moderator
  • There is no better way in your case.

    But the best way would be '20-Aug-2013'.

    If you use in a SP, then assign it to a variable and reuse.


    • Edited by Taherul673 Wednesday, August 21, 2013 3:38 PM
    Wednesday, August 21, 2013 3:29 PM
  • I'm using follwoing code:

    TimeStamp Column:

    2013-08-04 19:18:27.200
    2013-08-04 19:18:27.417
    2013-08-04 19:18:34.600
    2013-08-04 19:18:38.100
    2013-08-04 19:18:50.453
    2013-08-04 19:18:53.040
    2013-08-04 19:27:55.417

    Where Clause:

    WHERE     (TimeStamp > CONVERT(date, GETDATE()))

    to get data filtered by today's date and it's working fine

    Is there a better way to do this?

    Hello Can you check this,this will provide todays date from starup like 2013-08-21 00:00:00.000, so may be you can select exactly one day records with exact time.You cab  sue this in where clause

    select DATEADD(day, DATEDIFF(day, 0, getDate()), 0)


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Wednesday, August 21, 2013 3:42 PM added line
    Wednesday, August 21, 2013 3:42 PM
  • Yes. CONVERT and getdate() are the old 1970's dialect from Sybase. We use the ANSI/ISO Standard CAST() and CURRENT_TIMESTAMP now.   

     CAST (foobar_timestamp AS DATE) =  CAST (CURRENT_TIMESTAMP AS DATE) = 


    --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

    • Marked as answer by SSISQ Friday, August 23, 2013 3:41 PM
    Wednesday, August 21, 2013 6:50 PM