Post reply

how to count a column by using between operator

  • September 17, 2011 at 3:34 am

    #257544

    HI,

    i have a column of varchar i want to count by using between datatime

    my query is

    select (*) from tablename where columnname(datetime) between 1 and 5

    this query is throughing error

  • September 17, 2011 at 3:46 am

    #1382886

    depending on the format of your date:

    select count(*) from yourtable where cast(column as datetime) between '2010-01-01' and '2011-01-01'

    select count(*) from yourtable where convert(datetime, column, 112) between '2010-01-01' and '2011-01-01'

    More information regarding date formats in books online


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • September 17, 2011 at 4:18 am

    #1382890

    As okbabgas said, it depends on the format in the varchar column. If the format is yyyymmdd then you dont need to cast it to datetime. Just use WHERE COL BETWEEN '20110101' AND '20110201'. A cast will prevent any use of indexes you might have on the column.

    My question is why you would store it in varchar instead of datetime.

Viewing 3 posts - 1 through 2 (of 2 total)

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