COUNT DATE

• TJ_T

  Old Hand

  Points: 352

  More actions

  November 7, 2019 at 11:35 pm

  #3696130

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

  More actions

  November 8, 2019 at 1:13 am

  #3696150

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

  More actions

  November 8, 2019 at 10:29 am

  #3696209

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

  More actions

  November 8, 2019 at 12:10 pm

  #3696230

  convert(varchar,mydatefield,101)

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

  cast(mydatefield as date)

  ?

  ---

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  November 8, 2019 at 12:29 pm

  #3696234

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

  John