Post reply

newbie - how to correctly filter on date select * from dbo.tblSubmissions where createdby = CreatedOn > '2011-02-03'

  • February 8, 2011 at 7:49 am

    #99176

    How to correctly filter on date? I have to now use this greater than OR the exact date time below.

    select * from dbo.tblSubmissions where createdby = CreatedOn > '2011-02-03'

    It only works when I include the time as well as date CreatedOn = '2011-02-08 08:24:57.660'

    Googled and could not get an answer probably cause it is so simply but i tried like

    datepart(YYYYMMDD,CreatedOn) = '20110208' to no avail.

    WHat is the syntax if i just want up to plug in year month day

    Why does this not work, am I going crazy?

    CreatedOn = '2011-02-03'

    Thanks in advance

  • February 8, 2011 at 7:54 am

    #1283695

    All you could ever wish to know about dates in sql server is documented here...

    http://www.karaszi.com/SQLServer/info_datetime.asp


    Clear Sky SQL
    My Blog[/url]

  • February 8, 2011 at 8:24 am

    #1283734

    The easiest way to do it is to check a range.

    Where CreatedOn >= '2011-02-03' and CreatedOn < '2011-02-04'

    There are other ways to do it, but this one almost always works best (most accurate and fastest).

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • February 8, 2011 at 1:02 pm

    #1284004

    Thanks all,

    I got quickly what I needed( CONVERT(char(8), CreatedOn, 112) = '20110208' ) from the link from Dave before I saw the other reply. But I will need to use the latter for range searches!

    Thanks

  • February 8, 2011 at 1:06 pm

    #1284008

    The convert version will bypass indexes on the datetime column, thus slowing the whole query down significantly. The range version can use indexes, and is thus usually much faster and more efficient.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • February 8, 2011 at 1:10 pm

    #1284010

    Please finish reading that article, specifically the sentence following the conversion it states "This can be disastrous for performance!".


    Clear Sky SQL
    My Blog[/url]

  • February 8, 2011 at 1:23 pm

    #1284018

    adam spencer (2/8/2011)

    Thanks all,

    I got quickly what I needed( CONVERT(char(8), CreatedOn, 112) = '20110208' ) from the link from Dave before I saw the other reply. But I will need to use the latter for range searches!

    Thanks

    What you are doing IS a range search, just a very inefficient one.

    It is logically equivalent to this range search, but the convert function prevents it from using indexes.

    where

    CreatedOn >= '20110208 00:00:00.000'and

    CreatedOn < '20110209 00:00:00.000'

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

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