Rounding down a Date in SQL

November 15, 2006 • from Maui, Hawaii • 16 comments

On this page:

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?

The Voices of Reason

Einar G.
November 15, 2006

# re: Rounding down a .NET Date

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

Colin Neller
November 15, 2006

# re: Rounding down a .NET Date

In SQL, you can truncate the time with either

DATEDIFF(d,0,GETDATE())

CAST(GETDATE() AS INT)

Eric W. Bachtal
November 15, 2006

# re: Rounding down a .NET Date

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.

Paul Mrozowski
November 15, 2006

# re: Rounding down a Date in SQL

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]

Marc Brooks
November 15, 2006

# re: Rounding down a Date in SQL

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.)

Rob Kent
November 16, 2006

# re: Rounding down a Date in SQL

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.

Bob Archer
November 16, 2006

# re: Rounding down a Date in SQL

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

Rob Kent
November 16, 2006

# re: Rounding down a Date in SQL

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

Nancy Folsom
November 16, 2006

# re: Rounding down a Date in SQL

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.

Kiliman
November 16, 2006

# re: Rounding down a Date in SQL

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

steve from Pleasant Hill
November 16, 2006

# re: Rounding down a Date in SQL

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)

Mike O'Brien
November 20, 2006

# re: Rounding down a Date in SQL

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

William Plander
November 21, 2006

# re: Rounding down a Date in SQL

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
June 23, 2007

# Rick Strahl's Web Log

Aron
November 10, 2007

# re: Rounding down a Date in SQL

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

Grim repair
January 20, 2012

# re: Rounding down a Date in SQL

I'd love to see microsoft include actual functions to do ordinary date manipulations that I was used to since the early 80s. How they can sell their garbage as a professional product is beyond me. Every single time I work with t-sql, i spend a huge time trying to re-invent the wheel.

Rick Strahl's Weblog