• TJ_T

    Old Hand

    Points: 348

    I need some help counting a date field. I tried the examples below and no luck

    COUNT(CAST(CONVERT(CHAR(11),  MyDateField , 113)

    COUNT(CONVERT(DATETIME, MyDateField, 101))


    2017-01-01 01:03:06.001

    2017-01-01 01:04:05.002

    2017-01-02 01:03:05.003

    2017-01-02 01:04:04.006

  • Jonathan AC Roberts


    Points: 17273

    I'm not sure what you are trying to do, but COUNT(MyDateField) will count the not null MyDateField values.

    Or are you trying to do do something else like COUNT(DISTINCT CONVERT(varchar, MyDateField, 112)) ?

  • SGT_squeequal


    Points: 7163

    are you just wanting to count the number of occurrences of a date regardless of time,  example,my orders table stores datetime so if i count orders created yesterday i get a list of date and times and a count of 1 but i want to count how many orders i created yesterday. so i could convert the date field to just DD MM YYYY and count.

    select convert(varchar,mydatefield,101),count(*)

    from orders

    group by convert(varchar,mydatefield,101)






    ***The first step is always the hardest *******

  • Phil Parkin

    SSC Guru

    Points: 244447


    Do you prefer this over the more intuitive (in my opinion)

    cast(mydatefield as date)


    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See for details of how to post T-SQL code-related questions.

  • John Mitchell-245523

    SSC Guru

    Points: 148746

    Yes, I agree, Phil.  Casting to date preserves sargability; converting to varchar doesn't.



Viewing 5 posts - 1 through 5 (of 5 total)

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