efficiently extracting date portion of datetime

efficiently extracting date portion of datetime

Post by Simon Weave » Wed, 26 Mar 2003 08:57:09



(this is a simplified example)

I have a table with

datetime EntryDate = getdate()
int EventID

I want to see how many times each event occurs on each day.

the best i can come up with is:

SELECT
COUNT(*),
DATEPART(yy, EventDate) AS yy,
DATEPART(mm, EventDate) AS mm,
DATEPART(dd, EventDate) AS dd
FROM [Events]
GROUP BY yy, mm, dd
ORDER BY yy desc, mm desc, dd desc

which extracts the yy/mm/dd components and then groups by them.
I have also used the convert function to create a string of :
yy + '/' + mm + '/' + dd and then convert that back into a datetime.

now functionally i have no problems whatsoever. works just great.
i was just curious as to whether this is the most efficient way to do
this or not.

i'm sure it would be more efficient to have a datefield which just contained
0,0,0 for hours minutes and seconds but i want to retain the hours,
minutes and seconds for other reasons. i'd rather not have 2 date columns
for different purposes.

The above does seem quite fast even with thousands of rows, but it
just looked suspicious to me so i thought i'd seek expert advice.

any thoughts?

thanks,
simon

 
 
 

efficiently extracting date portion of datetime

Post by Anith Se » Wed, 26 Mar 2003 09:13:29


You can do:

SELECT CONVERT(VARCHAR, dateCol, 101),
       COUNT(*)
  FROM tbl
 GROUP BY CONVERT(VARCHAR, dateCol, 101)

--
- Anith
(Please respond only to newsgroups)

 
 
 

efficiently extracting date portion of datetime

Post by hsali » Wed, 26 Mar 2003 14:32:33


Like Anith said, you should cast/convert it to a char datatpe
If you still needed the date datetype, you could
Select cast(Cast(getdate() as char(11)) as smalldatetime)

Regards
Habib


| You can do:
|
| SELECT CONVERT(VARCHAR, dateCol, 101),
|        COUNT(*)
|   FROM tbl
|  GROUP BY CONVERT(VARCHAR, dateCol, 101)
|
| --
| - Anith
| (Please respond only to newsgroups)
|
|
|
|

 
 
 

efficiently extracting date portion of datetime

Post by Simon Weave » Thu, 27 Mar 2003 03:40:51


great, thanks


> Like Anith said, you should cast/convert it to a char datatpe
> If you still needed the date datetype, you could
> Select cast(Cast(getdate() as char(11)) as smalldatetime)

> Regards
> Habib



> | You can do:
> |
> | SELECT CONVERT(VARCHAR, dateCol, 101),
> |        COUNT(*)
> |   FROM tbl
> |  GROUP BY CONVERT(VARCHAR, dateCol, 101)
> |
> | --
> | - Anith
> | (Please respond only to newsgroups)
> |
> |
> |
> |

 
 
 

1. Extracting Portions of Datetime

I want to do a SELECT based on just the time portion of a datetime column.
i.e.  "Show me the rows where the starttime is greater than 12:00 regardless
of the date".  I have tried using the CONVERT function, but no matter what
style I use, the engine includes the date in the comparison.

For instance, the following fragment works, for dates of 5/31.  But I don't
want to limit the result set to just rows from 5/31.  I want ALL rows that
where starttime is in the afternoon.  If I remove the date portion from the
conversion string, or put in a lower date, I don't get the result I want.
WHERE     (StartTime > CONVERT(DATETIME, '2001-05-31 12:00:00', 102))

The key lies in extractings just the time, and then doing a compare.  I
could also see wanting to extract just the date on other queries.  In either
case, I don't know how to do the extract and compare.

Thanks,

Bill

2. Ad-Hoc query tools

3. extract time portion of date

4. how to use a pulltext procedure

5. Editing just the time (or date) portion of the datetime

6. DBGrid row selection?

7. Select Statement (Grouping by Date portion of DateTime)

8. Salary for certified Oracle 8i dba's.

9. Setting Field default to date portion of datetime

10. Compare only date portion of DateTime field

11. Extracting date from a DateTime field

12. Extracting a date or time from DATETIME?

13. Extract date from datetime field