COUNT DATE

  • TJ_T

    Old Hand

    Points: 346

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

    DATA:

    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

    SSCoach

    Points: 16929

    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

    SSCertifiable

    Points: 7131

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

    convert(varchar,mydatefield,101)

    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.

  • John Mitchell-245523

    SSC Guru

    Points: 148360

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

    John

     

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

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