SQL Server Performance Forum – Threads Archive

DateTime issues

Hello, I am trying to run a query on SQL server 2005, I need to extract some data according to the datestamp of the records.
This is the query: SELECT COUNT(LogID),CONVERT(datetime, CONVERT(varchar(10), DateStamp, 103), 103)
FROM TransactionsLog
WHERE (DateStamp BETWEEN CONVERT(datetime, ’01/03/2007′, 103) AND CONVERT
(datetime, ’10/03/2007′, 103))
GROUP BY CONVERT(datetime, CONVERT(varchar(10), DateStamp, 103), 103) I added the outer CONVERT because the datestamp on the result set was varchar, thus it wasn’t sorted according to the date but according to the first 2 characters of the string. With the outer CONVERT the data is now sorted however i need to get rid of the time (which now is 00:00:00)! Can anyone help? Thanks in advance, TedManowar

Avoid adding functions around column names in WHERE or GROUP BY. This usually means SQL will not use indexes, and do table scans instead. Does the data on the DateStamp column record specific time (other than midnight)? If not, then drop the CONVERT calls from column list and GROUP BY. Also, you don’t have to use CONVERT on a valid date expression when comparing against a DATETIME column – ’01/03/2007′ is fine by itself (provided that you mean Jan 3rd – however, to avoid all possible confusion, use ‘2007-01-03’).

Hi, The reason I am using the functions is because i need the grouping to be performed on the date only. The datestamp has full datetime values with date and time of bookings.

Thanks for your help I have the answer with functions: SELECT COUNT(LogID) AS Expr1, CONVERT(varchar, DateStamp, 103) AS Expr2
FROM TransactionsLog
WHERE (DateStamp BETWEEN CONVERT(datetime, ’01/02/2007′, 103) AND CONVERT(datetime, ’10/03/2007′, 103))
GROUP BY CONVERT(varchar, DateStamp, 103)
ORDER BY CONVERT(datetime, CONVERT(varchar, DateStamp, 103), 103) TedManowar

You might try a derived table for the base dates, see if it helps: SELECT COUNT(*), x.DateNoTime
FROM TransactionsLog t
INNER JOIN
(SELECT DISTINCT DateStamp, DATEADD(d, DATEDIFF(d, 0, DateStamp), 0) AS DateNoTime
FROM TransactionsLog) x ON t.DateStamp = x.DateStamp
GROUP BY x.DateNoTime [edit: switched the parameters for DATEDIFF]

Wouldn’t that yield the same result?
SELECT COUNT(LogID) AS Expr1, DATEADD(d,DATEDIFF(d,0,DateStamp),0) AS Expr2
FROM TransactionsLog
WHERE DateStamp BETWEEN CAST(‘20070201’ AS DATETIME) AND CAST(‘20070310’ AS DATETIME)
GROUP BY DATEADD(d,DATEDIFF(d,0,DateStamp),0)
ORDER BY Expr2
—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Frank, of course it would. It was a silly suggestion. I was finding better performance for the derived table when using a temp table, but worse when using a permanent table – not sure what was happening there.

Adriaan, my reply was in turn to Teds’ last one. When I started typing, your reply "in between" wasn’t there. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

]]>