November 7, 2019 at 11:35 pm
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
November 8, 2019 at 1:13 am
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)) ?
November 8, 2019 at 10:29 am
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 *******
November 8, 2019 at 12:10 pm
convert(varchar,mydatefield,101)
Do you prefer this over the more intuitive (in my opinion)
cast(mydatefield as date)
?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 8, 2019 at 12:29 pm
Yes, I agree, Phil. Casting to date preserves sargability; converting to varchar doesn't.
John
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply