locked
how to write condition for 'where' getDate() RRS feed

  • Question

  • User647458646 posted

    I would like to know correct query syntax for writing a 'where' condition, where its able to retrieve data records that equals today's date from datetime paramater 'Activedate'.

    for example, i have tried the following queries but i keep getting no records - please advise further. 

    select * from [dbo].[StoryTbl] where  [ACTIVEDATE] = CONVERT (date, GETDATE())
    
    select * from [dbo].[StoryTbl] where ACTIVEDATE = getDate()
    
    select * from [dbo].[StoryTbl] where ACTIVEDATE = Dateadd(day,datediff(day,0,getdate()),0)

    thank you

    Wednesday, June 15, 2016 3:07 PM

Answers

  • User77042963 posted

    If your column  ACTIVEDATE is datetime data type,

    you can use query like this:

    select * from [dbo].[StoryTbl] where ACTIVEDATE>=Dateadd(day,datediff(day,0,getdate()),0) and ACTIVEDATE<Dateadd(day,datediff(day,0,getdate())+1,0)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 15, 2016 5:45 PM

All replies

  • User77042963 posted

    If your column  ACTIVEDATE is datetime data type,

    you can use query like this:

    select * from [dbo].[StoryTbl] where ACTIVEDATE>=Dateadd(day,datediff(day,0,getdate()),0) and ACTIVEDATE<Dateadd(day,datediff(day,0,getdate())+1,0)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 15, 2016 5:45 PM
  • User-1636183269 posted

    Missy,

    Please try:

    select * from [dbo].[StoryTbl] where  CAST([ACTIVEDATE] as DATE) = CAST(GETDATE() as DATE)

    Wednesday, June 15, 2016 6:07 PM
  • User1559292362 posted

    Hi missy786,

    for example, i have tried the following queries but i keep getting no records - please advise further. 

    you need to CONVERT the field named ACTIVEDATE, please check the following statement.

    select * from [dbo].[StoryTbl] where CONVERT(DATE, [ACTIVEDATE]) = CONVERT(DATE, CURRENT_TIMESTAMP);

    Best regards,

    Cole Wu

    Thursday, June 16, 2016 8:16 AM