Rounding down a Date in SQL

November 15, 2006 @ 2:36 pm- from Maui, Hawaii

Ok I'm a moron for not seeing the tree for forest once again. Nevermind on date conversions DateTime.Now.Date gives just the date part.

Dates in .NET are always represented as DateTime values which include a time portion. Sometimes I need to use dates that are ‘just’ date values and there’s no native mechanism to extract just the date as far as I can see. The closest thing that I can see is to use the following ugly code:

DateTime DateCutOff = DateTime.Now.AddDays(days * -1);

DateCutOff = DateTime.Parse(DateCutOff.ToString("d")); // round down

Works, but really this just feels all wrong <s>.

But why is it so hard to work with dates with anything Microsoft related in the first place? SQL Server doesn’t have any notion of a date without time. For example, to run a query that retrieves a result set that is grouped by date you have to resort stuff like this:

select count(*) as Count,year(time) as yy, month(time) as mm, day(time) as dd,

ip, min(time) as Time into #TQuery

from webrequestlog

where time >= @DateCutOff

group by year(time),month(time), day(time), ip

order by 1 desc

select min(time) as Date,sum(Count) as Hits, count(*) as Visitors from #TQuery

group by yy,mm,dd order by min(Time) Desc

just to get a date sorted aggregate. How much easier would this be if there was just a simple way to round down a date?

Posted in .NET CSharp

Feedback for this Weblog Entry

re: Rounding down a .NET Date
by Einar G. November 15, 2006 @ 3:16 pm

If you just need the date part why not just DateTime.Now().date ? that returns only the date part if I recall right

re: Rounding down a .NET Date
by Colin Neller November 15, 2006 @ 3:35 pm

In SQL, you can truncate the time with either

DATEDIFF(d,0,GETDATE())

CAST(GETDATE() AS INT)

re: Rounding down a .NET Date
by Eric W. Bachtal November 15, 2006 @ 4:02 pm

For what it's worth, there are some interesting suggestions for dropping time from SQL Server dates in the comments of this post from a couple of years ago:

http://ewbi.blogs.com/develops/2004/11/sql_server_date.html

Good luck.

re: Rounding down a Date in SQL
by Paul Mrozowski November 15, 2006 @ 7:04 pm

Not that I disagree, but you can do a group by like this:

SELECT COUNT(*) AS Visitors,
       ip,
       CONVERT(varchar, [time], 111) AS [date]
  FROM WebRequestLog
 WHERE time > @DateCutoff
 GROUP BY CONVERT(varchar, [time], 111), ip
 ORDER BY [date]

re: Rounding down a Date in SQL
by Marc Brooks November 15, 2006 @ 11:51 pm

You just want to truncate the time portion, right? That is equivalent to midnight, which is the very first of the formulas shown on my "More on DATEs in SQL" post... http://musingmarc.blogspot.com/2006/07/more-on-dates-and-sql.html

In context of your post, it's

SELECT  
  COUNT (*) AS [Count]
  , DATEADD(dd, DATEDIFF(dd, 0, [time]), 0) AS [Date]
FROM
    [webrequestlog ]
WHERE
    [time] >= @DateCutOff
GROUP DATEADD(dd, DATEDIFF(dd, 0, [time]), 0)
    , [ip]
ORDER BY
    [Date] DESC

This works by subtracting the supplied date (like [time] in your case) from zero--which Microsoft SQL Server interprets as 1900-01-01 00:00:00 and gives the number of days since 1900-01-01. This value is then re-added to the zero date yielding the same date with the time truncated.

Enjoy all the OTHER cool date stuff on my page (like how to extract the date, week, month, year, end-of-xxx, etc.)

re: Rounding down a Date in SQL
by Rob Kent November 16, 2006 @ 1:41 am

To get around these problems I have a PrecisionDay class in C# and a PrecisionDay function in SQL, so that where necessary I'm dealing with zero hours, minutes and seconds. I coded this up after reading Martin Fowler's discussion of the TimePoint and Range patterns (http://www.martinfowler.com/ap2/timePoint.html). The constructors for PrecisionDay are:

public PrecisionDay(int year, int month, int day)
{
    _base = new DateTime(year, month, day, 0, 0, 0, 0);
}

public PrecisionDay(DateTime date)
{
    _base = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0, 0);
}

The Sql function does the same thing and sets the time elements to zero. If the time is not important, I'll store the date in that format anyway to avoid comparison issues. For example, if you are storing the date someone joined an organisation, you don't care about the time.

re: Rounding down a Date in SQL
by Bob Archer November 16, 2006 @ 6:59 am

Rick,

Hmm... I think people are making this to hard. As long as you never send a "time" to sql server the time on every date will be the same, 12:00:00 AM... if that is the case you can group by date just fine.

BOb

re: Rounding down a Date in SQL
by Rob Kent November 16, 2006 @ 8:48 am

Bob,

I think in Rick's example he was using IIS log entries so has no choice over the time portion. Otherwise, it does make sense to store all dates with a time of zero, which is the purpose of the PrecisionDay functions in C# and SQL.

Rob

re: Rounding down a Date in SQL
by Nancy Folsom November 16, 2006 @ 9:59 am

Einar suggestion to use DateTime.Now.Date is good for getting just the date portion; however, your point about dates being a PIA to work with is dead-on, IMHO. Don't even get me started on the issue of empty dates.

re: Rounding down a Date in SQL
by Kiliman November 16, 2006 @ 10:27 am

Although you can truncate the time portion of a datetime value using (CAST AS INT), the problem is that it will round *up* to the next day after 12:00 PM.

I've used the following to strip the time portion:

select convert(datetime, floor(convert(float, getdate())))

Kiliman

re: Rounding down a Date in SQL
by steve from Pleasant Hill November 16, 2006 @ 6:59 pm

Would something along these lines help (using the CONVERT function with Styles)?

select count(*) as Count, ip, convert(varchar(12), [somedatefield], 111) as mydate
from webrequestlog (nolock)
where DATEDIFF(day, [somedatefield], getdate()) > 1 --stuff when older than 1 day
group by ip, convert(varchar(12), [somedatefield], 111)

re: Rounding down a Date in SQL
by Mike O'Brien November 20, 2006 @ 11:23 am

I have always used Kiliman's method. I think it is the cleanest and most accurate. More on that method can be found here: http://blog.mike-obrien.net/PermaLink,guid,f3363145-8753-4604-8314-855012a00400.aspx

re: Rounding down a Date in SQL
by William Plander November 21, 2006 @ 4:54 pm

well, if you have problems noticing the trees in the forest?.... get yourself a bull-dozer

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=74300

Major League date solution

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Rick Strahl's Web Log
by Rick Strahl's Web Log June 23, 2007 @ 6:12 pm

re: Rounding down a Date in SQL
by Aron November 10, 2007 @ 4:50 am

Hello, I tried this below and got an error:

Do do you group by date (without the grouping by the time too)

SELECT TOP (100) PERCENT SUM(OrderTotal) AS DateTotal, StoreId, CONVERT(varchar, OrderDate, 111) AS date
FROM dbo.thisticket_tbl_Order
GROUP BY CONVERT(varchar, time, 111), StoreId
ORDER BY StoreId